0

Already checked this post: SSIS and sending query with date to Oracle

And I'm using variable query per below thread SSIS - Using parameters in Oracle Query using Attunity Oracle Datasource

Tool used: VS-2019 Data flow: MS Oracle Source (for VS-2019) My source is Snowflake cloud. I'm successfully able to get the max date from table and store in Object type variable (named:- @var_Snowflake_Table_maxDate). Then I use a script task to convert the value to string type. Code for script task is:

 public void Main()
        {
            OleDbDataAdapter A = new OleDbDataAdapter(); //using System.Data.OleDb; ADDED above in NAMESPACES
            System.Data.DataTable dt = new System.Data.DataTable();

            A.Fill(dt, Dts.Variables["User::var_Snowflake_Table_maxDate"].Value);

            foreach (DataRow row in dt.Rows)
            {
                object[] array = row.ItemArray;
                Dts.Variables["User::var_CreateDate"].Value = array[0].ToString();
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }

This sets my param of @var_CreateDate String type correctly. I tried this on local machine and was able to pass the value to a native instance of sql-server(yes NOT oracle). Just to test my parameters from script task works. Finally: I'm using VS-2019's MS Oracle Source to pass the value into Oracle cloud server. Sample query's I have tried

"select * from Table where rownum <= 5 and NVL(CREATE_DATE,UPDATE_DATE) = " +"'05-09-2020'"

::::evals to:::: select * from relate.awd_acct_activity where rownum <= 5 and NVL(CREATE_DATE,UPDATE_DATE) = '2020-05-09' and this works. But value is hard coded. Try 2: 

"select * from table  where rownum <= 50 and 
NVL(CREATE_DATE,UPDATE_DATE) = " +"'@[User::var_CreateDate]'"

Try 3: 

"select * from table  where rownum <= 50 and 
NVL(CREATE_DATE,UPDATE_DATE) = to_date(" +"'@[User::var_CreateDate]'"+")"

Try 4: 

"select * from table where rownum <= 50 and 
NVL(CREATE_DATE,UPDATE_DATE) = to_date(" +"'@[User::var_CreateDate]'"+",'YYYY-MM-DD')"

None of try 2 through 4 eval correctly. Can I have some guidance into how to pass this parameter to Oracle cloud. Thanks.

junketsu
  • 533
  • 5
  • 17

1 Answers1

0

I'm assuming you're trying to figure out the syntax for a variable, that would hold the query text. You can try something like this:

"select * from table where rownum <= 50 and 
NVL(CREATE_DATE,UPDATE_DATE) = to_date(" + "'" + @[User::var_CreateDate] + "'" + ",'YYYY-MM-DD')"
Gigga
  • 555
  • 2
  • 10
  • Believe it or NOT what worked was another co-worker shared. In script task I had to put additional " ' " (single quotes) to encapsulate the date. And then within SSIS I had to created a 3rd variable to type string and assign the value of variable 2 (the one from script task with single quotes). Assign this 3rd variable to query. AND that worked. – junketsu May 22 '20 at 15:29