Category Archives: Control Flow

For Loop in SSIS

After lots of failure attempts finally I am confident enough to write my first post. It took like 3 years for me to start writing this first post. And you know what, its not that simple!!.

Anyways feelings aside, and lets starts something which is of our interest.

Yeah we gona start with Loops in SSIS, well there are two types of loops available in SSIS 2005/2008

  1. FOR Loop : Use when you know the count of  iteration of  the loop
  2. FOREACH Loop : Use when you don’t know the count of Iteration of loop

Now I am not going to put much text to read, or give you detailed information on architecture of loops or SSIS, rather we will be learning here by examples.

So lets start with For Loops: <I will cover ForEach loop in my next article>

Lets say the requirement is everytime I execute the loop in my ssis package, three records will be inserted into a table named LoopsExample. So we will start with

1. Create a table named LoopsExample as shown

Create table LoopsExample(  Pkey int identity(1,1), IterationCount int,  DateLooped datetime)

Whenever the SSIS loop executes, it inserts three new records into the LoopsExample table with PKey as primary key, IterationCount as iteration number just to know how the iteration values gets change, and DateLoooped will show the date the record is inserted into the table

2. So here what we do in SSIS designer

  1. Create a package variable named @i
  2. Drop a For Loop container into the SSIS Control flow and double click to open For loop Editor.Image1
  3. Drop Execute SQL Task into For Loop container.Image1
  4. Configure Execute SQL task.Image1
  5. Set Parameter Mapping tab.Image1
  6. Here is the query to use in SQL Statement in Execute SQL task

Insert into LoopsExample (IterationCount,DateLooped)

Values(?,getdate())
.Image1 That’s it, on executing the package we get following output ..

As Expected 🙂 and I hope you have learned some thing out of this..

–By Learner..!!

Advertisements