by Eric Johnson

New to SQL Server 2005, SQL Server Integration Services (SSIS) adds an enterprise level extract, transform, and load (ETL) tool to the ever growing suite of SQL Server tools. SSIS replaces DTS, which was only around for two releases of SQL Server and grabbed limited following. Besides being slow and a little tricky to work with, DTS didn’t offer any easy ways of debugging packages when you had problems. With SSIS comes a whole host of methods for debugging your packages; helping you to get to the root cause of your problems.

The environment in which you build SSIS packages is called the SQL Server Business Intelligence Development Studio (BIDS), which comes with the SQL Server 2005 client tools. In reality, BIDS is just a stripped down version of Visual Studio. In fact, if you install Visual Studio on the same box as BIDS, you will, from that point forward, notice that you are running Visual Studio in lieu of BIDS. Okay. So what does this mean for debugging? It means that you have the power of Visual Studio at your fingertips when you are developing your SSIS packages.

Breakpoints

            One of the staples of debugging is the ability to setup breakpoints. SSIS let’s you setup two different kinds of breakpoints; on packages, tasks and containers, or inside of script objects. Once set, the execution of your SSIS package will stop at breakpoints and allow you to view the package in a paused state. When working with breakpoints, keep in mind that they can only be set on items in your control flow; you cannot setup breakpoints on data flow tasks. Let’s first look at setting up breakpoints on packages, tasks or containers.

            To setup a breakpoint on a package, you must go to the Control Flow tab, right-click on the background of the package, and select Edit Breakpoints. If you want to setup a breakpoint for a task or container, right-click the task or container you are interested in and select Edit Breakpoints. In either case, you will be presented with Set Breakpoints dialog.With this dialog, you can pick the Break Condition and the Hit Count for that condition. In this example, I have a data flow task set to break when it receives the OnPreExecute event. You notice there are options for hit count also available. In this example, I am telling the package to break after the task in question receives the OnPreExecute event, which is triggered just before the task runs, 5 or more times. When you run the package, it stops and highlights the task on which the breakpoint was triggered. The breakpoint we set earlier has been triggered and you are given the yellow arrow over the active break point. At this point you have access to the all the local variables, any watch variables you have configured, as well as a view of the package’s status as of the break.

            In addition to being able to setup breakpoints on packages, containers, and tasks, you can configure them inside of script tasks. To do this, you open the script in the script designer and either right-click the line you are interested in and select Breakpoint > Insert Breakpoint, or left click once in the left hand margin of the editor next to the line on which you want to break. Like before, the SSIS package will pause when your breakpoint is encountered.

            The sky is the limit on using your breakpoints. You can set them up to trigger if your “error logic” is called more then a few times, or just to be able to see what the package is doing during a problem spot. These breakpoints are stripped off when you compile the package to run from Integration Services, so don’t worry about affecting the production package when it comes time to elevate.

Data Viewers

            So you can setup breakpoints on your Control Flow, but what if you want to troubleshoot problems inside of a data flow? That is where Data Viewers come in handy. A Data Viewer, of which there are four flavors, can be configured on any of the Data Flow tasks. By right-clicking a path (one of the arrows between tasks) and selecting Data Viewers, you are presented with the Data Viewers section of the Data Flow Path Editor. From here, you can click Add and then select a Grid, Histogram, Scatter Plot, or Column chart to be displayed. The type of Data Viewer you pick will be largely dependent on the type of data you are viewing and what you need to know. In this case, I just want to see the data as it exists after the conditional split task. When you run the package, it will actually break at the data viewer and show you the data in the format you selected. At this point I can examine my data to ensure that things are running as expected or I can look for bad data that may be causing additional problems. In order to get the package going again, I can click the “play” button or click Detach. If I click play, the package will continue to execute, and the next time this same task is run, during a loop for example, I will get another data viewer with the current data. If I click Detach, the package will continue to run leaving my data viewer untouched. In this case, if the same task runs again in a loop, I will NOT get a new data viewer.

            SSIS offers several new methods of debugging. Two of the easiest methods to use are breakpoints and data viewers. Both of these methods will offer you a lot of information about the inner workings of your packages. Data viewers and breakpoints just start to scrape the surface of debugging inside SSIS packages, but hopefully this short overview has given you enough insight to be able to get under the hood and fix some of the problems you may encounter in your packages.

Copyright 2000  by TechTarget.com, 117 Kendrick Street, Suite 800, Needham, MA 02494. Reprinted by permission of TechTarget.com.

Digg It