Essential Coding, Debugging, and Troubleshooting Techniques - SSIS

You have now been all over the VSTA development environment and have been introduced to the different languages that move SSIS development into the managed code arena. Now, it is time to dig into some of the techniques for hardening your code for unexpected issues that may occur during runtime, and to look at some ways to troubleshoot SSIS packages. Any differences between the Script Task and the Script Component for some of these techniques are highlighted.

 

Structured exception handling (SEH) enables you to catch specific errors as they occur and perform any appropriate action needed. In many cases, you just want to log the error and stop execution, but in some cases you may want to try a different plan of action, depending on the error.

 

This trivial example attempts to read the contents of the file at C:data.csv into a string variable. The code makes some assumptions that might not be true. An obvious assumption is that the file exists. That is why this code was placed in a Try block. It is trying to perform an action that has the potential for failure. If the file isn’t there, a System.IO.FileNotFoundException is thrown. A Try block marks a section of code that contains function calls with potentially known exceptions. In this case, the FileSystem ReadAllText function has the potentia l to throw a concrete exception.

 

The Catch block is the error handler for this specific exception. You would probably want to add some code to log the error inside the Catch block. For now, the exception is sent to the message box as a string so that it can be viewed. This code obviously originates from a Scripting Task, as it returns a result. The result is set to Failure, and the script is exited with the Return statement if the exception occurs. If the file is found, no exception is thrown, and the next line of code is executed. In this case, it would go to the line that sets the TaskResult to the value of the Success enumeration, right after the End Try statement.
 

If an exception is not caught, it propagates up the call stack until an appropriate handler is found. If none is found, the exception stops execution. You can have as many Catch blocks associated with a Try block as you wish. When an exception is raised, the Catch blocks are walked from top to bottom until an appropriate one is found that fits the context of the exception. Only the first block that matches is executed. Execution does not fall through to the next block, so it’s important to place the most specific Catch block first and descend to the least specific. A Catch block specified with no filter will catch all exceptions. Typically, the coarsest Catch block is listed last. The previous code was written to anticipate the error of a file not being found, so not only does the developer have an opportunity to add some recovery code, but the framework assumes that you will handle the details of the error itself. If the same code contained only a generic Catch statement, the error would simply be written to the package output. To see what this looks like, replace the Catch statement in the preceding code snippet with these:
 

C#

VB

Catch
 

In this case, the error would simply be written to the package output like this:
 

The full stack is omitted for brevity and to point out that the task status shows that it failed.

 

Another feature of structured error handling is the Finally block. The Finally block exists inside a Try block and executes after any code in the Try block and any Catch blocks that were entered. Code in the Finally block is always executed, regardless of what happens in the Try block and in any Catch blocks. You would put code to dispose of any resources, such as open files or database connections, in the Finally block. Following is an example of using the Finally block to free up a connection resource:

 

 

In this example, the Finally block is hit regardless of whether the connection is open or not. A logical If statement checks whether the connection is open and closes it to conserve resources. Typically, you want to follow this pattern if you are doing anything resource intensive like using the System.IO or System.Data assemblies.

 

NOTE For a full explanation of the Try/Catch/Finally structure in Visual Basic or C#, see the language reference in MSDN .

Debugging is an important feature of scripting in SSIS. You can still use the technique of popping up a message box function to see the value of variables, but there are more sophisticated techniques that will help you pinpoint the problem. Using the Visual Studio Tools for Applications environment, you now have the capability to set breakpoints, examine variables, and even evaluate expressions interactively.

Breakpoints enable you to flag a line of code where execution pauses while debugging. Breakpoints are invaluable for determining what’s going on inside your code, as they enable you to step into it to see what’s happening as it executes.

 

 

NOTE A new feature since Integration Services 2012 is the ability to debug Script Components, which includes breakpoints and step abilities.

You can set a breakpoint in several ways. One way is to click in the gray margin at the left of the text editor at the line where you wish to stop execution. Another way is to move the cursor to the line you wish to break on and press F9. Yet another way is to select Debug ? Toggle Breakpoint.
 

To continue execution from a breakpoint, press F10 to step to the next line, or F5 to run all the way through to the next breakpoint. When you have a breakpoint set on a line, the line has a red highlight like the one shown in below screen shot (though you can’t see the color in this Screenshot).

 

 

 

When a Script Task has a breakpoint set somewhere in the code, it will have a red dot on it similar to the one in below screen shot.

 

 

Previously, you looked at using the Visual Studio Tools for Applications environment to debug a Script Task or Script Component using breakpoints and other tools. Alternatively, you can inspect the data as it moves through the Data Flow using the Row Count Component or a Data Viewer.
 

The Row Count Component is very straightforward; it simply states how many rows passed through it. The Data Viewer contains additional information if desired. To add a Data Viewer, select the connector arrow that leaves the component for which you want to see data. In the previous example, this would be the connector from the Script Component to the Conditional Split Task. Right-click this connection, and select Enable Data Viewer. This automatically adds a Data Viewer that will show all columns on the stream. To remove any columns, double click the connector and select the Data Viewer menu. Below screen shot shows how to turn on the Data Viewer on the Data Flow Path.

how to turn on the Data Viewer on the Data Flow Path
                 

Now when you run this package again, you will get a Data Viewer window after the Script Component has executed. This view will show the data output by the Script Component. below screen shot shows an example. Click the play button to continue package execution, or simply close the window.
                     
 

While using the Data Viewer certainly helps with debugging, it is no replacement for being able to step into the code. An alternative is to use the FireInformation event on the ComponentMetaData class in the Script Component. It is like the message box but without the modal effect.
 

The SQL Server Data Tools environment provides you with some powerful views into what is happening with the execution of your code. These views consist of three windows: the Autos window, the Locals window, and the Watch window. These windows share a similar layout and display the value of expressions and variables, though each has a distinct method determining what data to display.

The Locals window displays variables that are local to the current statement,as well as three statements behind and in front of the current statement. For a running example, the Locals window would appear (see below screen shot).
                
 

Watches are another very important feature of debugging. Watches enable you to specify a variable to watch. You can set up a watch to break execution when a variable’s value changes or some other condition is met. This enables you to see exactly when something is happening, such as a variable that has an unexpected value.
 

To add a watch, select the variable you want to watch inside the script, rightclick it, and select Add Watch. This will add an entry to the Watch window.
 

You can also use the Quick Watch window, accessible from the Debug menu, or through the Ctrl+Alt+Q key combination. The Watch window shown in below screen shot is in the middle of a breakpoint, and you can see the value of Iterator as it is being assigned the variable value of 2.

 

This window enables you to evaluate an expression at runtime and see the result in the window. You can then click the Add Watch button to move it to the Watch window.
 

The Immediate window enables you to evaluate expressions, execute procedures, and print out variable values. It is really a mode of the Command window, which enables you to issue commands to the IDE. Unfortunately, this too is useful only when you are within a breakpoint, and this can be done only within a Script Task.

NOTE If you can’t find the Immediate window but see the Command window, just type the command immed and press Enter.

 

The Immediate window is very useful while testing. You can see the outcome of several different scenarios. Suppose you have an object obj of type MyType, and MyType declares a method called DoMyStuff() that takes a single integer as an argument. Using the Immediate window, you could pass different values into the DoMyStuff() method and see the results. To evaluate an expression in the Immediate window and see its results, you must start the command with a question mark (?):

?obj.DoMyStuff(2)
“Hello”

Commands are terminated by pressing the Enter key. The results of the execution are printed on the next line. In this case, calling DoMyStuff() with a value of 2 returns the string “Hello.”

 

You can also use the Immediate window to change the value of variables. If you have a variable defined in your script and you want to change its value, perhaps for negative error testing, you can use this window, shown in below screen shoot.
                           
In this case, the value of the variable greeting is printed out on the line directly below the expression. After the value is printed, it is changed to “Goodbye Cruel World.” The value is then queried again, and the new value is printed. If you are in a Script Task and need to get additional information, this is a useful way to do it.