CODEWITHSUNDEEP
sql-serveroracle-databaseloopsobjectssis
d2907
d2907

Reputation: 902

Iterate and get a fixed number of result from an Object in SSIS

I've got this issue with a query in SSIS. From a table in SQL Server I'm getting over 25000 different identifiers. These identifier are associated to many values in a table in one Oracle Database. This is the schema that I have implemented for doing this.

enter image description here

The problem is that some days the identifiers can be over 45000, and at this point perform a loop for every one is not the best solution (It can take to much time to get the result). Previously I have performed another query where from the SQL statement, I am creating and sending a unique row with all the values concatenated and then I have recover this unique string from an object and use it to create the query in the ODBC Source that invoke the table in Oracle: something like this:

'Select * from Oracle_table' + @string_values

with @string_values = 'where value in (........)'. It works good because the number of values is small enough to be used, like 250. But in this case I can not use this approach because the number is really big and obviously the DBA of Oracle is going to cancel the query.

So I wonder, how can I iterate over the object getting only a few number of values everytime, something like 300 or maximun 500, to avoid the cancelation of the query but at the same time doing the minimun number of loops. Or if there another solution to this question I would appreciate your help.

Upvotes: 0

Views: 128

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Ok, what you can do is a hybrid of looping through every individual identifier and building a string to use with a "WHERE value IN (...)" clause.

Create a loop where you get as many identifiers as you can without causing a "WHERE value IN (...)" to get too big. So say you get the top 100 or 200 or however many it is.

EDIT: One way to do the above is use a variable for Loop Control. If you want 100 at a time, start the variable at 100. Use it to get the Identifiers with ROW_NUMBER() between 1 - 100 (the logic in your query would be something like: BETWEEN (MyVariable - 99) AND MyVariable). Then at the end of the loop, increment your variable's value by 100.

Your only loop is against the variable using this approach. Your query builds a string of identifiers. Here is an example of this:

Concatenate many rows into a single text string?

You use that string to build your Oracle query that uses WHERE value IN (The string of identifiers)

Run that "WHERE IN" query against Oracle, store that data either on SQL or in memory, and then loop and get the next batch of identifiers. Repeat the loop until you've gone through all the identifiers you need.

Upvotes: 2

Related Questions