June 16, 2010 at 10:32 pm
I have a OLE DB Source in a package that returns data from an Oracle db to a table in SQL. I want to pass a parameter to the where clause.
June 17, 2010 at 6:18 am
And the question is...?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2010 at 12:46 pm
Parameters can be passed to the where clause if the entire query is stored in a string variable.
If there are 2 parameters to be passed, then create 3 variables.
One variable should be -- Sting data type contains the actual query -- Set Evaluate as Expression property to TRUE
Other two variables can be of any data type depending on the type of parameter to be passed.
See Example below:
User::Query = "SELECT * from CLAIMDX
where ClientID = "+ (DT_WSTR, 10) @[User::clientID]
+ " AND InsertDateTime = TO_DATE(' "
+ (DT_WSTR, 10) @[User::InsertDate] + " ', 'MM/DD/YYYY')"
This example has the query stored in the variable USER::Query
Parameters passed are clientID (numeric data type) and InsertDate(Date type).
Note that we need TypeCast operators to convert each type to string.
Also make use of the "Evaluate Expression" feature to verify that the query created is valid and you should be able to run it in Oracle database.
------------------
Why not ?
May 9, 2016 at 7:41 am
The next step in OLE DB Connection Manager, pick out the Oracle Connection.
In Data Access mode: Choose SQL Command from variable
In Variable Name: Pick out the variable for the whole query.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply