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
('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.


7 comments:

Unknown 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!

Unknown 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.

Andrey 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.

raynnowui21 said...

That is the fitting blog for anyone who desires to seek out out about this topic. You realize a lot its nearly arduous to argue with you (not that I actually would want…HaHa). You positively put a brand new spin on a subject thats been written about for years. Nice stuff, just nice! online casino bonus

chandra sekhar reddy peddireddy said...

Hi I tried the same but i used "script task" inside the "for each loop container" instead of "Send Email task". Looping is not working. why it's not working i do not understand. getting follwing error after 1st loop.

Error:0x1 at Send emails to eligible customers(Script task): System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to using an instance of an object.

i tried with script task to show variable value using MessageBox.Show(Dts.variables["User::Cust_Email"].ToString() ); Loop is working for 5times for 5 rocords.