Debugging Components


Debugging components is a really great feature of SSIS. If you are a Visual Studio .NET developer, you should easily recognize the interface. If you're not familiar with Visual Studio, hopefully this section will allow you to become proficient in debugging your components.

There are two phases for debugging. The design-time can be debugged only while you're developing your package, so it makes sense that you will need to use BIDS to do this. The second experience, which is the runtime experience, is slightly different. You can still use BIDS, though, and when your package runs, the component will stop at breakpoints you designate. You need to set up a few things first, though. You can also use DTExec to fire the package straight from Visual Studio. The latter method saves you the cost of invoking another instance of Visual Studio.

The component you are going to debug is the Reverse String transform.

Design-Time

You will now jump straight in and start to debug the component at design-time. Open the component's design project and set a breakpoint at ProvideComponentProperties (breakpoints are discussed in Chapter 13). Now create a new SSIS project in BIDS. In the package, add a Data Flow task and double- click on it. If your component is not in the Toolbox already, add it now.

Note

To add a component to the Toolbox, right-click on the Toolbox and select Choose Items from the context menu. When the Choose Toolbox Items dialog box appears, click the SSIS Data Flow Items tab and scroll down until you see the component. Check your new component and click OK. When you go back to the Toolbox, you should see your new component.

You need to create a full pipeline in this package because you'll be using it later on when you debug the runtime, so get a Connection Manager and point it to the AdventureWorks database. Now add a source adapter to the design surface and configure it to use the Connection Manager you just created. It's now time to add your component to the designer. However, before you do that, you need to tell the component's design project to attach to the devenv.exe process you're working in so that it can receive the component's methods being fired. The way you do that is as follows. In the design project, select Tools Attach to Process. The Attach to Process dialog box opens (see Figure 14-7), which allows you to choose what you want to debug as well as which process.

image from book
Figure 14-7

The process you're interested in is the package you're currently building. This shows up in the Available Processes list as Integration Services Project 1 — Microsoft Visual Studio (the name you see may differ). You can see just above this window a small box containing the words "Managed Code." This tells the debugger what you want to debug in the component. There are a number of options available, and if you click the Select button to the right of the label, you'll be able to see them. They are Managed, Native, and Script.

Highlight the process for your package and click Attach. If you look down now at the status bar in your component's design project, you should see a variety of debug symbols being loaded. Go back to the SSIS package and drop the ReverseString transform onto the design surface. Because one of the very first things a component does when it gets dropped into a package is call ProvideComponentProperties, you should immediately see your component break into the code in its design project, as shown in Figure 14-8.

image from book
Figure 14-8

As you can see, the breakpoint on ProvideComponentProperties in the component's design project has been hit. This is indicated by a yellow arrow inside the breakpoint red circle. You are now free to debug the component as you would with any other piece of managed code in Visual Studio.NET. If you're familiar with debugging, a number of windows appear at this point at the bottom of the IDE, things like "Locals," "Autos," and "Call Stack." These can help you get to the root of your problem, but you do not use them here. Don't be afraid of them.

Runtime

As promised, in this section you are going to look at two ways of debugging. As with design-time debugging, the first is through the BIDS designer. The other is by using DTExec and the package properties. Using BIDS is similar to the design-time method with a subtle variation.

You should now have a complete pipeline with the ReverseString transform in the middle. If you don't, quickly make up a pipeline like in Figure 14-9.

image from book
Figure 14-9

Note

The Trash Destination you see terminating this pipeline is a simple destination adapter that requires no setup at all. Just drop it onto the design-sheet and go. It is used as a development aid, when you wish to test a partially completed workflow and you need a destination to quickly terminate the flow. The component is freely available for download from www.konesans.com or www.sqlis.com.

You then need to add a breakpoint to the Data Flow task that is hit when the Data Flow task hits the OnPreExecute event. You need to do this so that you can attach your debugger to the correct process at runtime. Right-click on the task itself and select Edit Breakpoints. The Set Breakpoints dialog box will appear, as shown in Figure 14-10.

image from book
Figure 14-10

You are now ready to execute your package. Hit F5 and allow the breakpoint in the Data Flow task to be hit. When you hit the breakpoint, switch back to the component's design process and follow the steps detailed earlier when debugging the design-time in order to get to the screen where you chose what process to debug.

When you execute a package in the designer, it is not really the designer that is doing the work. It hands off the execution to a process called DtsDebugHost.exe. This is the package that you want to attach to, as shown in Figure 14-11.

image from book
Figure 14-11

Click Attach and watch the debug symbols being loaded by the project. Before returning to the SSIS package, you need to set a breakpoint on one of the runtime methods used by your component, such as PreExecute. Now return to the SSIS project and press F5 again. This will release the package from its suspended state and allow the package to flow on. Now when the ReverseString component hits its PreExecute method, you should be able to debug what it is doing. In Figure 14-12, the user is checking to make sure that the LineageID of a column is being retrieved correctly and is ready to be used in the ProcessInput method that follows.

image from book
Figure 14-12

That concludes your look at the first method for debugging the runtime. The second method involves BIDS indirectly because you need to create a package like this one that you can call later. After that, you do not need BIDS at all. You do, however, still need the component's design project open. Open your project's properties and look at the Debug tab on the left, which should look similar to Figure 14-13.

image from book
Figure 14-13

As you can see, you have said that you want to start an external program to debug. That program is DTExec, which is the new and more powerful version of DTSRun. On the command line, you will pass a parameter /FILE to DTExec. This tells DTExec the name and location of the package you just built. Make sure you still have a breakpoint set on PreExecute, and hit F5in your project. A DOS window will appear and you will see some messages fly past, which are the same messages you would see in the designer. Eventually you will get to your breakpoint, and it will break in exactly the same way as it did when you were using BIDS. So, why might you use one over the other? The most obvious answer is speed. It is much faster to get to where you want to debug your component using DTExec than it is doing the same in BIDS. The other advantage is that you do not need to have two tools open at the same time. You can focus on your component's design project and not have to worry about BIDS at all.



Professional SQL Server 2005 Integration Services
Wireless Java : Developing with Java 2, Micro Edition
ISBN: 189311550X
EAN: 2147483647
Year: 2006
Pages: 182

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net