Working with Loops in SSIS

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:

ColSIDStartRange ColSIDEndRange
1 20
21 40
41 60
61 80
81 100

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:

Untitled1

Set SQL Task properties:

Double click SQL Task and set the properties as shown in below screenshot:

Untitled3

Go to Result Set and select the Range Variable name and set it as:

Untitled4

 

Now, since you have the format available, you will pass this format to For Loop container by selecting few properties as:

Untitled2

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:

Untitled5

Note: ColSIDStart and ColSIDEnd are integer variables.

Now double click the DFT task inside For Loop Container and set the properties as:

Untitled6

 

Now click the ‘Parameters’ tab and select parameters as:

Untitled7

 

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!!

 

Advertisements

One thought on “Working with Loops in SSIS

  1. Pingback: Using File System Task | ssisblog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s