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
('Dinesh','example@example.com')

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.