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. In the latest SQL Server release, a whole host of methods exist for debugging SSIS packages and for helping you find the root cause of your problems.
SSIS packages are built in 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, from that point forward, you will be running Visual Studio in lieu of BIDS. 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 for debugging SSIS packages
The ability to set up breakpoints is a staple of debugging. SSIS lets you set up two different kinds of breakpoints: One is on packages, tasks and containers, and the other is 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 set up breakpoints on data flow tasks.
Let's first look at setting up breakpoints on packages, tasks or containers.
To set up 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 set up a breakpoint for a task or container, right click on the task or container you are interested in and select Edit Breakpoints. In both cases, you will be presented with the Set Breakpoints dialog shown in Figure 1.
Figure 1: The Set Breakpoint dialog for an SSIS Package.
In this dialog box, 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. 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, five or more times. When you run the package, it stops and highlights the task on which the breakpoint was triggered, as shown in Figure 2.
Figure 2: The breakpoint on "Data Flow Task" has been triggered.
As you can see, the breakpoint we set earlier has been triggered and you are given the yellow arrow over the active break point. At this time, you have access to all of the local variables, any watch variables you have configured as well as a view of the package's status since the break.
In addition to being able to set up breakpoints on packages, containers and tasks, you can configure them inside script tasks. There are two ways to do this: You can open the script in the script designer and either right click on the line you
are interested in and select Breakpoint > Insert Breakpoint. Or, you can 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 than a few times, or you can set them up 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 for debugging SSIS packages
So now, you can set up 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 -- 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. In Figure 3, you will see a Grid Data Viewer that has been configured on the "Child Rows" output of a conditional split.
Figure 3: A Grid Data Viewer configured on the output of a conditional split task.
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. Figure 4 shows the Grid Data Viewer I got when I ran my package.
Figure 4: A Grid Data Viewer as seen during package execution.
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 choose "Play," the package will continue to execute. 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 choose "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 present a lot of information about the inner workings of your packages. Data viewers and breakpoints just start to scrape the surface of debugging options inside SSIS packages, and with this short overview, you have enough insight to be able to get under the hood and fix some of the problems you may encounter in your packages.
| ABOUT THE AUTHOR: |
|
Eric Johnson (MCSE, MCITP: Database Administrator, MCSD) is a co-founder of Consortio Services and the primary Database Technologies Consultant. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. He has presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. Eric is active in the local SQL Server Community, serving as the president of the Colorado Springs SQL Server Users Group.
Copyright 2007 TechTarget
|
|
