Parameters in data flow task with Oracle database source

In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.

Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.

I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.

Am I missing an obvious workaround?

pshotts wrote:

In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.

Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.

I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.

Am I missing an obvious workaround?

below is workaround, but i don't know how "obvious" it is:

Use property expressions. The SQL statement you use to query the Oracle source can be set by a variable, and that variable can be composed using a property expression such as: "select * from Orders where OrderID > " + @.LastOrderID. It's a parameterized query - and a very flexible one - in all but name.

|||Thanks Duane, but that does not get around the 4000 character limit on variable and expression length|||

Can you use a combination of query strings (in SSIS) and views in Oracle to reduce the amount of work you are doing in the one string?

Another alternative might be to execute a stored procedure that builds a temporary table (passing the parameters to the procedure), then read from the temporary table.

A third alternative would be to pull back the various discrete data sources from Oracle and handle joining/merging within SSIS. Can't say I'd recommend this one though. Better to just pull across the data you need from the remote source.

|||

Another option is to use a script source in which you concatenate the query string and call Oracle using OLEDB or ADO.Net in the script.

Donald

|||

Thanks Donald

That sounds like a good option. I understand that you mean we should use a Script Component as the Source in the Data Flow. The script will make the connection and build and run the required query.

Nice!

|||

That's correct.

You can still use a connection manager with your script component, to take advantage of that feature, but even that is not essential. although recommended.

Donald