SSIS debugging your variable's value

From time to time, for debugging and troubleshooting your flow at run time you’ll need to check if the variable has the correct value or not, there’re several ways to check for the value of the variables inside the flow, You can add Break Points, a Scrip Task for you’re in the control flow and a Script Component in the Data Flow Task, let’s add a Script Task without configuring it as we’ll use it next. I’ll assume basic knowledge of SSIS, and skip how to create a project, solutions and the difference between Control Flow and Data Flows.

Let's create a variable

Open the variable’s windows by going to View --> Other Windows --> Variables. Click on “Add Variable” button as seen on Figure 0.

In the demo, we’ll create a DateTime data type variable called “DTLastWeek” , we’ll also use a custom NameSpace named “Demo”. For SSIS data types, please check
http://msdn.microsoft.com/en-us/library/ms141036(v=sql.105).aspx

Using Breakpoints in the Control Flow
The easiest and simplest to debug your variable value is to set a Break Point, it has several break conditions based on the task you’re setting the breakpoints on, for example when you’re using “ForEach Loop Container” you’ll get “Break at the beginning of every iteration of the loop”, and it’s simple a way to tell the SSIS control flow to take a break and pause to be able to debug the package. Breakpoints can be enabled at the package level, or at the level of the individual component. If break conditions are enabled at the task or container level, the breakpoint icon appears next to the task or container on the design surface of the Control Flow tab. If the break conditions are enabled on the package, the breakpoint icon appears on the label of the Control Flow tab. For further reading about each individual condition, please check
http://msdn.microsoft.com/en-us/library/ms140274%28v=sql.105%29.aspx

Let’s add a script task to the control flow, and set a breakpoint on it, by Right clicking on the Script Task and choose “EDIT BREAKPOINTS” for the “OnPreExecute “event which will get you the value before the task execution as shown in Figure 1, since our variable already has a preset value. You’ll see red dots on tasks indicate that breakpoints are configured on the task. To get a list of all the breakpoints in your package you can go to Debug --> Windows --> Breakpoints or click on Ctrl + Alt + B.

Run the package by clicking on the green arrow from the top bar or click on F5, when the package runs the breakpoint will pause the package and open the Locals window which will have all the variable’s values for you, if it’s not opened you could go to Debug --> Windows --> Locals as shown in Figure 2.

You can remove the breakpoints by clicking CTRL + SHIFT + F9 or you can go to Debug --> Delete All Breakpoints

Using a Script Task in the Control Flow
One of SSIS strengths is using a custom .NET code to leverage the package functionality, typically you won’t create a script task to check the variable’s value, but we’ll demo how to read and to write to variables inside a script task as a way to get the variable’s value.

There’s two ways to read and write variables in The Script Task, natively by selecting the variable as “ReadOnly” or “ReadWwriteVariables” using the GUI or by using the .NET code to lock your variables for read or write.

Let’s get our variable as “ReadWriteVariables” so we can change its value, by clicking on the ellipsis and selecting our “DTLasWeek” variable at the “Script Task Editor” as shown in Figure 3. This window has been improved in SQL 2012 to show only the user variables, instead of all package variables when you click on “ReadWriteVariables”.

After selecting the variable, select “Microsoft Visual Basic 2008” as the scripting language, then click on “Edit Script”, replace the your Main() Sub with the one shown in Figure 4 to basically read the variable’s value in a message just for debugging.

Using a Script Component in the Data Flow
The Script Component also gives you the power of working with variables in the data flow, also gives you the option to select your variables using the GUI, it has a similar GUI but slightly different properties, but again you can choose your variable as “ReadOnly” or “ReadWrite”.

Let’s add a “Data Flow Task”.

Drag a “Script Component” inside, select “SOURCE” as its type.

You need at least one output column, so hop to the “InputOutput” tab and add a column.

Add our variable as a “ReadWriteVariables” shown in Figure 5.

Replace your “PostExecute()” with the one shown in Figure 6, The PostExcute is the last step in the task after all the rows has been processed, you could use your variables to hold a column value for example, or the number of rows processed for example.

Conclusion
The demonstrated example above is yet been fairly simple and easy to follow, however it could get more complex and advanced by utilizing expressions and more variable of various data types, debugging your package at run is important to know as you'll need it from time to time.

SSIS debugging your variable's value

Comments (0)