Create SSIS- SQL Task Oracle SQL Statement With WHERE Clause "IN" keyword

Question

I really hope I can get some input on this. I'm trying to create an SSIS package with SQL Statement with "IN" in WHERE clause (ex: SELECT * FROM Oracle.Table1 WHERE Col1 IN (?) ). Basically, an Oracle SQL Statement with values from a SQL Server table. I've searched and can't find anything. Any suggestion will be greatly appreciated:

1. Can I use an "IN" keyword with values in object variable?
2. I've seen methods where "ForEach" component is used. Wouldn't this create a SELECT statement for each value? Potentially, thousands of records.

I've tried the following:

1. Used SQL Command with statement: SELECT * FROM Oracle.Table1 WHERE Col1 IN (?) - where ? is an object variable (resultset 1 col from a SQL Server statement) When I try to select "parameter", I get an error message.

2. Used SQL Command from Variable with an expression in a variable: "SELECT * FROM Oracle.Table1 WHERE Col1 IN (" + @[User::Obj_values] + ")"

FYI: I only have read permission to the Oracle DB :(

Answer 1

Top questions

1. No, you can do nothing in the expression language if the SSIS variable is of type Object

2. Yes, a foreach approach would entail a separate query per element in the array.

What you've tried questions

1. Parameter substitution does not work with a list of values - as you've observed

2. In is supported in Oracle so this approach will work but you must a) use a string data type b) roll your own logic to build the delimited list. In a C# task, it could be as trivial as string.Join(",", Dts.Variables["User::Obj_values"].Value.ToArray()); but may not depending on the underlying voodoo of how you've populated the object array

Other approaches

Depending on volume and whether a key in SQL Server could match (0 to 1) or (0 to many) in Oracle, you could write your Data flow as

• OLE DB Source (SQL Server query)
• Lookup Component (Oracle)
• Whatever destination

In this approach, you start with a filtered list of values and then compare it to your reference table in Oracle. If the reference table is "All transactions on the NYSE since inception" yeah, that's not going to work but if it's "all my customers" then you can probably bring that data into the lookup component.

You could try and split the difference by using the Lookup in a Partial cache mode. In this approach, instead of pulling the entire target table into memory, it fires a query for each element unless it has already seen the keys in which case it re-uses a local cached value. Between this approach and a foreach Enumerator approach, I'd favor firing the queries off via the lookup component as you would not have to pay the validation overhead for each start of the data flow task. Given this is Oracle, this presumes you have an OLE DB connection manager available. Otherwise, you'd have to work with the Cache Connection Manager and that, as the name implies, can only cache pre-defined data.