Home > SSIS Best Practices > SSIS – ETL Audit Table with Variables Passed to SQL Task

SSIS – ETL Audit Table with Variables Passed to SQL Task

Goal

  • Create an ETL audit table (tblETLAudit)
  • Use an Execute SQL Task to enter entries into the ETL audit table
  • Need to pass system and user variables to the Execute SQL Task

Issues

There are 4 system variables, and one user variable I want to pass to the Execute SQL Task. To accomplish this, I just need to create one user variable varETLMode, and pass all 5 variables to the Execute SQL Task as parameters. In addition to these 5 variables, I need to populate a few more fields on the tblETLAudit table that come from the database.

image 

Here is my table definition:

image

Then I found out that mixing the T-SQL variables with packages parameters do not work. Script like this will generate error in an Execute SQL Task.

image

Solution

Instead of populating the audit table in one step, I have to create two SQL tasks, with one creating a new entry, another one update the last entry with the number from the database.

But first, how do I pass the system or user variables into a SQL task as parameters?

Pass the system or user variables into a SQL task as parameters

Step 1: Create the user variable in the variable window

image

Step 2: Create a SQL task to populate the variable varETLMode from a SQL table

image

Set the ResultSet to Single row, and enter the SQL script in the SQLStatement.

image

In the Result Set tab, enter the field name as the Result Name (needs to be the same as the field name in the SQL script), and the user variable in Variable Name.

image

Step 3: Create two SQL Task to pass the variables as SQL parameters, create an entry in the audit table, then update the entry with new numbers from database

image

Step 4: For the first task above, create 5 parameter mappings as below. Parameter Name is something you must understand. There is a detail discussion here at MSDN. Since I am using the OLE DB connection, I cannot use parameter names to identify each parameter, instead, I have to use positions. Later in the SQL script, parameters are represented by question marks, not by their names.

image

In the SQLStatement, enter the script as below. Note the question mark here. The first question mark will represent variable system::PackageName, the 2nd question mark will represent system::VersionMajor, and so on.

image

Step 5: For the 2nd SQL Task, enter the following script in SQLStatement to update the entry with some numbers I need in the audit table.

image

Categories: SSIS Best Practices

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: