SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Parameter in Oracle query


SSIS Parameter in Oracle query

Author
Message
bob_kudulis
bob_kudulis
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 98
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.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62974 Visits: 13298
And the question is...?


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

MCSE Business Intelligence - Microsoft Data Platform MVP
vineet_
vineet_
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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 ?
Thomas Nguyen-480097
Thomas Nguyen-480097
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 24
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.
Attachments
Oracle.JPG (68 views, 50.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search