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
Finally we
learn how to use RecordSet destination.
