RecordSet Destination in SSIS

The RecordSet destination does not save data to an external data source. Instead, the RecordSet destination saves data in memory in a RecordSet that is stored in an Integration Services package variable of the Object data type. After the RecordSet destination saves the data, you typically use a Foreach Loop container with the Foreach ADO enumerator to process one row of the RecordSet at a time. The Foreach ADO enumerator saves the value from each column of the current row into a separate package variable. Then, the tasks that you configure inside the Foreach Loop container read those values from the variables and perform some action with them.

Let’s learn how to use RecordSet destination

Here I am taking a simple scenario where I am Implementing RecordSet destination. We are getting CSV file from the client, that files are having the email id subject and body (message). We need to send the mails to respective stakeholder.

Below is the CSV contain

Open the SSDT.

Package design

I am taking the Data flow task.

In Data flow I am taking the Source as Flat file source.

And the selecting delimiter as comma (,)

Click OK.

Now for storing the result I am taking the RecordSet destination.

For storing the value I am creating one Object type variable.

Now we map the variable value.

And also go to the Input Columns tab and select the column.

  

Click ok l. all value of CSV file is now stored on the dataset.

  

Now I am going to control flow and taking the Foreach loop container to getting one by one email id and sending the mail to respective stakeholder.

Selecting Enumerator as ForEach ADO Enumerator and selecting ADO object source variable.

Now we need to map the variables.

Click ok.

For sending the mail, there are two way either we use send mail Task or use script task.

Here I am using script task.

  

In the script task I am writing the C# code to send the mail.

Here I am seeing the values.

Read the below blog to send the mail using the script task.

How to send the mail in SSIS using script task. Read here

http://bageshkumarbagi-msbi.blogspot.in/2014/12/script-task.html

Finally we learn how to use RecordSet destination.