I have observed one peculiar issue while pulling data from source in one shot i.e. SSIS throws “Connection time out” error after pulling ~80% of the total records. To rectify this issue, I started pulling data in chunks. There, I made use of “For loop Container” which I am going to explain below:
Let’s say I need to pull data on the basis of ColSID (assuming ColSID is the name of integer column) and I have 100 records in source. This is just an example but in real time you may millions of records. For Loop Container shall accept the format as:
Now, you have to write some logic and divide the ColSID column in a format acceptable by For Loop Container i.e. format similar to above mentioned table.
Note: For dividing the ColSID into table format please refer ‘SQL Helper’ section in my blog.
Below screenshot depicts the sample flow of execution:
Set SQL Task properties:
Double click SQL Task and set the properties as shown in below screenshot:
Go to Result Set and select the Range Variable name and set it as:
Now, since you have the format available, you will pass this format to For Loop container by selecting few properties as:
Note: The variable in which you are storing the Range should be of OBJECT Type. In our case it is ColSIDRange
Click ‘Variable Mappings’ and set the variables as shown in below screenshot:
Note: ColSIDStart and ColSIDEnd are integer variables.
Now double click the DFT task inside For Loop Container and set the properties as:
Now click the ‘Parameters’ tab and select parameters as:
Click OK and everything is set for you.
Map the source columns with your destination and start pulling the data.
Leave a comment if you like the blog and if it is helping you in any respect.
Happy Coding Cheers!!