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.
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
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.
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.
Thank you so much for providing information and insights about SSIS and the way its usage is shown as per Example is something which seems organic.
ReplyDeleteSSIS Postgresql Write
https://saglamproxy.com
ReplyDeletemetin2 proxy
proxy satın al
knight online proxy
mobil proxy satın al
6W8İ