Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SSIS Parameter in Oracle query Expand / Collapse
Posted Wednesday, June 16, 2010 10:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 12:25 PM
Points: 9, Visits: 69
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.
Post #938645
Posted Thursday, June 17, 2010 6:18 AM



Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
And the question is...?

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #938833
Posted Thursday, June 17, 2010 12:46 PM


Group: General Forum Members
Last Login: Tuesday, March 15, 2011 8:30 AM
Points: 17, Visits: 89
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 ?
Post #939184
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse