Process X number of records at a time in SSIS

[dmusson17]

Hello,

I am getting data from a remote source via an odbc driver in a DataReader Source. I am limited to selecting 2000 records at a time but need to select many more on a regular basis. The recommended way of getting around this is querying once to get the ID numbers that I need (I can do an unlimited query when only selecting ID and recmoddate) and then use that result set to build a subsequent select statement that select where id# in id1, id2, id3, ... id2000.

Then I would need to create the next select statement to get ids 2001-4000, and so on.

I am having a hard time conceptualizing how to accomplish this in SSIS. Do I utilize a few for loop containers and some variables or is there a more desirable way to accomplish this?

Any guidance is much appreciated.

Dave

 

[SQLBill]

Are your ID numbers in sequence? If so,
1. get the highest and lowest ID numbers
2. put those values into variables (@lowID, @highID)
3. then set a counter (@IDcntr) to the @lowID.
4. lastly do a IF @IDcntr < @highID followed by your SELECT <code> WHERE ID > @IDcntr AND ID < (@IDcntr + 2000). At the end of the SELECT increment the @IDcntr (SET @IDcntr = @IDctnr + 2000).

That is untested, but should do what you need.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875