Published on

August 21, 2010

Building an SSIS Package to Check for Changes in Table Definitions

As a SQL Server developer, there may be times when you need to build a package that checks for any changes in table definitions in a source system and sends a notification email listing the changed tables. In this article, we will explore one way of accomplishing this using simple SSIS control flow components.

Step 1: Retrieving your list of tables

First, we need to retrieve the list of tables from the source system. We can use a simple SQL query to populate an ADO result set with the table names:

SELECT 'My first Table' AS table_nm
UNION
SELECT 'My second Table' AS table_nm
UNION
SELECT 'My Third Table' AS table_nm

Make sure to set the ResultSet property to “Full Result Set”. We will store this result set in an object variable called table_list_object.

Step 2: Looping Over the Result Set

Once we have the list of tables stored in our object variable, we can loop over it using the Foreach Loop container. 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

In order to build a comma separated list of tables, we will use an Execute SQL Task. We need a string variable called csv_table_list_str to append the table names as they are iterated through the loop. The Execute SQL Task will use a dynamic SQL stored in a string expression variable called sql_str. The expression will append the current table name to the list of existing table names.

"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

Now, we need to generate the email message that will contain the list of tables. We can strip out the original comma in the csv_table_list_str variable using a simple SUBSTRING function. To generate the message dynamically, 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

Finally, we can use the Send Mail Task to send the email containing the list of tables. Set up the SMTP connection manager and configure the Send Email task. To ensure that emails are only sent if there were changes in the ADO object, we can add a boolean variable called tables_exist_flg using an Execute SQL Task inside the Foreach loop. This boolean variable can then be used as a precedence constraint on the Send Mail Task.

By following these steps, you can build an SSIS package that checks for changes in table definitions and sends a notification email with the list of changed tables. The package provides a simple and efficient way to automate this process.

Feel free to download the sample package attached to this article and configure the SMTP connection manager and Send Email task to test it out.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.