Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Parsing an ADO result set into a comma separated string in SSIS

By Garima Sharma,

Background

I recently had a need to build a package that would check for any changes in table definitions in a source system and send a notification email listing the changed tables. One option was to use a script task but I wanted to see if there were other ways to do simple programming using simple SSIS control flow components. This article presents one way of parsing values in an ADO result set into a comma separated list.

Building the SSIS Package

Step 1: Retrieving your list of tables

Let's just use this simple SQL so that we can populate our ADO result set with data:

select 'My first Table' as table_nm
union
select 'My second Table' as table_nm
union
select 'My Third Table' as table_nm

Screenshot of a simple execute SQL task that returns a list of table names.

Note that the ResultSet is set to "Full Result Set". We will be storing this into an object variable called table_list_object.

Step 2: Looping Over the Result Set

Once the list of tables is stored in our object variable, we can loop over it using the Foreach Loop container.

Let's create a string variable called current_table_in_loop_str to store the current table name being read by the loop.

Step 3: Building the comma separated list using Execute SQL Task

We will need a string variable to use for appending the table names as they are iterated through the loop. Let's call that csv_table_list_str

We will be using SQL to build our comma separated list of tables as they are iterated through the loop. An 'Execute SQL Task' will use a dynamic SQL stored in a string expression variable called sql_str. The expression basically appends the current table name to the list of existing table names and will have the following value:

"select '"+ @[User::csv_table_list_str] +","+ @[User::current_table_in_loop_str]+"' as table_list"

The results of this execute SQL task will be stored back into the csv_table_list_str variable.

Step 4: Generating your Email message

All we have to do now is to strip out the original comma in our csv_table_list_str variable which can be done using a simple SUBSTRING function. We can do that while we build our email message. To generate this message dynamically, we will create a string expression variable called mail_message_txt. The expression value will be:

"Here are the list of tables in the ADO object: " + SUBSTRING( @[User::csv_table_list] , 2, LEN(@[User::csv_table_list])) 

Step 5: Sending your email

We can now use the Send Mail Task to send our email about the list of tables.

Send Mail Task

The package so far looks like this:

Package so far

We can finally add logic to only send emails if there was anything found in the ADO object by setting a boolean variable called tables_exist_flg using an Execute SQL Task inside the ForEach loop:

SQL to set boolean flag

We can then use this boolean to add a precedence constraint on the Send Mail Task

Expression on Constraint

The final SSIS package looks like this:

Final Package

The sample package is also attached, you will just need to configure the SMTP connection manager and your Send Email task to test this out.

Resources:

Send_email_with_list_of_tables.dtsx
Total article views: 5817 | Views in the last 30 days: 11
 
Related Articles
FORUM

referencing Package variables in sp_send_dbmail

Using package variables for email recipient and subject

FORUM

Package corruption - variables disappear - how to rebuild package?

Variables have disappeared in variables window, but are still in Package Explorer. Can't load /buil...

FORUM

Provide variables for the from and to addresses in Email Task

SSIS Variables and Email Task

FORUM

can I export data from a package variable object to an SQL destination?

Export variable object table data to OLE DB

FORUM

How, where do you use the expression builder at the package builder to write to a Package (Global Variable)?

Objective to pass or reference a variable local to the task to the Package variable

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones