Parameterized queries against Oracle...
So I needed to get some data out of Oracle the other day and need it to be based off some parameters. Turns out the OLE DB source will not recognize parameters when used against Oracle. Neither the Microsoft or Oracle driver will do it. So I thought I might still be able to use the DataReader source against Oracle, it won't support parameters at all. I ended up creating a workaround that went like this....
First, create a variable called MyStmt and make it a String. Create another variable, MyID as an Integer. Now go to the properties of the MyStmt variable. Set the "EvaluateAsExpression" property to True. Now, read this well...place the SQL statement that you want to create in the "Expression" property, not the Value of the Variable. You will have to do some data conversion to concatanate the string and integer, so email me if you need help with that. Now, go back to your OLE DB source and set the source of the SQL statement to a Variable, and choose the correct variable name. Voila! A parameterized query! Don't forget to use the Microsoft OLE DB for Oracle driver and to check the datatypes on the Numerics...see my earlier blog posts...
Hope this helps....
Upcoming post...how to get SSIS to work against Oracle on a 64-bit server...
Scott
scott(at)barrettzone.com
posted by Scott Barrett @ 9:53 PM
6 comments
6 Comments:
Scott,
I wouldn't call that a workaround. I'd call it the right and proper way of going about this. I ALWAYS use this technique, regardless of whether I am going against Oracle or not.
-Jamie
Jamie,
I've found another method that will allow us to do the same thing without putting the SQL statement in a variable. I'll throw a post up today about it.
I agree, this is the easiest way to do this, but not as well documented as I would like.
Thanks for the post that you put up about my blog. I appreciate the help!
Scott
Scott,
Thanks for this BLog, it has some very useful tips.
Do you have any information for how to make work an Oracle OLE DB connection from SSIS in a Windows 2003 x64 edition? ... I have problems with this situation...
Thanks!
Sergio...
Remember that in order to use the 64bit components, you must move the SSIS package to the server and then launch it from a SQL Server Agent job. Running it manually will cause it to run in the 32bit workspace and it won't see the Oracle drivers. When it is launched from the SQL Server Agent, it is in the 64bit workspace and will see the Oracle drivers
Scott,
I have a "similar" problem, except I need to update records into an Oracle Database.
My situation is: I have two tables in an Oracle database, Days and C_DAY, and both have data, I need to read some records from C_DAY and strcat 4 columns for each row and then update the matching row in the DAYS table, using an OLE DB Command component.
I can not figure out how to update the Oracle database, in the OLE DB Command component, I tried to use a SQL statment, such as:
update DAYS set SAFETY=? where PrimaryKey=?
but it does not support parameterized SQL statement, with OLE DB Connection to Oracle.
Could you please help?
Thanks!
Wenbiao
Check which driver you are using. I believe only one of the drivers will support the parameters. I think I have a post on here that shows which one to use.
Scott
Post a Comment
<< Home