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 (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 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 (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99626 Visits: 13322
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-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 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
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 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 (109 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