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.