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

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.

The package so far looks like this:

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:

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

The final SSIS package looks like this:

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.