For Container in SSIS with example

In this post I will show you how to configure For Container in SSIS with an example. This post will gives a clear picture on For container in SSIS & How it is differ from For Each(Previous Post) 

To know about Foreach Container in SSIS, Check my previous post on For Each Container -http://www.sqlandssrssolutions.com/2016/02/foreach-container-in-ssis-with-example.html/

So below I will create a simple package to demonstrate the use for loop container, which will insert a record in ForLoop table for every minute increment which we will configure in For Container as below.

Below is the step by step guide to configure For Container in SSIS.

First we will create a table in SQL, where we will insert a record for each minute till the For Loop ends.

--ForLoop Table Script

Create Table dbo.ForLoop

(

ID int identitY(1,1) not null ,

DT_Date datetime not null

)

    Once we will create the For Loop table next we will open Integration Service Project in SSIS and will drop a For container into it.

 Next give a Name to For Loop container for eg Looping Each  Minute from StartDate to EndDate

 Now we will start configuring the For container, so for that first we will create two variable in SSIS i.e. Start Date & EndDate as in screenshot

Next double Click on For Loop, Configure the for Loop as below Screenshot.

  Once done, You will get a warning message in the for Loop container as below.

Warning               1              Validation warning. Looping Each  Minute from StartDate to EndDate : The initialization expression is not an assignment expression: "@StartDate". This error usually occurs when the expression in the iterate expressions on the ForLoop is not an assignment expression.      forloop.dtsx       0              0

You can either Ignore it or set Delay validation to True to remove the warning message.

Once done with For Loop, next Drop an Execute SQL Task inside it.

Name Execute SQL Task like this- For Every Increment of minute Inserting Record in table

Double click on execute SQL Task and configure it as below Screenshot

 First create a connection to your Data source

Next Select  SQL Source Type- Direct Input

Next write the below query in SQL Statement

INSERT INTO ForLoop

 (DT_Date)

 VALUES(?)

Next click on Parameter mapping in Right hand side and add input variable StartDate as below

 Make sure the data type should be Date otherwise it will throw the below error

[Execute SQL Task] Error: Executing the query "INSERT INTO ForLoop

                         (DT_D..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

OR

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query “insert into ForLoop

                         values (?)” failed with the following error: “Invalid time format”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

Once done click ok and Run the Package, The package will run successfully as below.

Do a select * from dbo.ForLoop to verify the data, you will notice that for every minute there is a entry in ForLoop Table.

Explanation -While configuring For Loop below are three imp things.

Initialize expression-This is the Initialization expression from where our Loop will start i.e. StartDate= “27-03-2016 01:30”

Evaluate expression-This will Evaluate the expression till the loop ends i.e @StartDate<=@EndDate, This will run until the startDate become equal to endDate

Assign Expression-Every times when the For Loop runs it will add a minute in startdate until start date will be equal to End Date

Next in Execute SQL Task Every time when StartDate will incremented by 1 minute it will insert the Record in ForLoop table until the StartDate equals to EndDate i.e. 27-03-2016 01:30<= 27-03-2016 01:40

Therefore a total of 11 entries inserted into ForLoop Table when you will do a select * from dbo.ForLoop

Please write in comment box if in case you will face any Issue while performing the above steps, I will be happy to help you.