I want to insert into DB2 Database from SQL server using SSIS. The insert should happen multiple times in a day. Approximately need to insert 1 million records and needs to inserted short span. Tried using conditional split but need to find is it possible to have dynamic split based on the row count. for example if there are 50000 records there should be split of 10 with each batch size of 5000. Is there any possible bulk import into DB2 tables
1 Answer
If you would like me to expand on my answer, please feel free to ask for more technical details. Here is the initial idea:
Add a Execute SQL Command component is SSIS which get the min(Id) and max(id) or row count of the the data you want to insert into DB2.
- Save the min and max values in two Variables.
- Define another variable as you Pointer.
- Create an If Loop in SSIS which the looping condition is when @pointer < @max
- Get Top n of the records you want to insert and update the @pointer to the latest ID --> @pointer+n
This idea is suitable for situations where your IDs are consecutive and there are no missing or deleted numbers in between.
However, it can also be adapted for situations where the IDs are not in a consecutive order with using ROW_NUMBER() which in your case might reduces the performance a little bit while we are loading the data. But after loading the data into RAM, the rest will be fine.
3 Comments
Maevrick
Can you brief me more about your idea. I have consecutive numbers how to use if loop in SSIS what is the component used for it
El.Hum
@Maevrick The video in this link is showing a very complete explanation of how to define a conditional loop in SSIS using variables: youtu.be/eE-d0g5V2aM?si=iD3xEFgbdEl6yf2R hope this would be helpful.
El.Hum
btw the name of the component is For Loop Container.