Wednesday, August 1, 2012

Loop through each row in a table using "For Each" loop in SSIS

Hi All,

Recently I had to teach someone to run a For..Each container for all the records in a table, for a specific requirement we had at my work. I have documented the whole process so that you can understand that too, if you haven’t done that before. Kindly read through the entire blog and leave your responses or other suggestions in the comments section.

Note:  Before we begin, let me just make a small recommendation.. All heavy computing and heavy lifting should be done on the DB side.

Uses of this are limitless. In this instance, I am sending emails based on the data in the table, like Word's Mail merge feature.

Let us start with creating the table and inserting few rows into it..

Create table TestTable
(UserID Varchar(200) null,
email Varchar(200) null)

Insert into TestTable(UserID,email) values

Step 1.
Start Visual Studio, create a new SSIS project add a blank SSIS package add the following to the package:
1. A data flow task
2. A for..each container
3. Within that for..each container, add a send mail task.

Step 2. Edit the Data Flow task , Add a "ADO .NET source" and "Recordset Destination"

Step 3: Create the following variables in the context of Package (Global variable) EmailLoop - Type String, TestTable - Type Object, UserIDLoop - Type String.

Step 4: Set the properties of the ADO.NET source (I am assuming you will have some background on SSIS, so skipping the details), Select the ADO.NET connection, Data accessmode: Table or view, Name of the table or view : TestTable

Step 5: Select both the UserID and the email as output in the column selection

Step 6: Select the properties for RecordSet Destination.  Click on the variable name, and select  “TestTable” (This “TestTable” variable should be of “object” data type, otherwise you will get an error) .

Step 7: Click on the “Input Columns” and add both the columns.

Step 8: Now edit the For...Each container Task, and the properties as follows in the collection tab - Enumerator : Foreach ADO Enumerator, ADO Object source variable : User::TestTable. 

Step 9 : In the variable mappings tab, Select the variable you’ve added for UserID and email in there..

Step 10 : Edit the email task

In the SMTPConnection property, select and give the SMTP connection details

Step 11: In the Mail tab, set the appropriate properties..

Step 12 : In the expressions tab, set the following values:
MessageSource - "This email was sent to :  the user,"+  @[User::UserIDLoop]+ " with the email " + @[User::EmailLoop]
To Line - @[User::EmailLoop]

Now, we are ready to test it.. Insert few rows in to the “TestTable” in the SQL server, and you have just created your simple mail merge program.

If you have any questions or comments, kindly reach me through the comments section.


Jeremy Harlow said...

Thanks for the post, this helped! Don't forget to enable 'delay validation' in the SMTP task.

G said...

I used the same thing for grabbing specific files off an SFTP server. Thanks for the tutorial!

r RDD said...

thank you. this was very helpful! Question. In the message source, can you use line feeds or carriage returns? I think that is why I'm getting an error.

Leslie Lim said...

This is really an interesting topic. Congratulations to the writer. I'm sure a lot of readers having fun reading your post. Hoping to read more post from you in the future. Thank you and God bless!


Silvia Jacinto said...

I love your blog. Keep it up.Visit my site too.

Andrey Paltusov said...

Thank you. Very helpful for me now.

Priya said...

Thank you.I would like to know how to store each record of the table into separate excel files.