1

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 :(

Jaji
  • 55
  • 5

1 Answers1

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Thanks. I will create a script to change my object variable into a string. Hopefully, that will work. Thanks much for your input! :) – Jaji Sep 15 '20 at 18:16
  • 2
    @Jaji, please note that Oracle can take no more than 1,000 expressions in **SELECT IN** statement. So, once your result set expand beyond this limit, Oracle query with IN and long CSV list will fail. See https://docs.oracle.com/database/121/SQLRF/conditions014.htm#SQLRF52169 – Ferdipux Sep 16 '20 at 10:02
  • @Ferdipux , Thank you! I can stop troubleshooting now :) . I'm surprised at how impossible this is. It seems so inefficient to return millions of records, to merge and only get about 5 thousand. I tried. :) – Jaji Sep 17 '20 at 04:10
  • 1
    Part of that is the constraints imposed upon you. If you could add a table to Oracle, then you could have a precursor data flow step that loads your search keys into a table. Then your extract is a `SELECT * FROM ORA_TBL AS O WHERE EXISTS (SELECT * FROM STG_TBL AS S WHERE S.key = O.key)` Given the imbalance between source row count and reference, I'd go with the "other approaches" section. If Oracle can't handle 5 thousand ad-hoc queries fired against it, it must not be the enterprise scale product I hear touted – billinkc Sep 17 '20 at 14:32