SSIS Design Pattern: Use Script Tasks for ETL Instrumentation

I consider scripting in SQL Server Integration Services (SSIS) my data integration “get out of jail free” card. Why? If I cannot find a way to accomplish some requirement using SSIS’s built-in objects – tasks, containers, Data Flow components, etc. – I can usually write .Net code to fulfill the requirement.

As stated in the SSIS Academy short course titled Troubleshooting SSIS, I almost always add to the Control Flow an SSIS Script Task that logs the initial state of parameters and variables when package execution starts. I find this information invaluable when troubleshooting failed SSIS executions.

Configuring a Script Task for ETL Instrumentation

Begin by adding an SSIS Script Task to the Control Flow:

Open the editor.
Click inside the ReadOnlyVariables property value textbox. An ellipsis is available:

Click the ellipsis to open the Select Variables window. Select the System::TaskName and System::PackageName variables:

Click the OK button to close the Select Variables window. Note the two variables we selected now appear in the ReadOnlyVariables value textbox:

Select the variables and copy them to the clipboard:

.Net Scripting

Click the Edit Script button to open the Visual Studio Tools for Applications (VSTA) .Net code editor. Scroll to public void Main() and select the text “TODO: Add your code here”:

Paste the contents of the clipboard:

I do this because mistyping variable names results in an error that’s not very intuitive. Also, .Net design-time validation has no way to test variable names for accuracy. It’s better to copy and paste than risk an error, in my opinion.

Declaring a .Net Variable

Begin a .Net variable declaration by typing “string “. Select the System::PackageName variable name from the comment and copy it to the clipboard:

Paste the clipboard contents after “string ” as shown here:

The .Net string variable declaration appears as shown:

Now, System::PackageName is not a valid .Net variable name.
System::PackageName is a valid SSIS variable name where “System” is the namespace and “PackageName” is the variable name.

Edit the .Net variable so it is named “packageName” as shown:

Initializing a .Net Variable

Our goal is to read the value of the SSIS variable System::PackageName into the .Net string variable named packageName. Assigning an initial value to a variable is known as initializing the variable.

To begin initializing the .Net string variable named packageName, add the “=” sign after the variable name (packageName). In C# (and similar languages), a single equal sign is an assignment operator.

Type “Dts.” – as shown next – and note IntelliSense presents a list of available methods and objects. Scroll to the object named “Variables” as shown:

You can append “Variables” to “Dts.” by double-clicking Variables or by pressing the Tab key when Variables is selected (as shown above). Add a beginning-bracket – [ – followed by a double-quote – “. Note the complementary closing bracket – ] – and double-quote – ” – are added automagically. Plus the cursor is in perfect position to paste the contents of clipboard (again):

Move to the end of this line of .Net code and type “.v”. IntelliSense kicks in – although my screen-capture software makes the context menu opaque – and the Value property is selected as shown:

This is important.

Next type “.t”. Notice the Value property is completed by IntelliSense – again, automagically – and IntelliSense also displays the only option available that begins with the letter “t” – ToString:

Complete the statement by typing “();”:

Rinse, Repeat

Use the same .Net variable declaration and initialization to declare and initialize the taskName .Net string variable:

Next, declare a .Net string variable named subComponent and use the packageName and taskName .Net string variables to initialize the value of subComponent:

Exercise your .Net variable declaration and initialization skills even more by declaring two more variables:

1. An int variable named informationCode and initialized to 1001
2. A bool variable named fireAgain initialized to true:

Finally, declare a .Net string variable named description and initialize it with the string

“I am ” + packageName

ETL Instrumentation via Information Event Message

Configure and raise an Information event using the following .Net statement:

Dts.Events.FireInformation(informationCode, subComponent, description, “”, 0, ref fireAgain);

Click the screenshot below to enlarge the image. Note the Dts.Events.FireInformation method takes six arguments:

1. informationCode [int]
2. subComponent [string]
3. description [string]
4. helpFile [string]
5. helpContext [int]
6. fireAgain [bool]

We use the .Net variables informationCode, subComponent, description, and fireAgain to supply four of the arguments. We supply literal values – “” and 0, respectively – to the helpFile and helpContext arguments.

When complete, our Main() method contains the following .Net code:

public void Main()
{
// System::TaskName,System::PackageName
string packageName = Dts.Variables[“System::PackageName”].Value.ToString();
string taskName = Dts.Variables[“System::TaskName”].Value.ToString();
string subComponent = packageName + “.” + taskName;
int informationCode = 1001;
bool fireAgain = true;

string description = “I am ” + packageName;
Dts.Events.FireInformation(informationCode, subComponent, description, “”, 0, ref fireAgain);

Dts.TaskResult = (int)ScriptResults.Success;
}

Test It!

Close the VstaProjects code editor by closing the window, then close the Script Task Editor by clicking the OK button.

Execute the SSIS package in the SQL Server Data Tools (SSDT) debugger by pressing the F5 key. If all goes as planned, you should see a successful debug execution:

Click the Progress tab to view the Information message:

Conclusion

ETL Instrumentation is an important part of data integration development with SSIS. It increases development time (slightly) to add ETL Instrumentation such as this but it’s worth it. When troubleshooting an SSIS package at 2:00 AM some months after deployment, ETL Instrumentation will save time.

The additional time spent during development is the opposite of technical debt, it’s a technical investment.

Learn more!

Get live, online training – from me! Check out the Enterprise Data & Analytics Training page.

Check out Introduction to Troubleshooting SSIS – a free sample of SSIS Self-Help at SSIS Academy!