question

aRookieBIdev avatar image
aRookieBIdev asked

Passing parameters to an oracle query in ssis package.

Hi everyone, I am just wondering if there is any proper way to pass parameters to an oracle query in SSIS package.Kindly let me know. Thanks, Kannan
ssisoracle
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have tried the following: 1) Put the query in a variable "query" and using a script task I did a find and replace for the parameter value from another variable "parameter". 2) I also tried out using the property expressions to modify the query. Thanks, Kannan
0 Likes 0 ·
Daniel Ross avatar image
Daniel Ross answered
The best way of using parameters is to use a variable for the command. Put a value in the parameter variable, just for testing purposes. Your command variable is called query, change the EvaluateAsExpression property of "query" to TRUE. Then in the Expression property enter; "select column1,column2 from sysadm.oracleTable where value='" + (DT_WSTR, 4) @[User::variable] + "'" It important to put in the (DT_WSTR,) type cast, as the variable will not work if the variable is not a string. If you are using the query as a dataflow source, then enter your connection details, then in the source component change the "Data Access Mode" to "SQL Command from variable", then select the query variable from the drop down list.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aRookieBIdev avatar image
aRookieBIdev answered
I have tried the following: 1) Put the query in a variable "query" and using a script task I did a find and replace for the parameter value from another variable "parameter". 2) I also tried out using the property expressions to modify the query. Thanks, Kannan
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.