How to add a Watch Windows in SSIS

My post SSIS Progress / Execution Results Tab talked about few things related to debugging and thought to add another post on it, specifically on two windows provided by SQL Server Data Tools for observing values during debugging.

There are two windows that can be used for seeing values of variables and parameters: Locals and Watch Windows. The Locals windows allows us to see systems setting, values of variables and parameters that are currently in scope. The Watch window helps us to specify a specific item such as variable or parameter and track its value through out debugging. If you have multiple variable and parameters and you need to see how they are getting changed during the execution (or debugging), Watch window is the best. You can add multiple Watch windows for tracing multiple items.

Let'see how we can add a Watch and use it when debugging. Follow given steps for creating a package and testing;

1. Create a SSIS project and add a package.

2. Create a variable called TestVariable. Set the type as int.

3. Add a Script Task. Make sure that TestVariable is added under ReadWriteVariables of the Script Task and following code is set with the task.

public void Main()
{
    // TODO: Add your code here

 
    int x = Convert.ToInt32(Dts.Variables["User::TestVariable"].Value);
    x = x + 1;
    Dts.Variables["User::TestVariable"].Value = x;
    Dts.TaskResult = (int)ScriptResults.Success;
}

This code increases the value of the variable by one.

4. Copy the Script Task added and duplicate it two times. Now you have three Script Tasks. Connect them with Success Precedence Constraints.

5. Select the first Script Task and press F9 or use Debug -> Toggle Breakpoint menu for adding a breakpoint. Do the same for other two tasks.

6. Now start debugging. It stops at the first Script Task as we have set a breakpoint. For opening Locals window, use Debug -> Windows -> Locals menu item.

7. Once the Locals window is opened, navigate to find TestVariable. Right-click on it and add a Watch on it.

8. You should see a Watch window now. You will see that Watch window shows how value is getting changed when you continue with debugging.

SSIS Progress / Execution Results Tab

SQL Server Integration Services is The Enterprise ETL platform that supports on all ETLing scenario. Covering from small data transfer to large complex ETL solutions, it has become the number one on ETL solutions for many projects and organizations.

Just like other implementations with other tools, the packages we create with Integrations Services need to troubleshoot when it does not work as we expected. There are many way of troubleshooting, however, the most common technique is debugging. Debugging is the process of finding problems that occur during the execution, either at design stage or after deployment. We can debug SSIS package too.

There are various ways of debugging SSIS package at the development, will discuss it with different post, but let's focus on one common technique. When we run the package using SQL Server Data Tools, most of the events are recorded and can be seen with one of the Tabs in design window. This tab cannot be seen when you open the package for designing but it becomes visible as Progress when you execute and visible as Execution Results when you complete the execution. See the images below;

This tab is very useful as it explains how tasks and containers involve with the execution in a hierarchical manner. You can navigate via the tree and find out the problematic one if there is an issue.

There is a slight performance impact on this collection. Since it has to collect all information, it adds an overhead to the package execution. Even though it is a slight one, if you want, it can be disabled by toggling Debug Process Reporting menu item in the SSIS menu;