Using The Components
In this section you install the components you have created into the SSIS design environment so you can use them to build packages. You then learn how to debug the components so you can troubleshoot any coding issues they contain.
Installing the Components
Unlike previous versions of BIDS pre SQL Server 2012, there is no Choose Items dialog for SQL Server Data Tools SSIS components. To add a component to the SSIS Toolbox:
- Open SQL Server Data Tools and then create or open an SSIS solution.
- Create a new Data Flow Task and then double-click it to enter the Data Flow panel. The Wrox.Pipeline components will automatically appear in the SSIS Toolbox (see below screen shot).
The components show up automatically because they are copied to the %PROGRAMFILES%\Microsoft SQL Server\120\DTS\PipelineComponents directory in your project’s post-build event.
If the components are not displayed in the SSIS Toolbox, ensure that you are referencing the SQL Server 2014 versions of the references, not any of the previous versions of SQL Server, and that the post-build event in your project is copying the files to the correct directory for your installation of Integration Services.
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, this section explains what you need to know 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 need to use SQL Server Data Tools to do this. The second phase, which is the runtime experience, is slightly different. You can still use SQL Server Data Tools, 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 Transformation.
This section describes the process of debugging the component at design time. Open the Visual Studio Wrox.Pipeline C# project and set a C# breakpoint at ProvideComponentProperties (SSIS also has breakpoints, which are discussed further in Error and Event Handling Topic). Now create a new SSIS project in SQL Server Data Tools. In the package, add a Data Flow Task and double-click it. If your component is not in the Toolbox already, add it now.
You need to create a full pipeline in this package because you’ll be using it later when you debug the runtime. Therefore, get an OLE DB or ADO.NET Connection Manager and point it to the AdventureWorks database. Now add an OLE DB or ADO.NET Source adapter to the design surface and configure it to use the Connection Manager you just created. Point the source to one of the tables in AdventureWorks — perhaps Person.Person — and select the columns you want to use.
Before adding your new components to the designer, you need to attach to the SQL Server Data Tools instance (DevEnv.exe) from the Wrox.Pipeline project you’re working in so that it can receive the methods fired by the component. To do that, in the Visual Studio Wrox.Pipeline C# project, select Debug ⇒ Attach to Process. The Attach to Process dialog opens (see below screen shot), where you can choose what you want to debug, as well as which process.
The process you’re interested in is the package you’re currently building. This shows up in the Available Processes list as ProSSIS2014_ISProject – Microsoft Visual Studio (the name you see may differ). Just above this window is a small box containing the words “Managed (v4.0) code.” This tells the debugger what you want to debug in the component. Three options are available, which you can view by clicking the Select button to the right of the label: 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 Reverse String Transformation onto the design surface. Because one of the first things a component does after it is dropped into a package is call ProvideComponentProperties, you should immediately see your component break into the code in its design project, as shown in below screen shot.
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 red breakpoint circle on the left. You are now free to debug the component as you would 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, such as Locals, Autos, and Call Stack. These can help you get to the root of any debugging problems, but you don’t need to use them now.
To leave debugging mode, return to Visual Studio and select Debug ⇒ Stop Debugging.
Building the Complete Package
Because the package already has a source and Transformation Component on it, you just need to add a destination. First make sure you have both configured the Reverse String Transformation to reverse some of the columns by double-clicking it and selected the required columns in the custom UI (or the Advanced UI if you have not built the custom UI yet, which is discussed in Adding a User Interface to Your Component Topic).
In the SSIS Connections pane, create a new File Connection Manager, setting the Usage Type to Create File. Enter a filename in a location of your choice, and then close the Connection Manager dialog.
Drop the Destination Component you have just built onto the design surface and connect the output of the Reverse String Transformation to the input of the destination. Open the destination’s editor, and on the first tab of the Advanced Editor, set the File to Write property value to the name of the connection you just created. Flip over to the Input Columns tab in the editor, and select which columns you want to write to the output file.
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 SQL Server Data Tools designer. The other is by using DTExec and the package properties. Using SQL Server Data Tools is similar to the design-time method with a subtle variation.
You should now have a complete pipeline with the Reverse String Transformation in the middle. If you don’t, quickly create a pipeline like the one shown in below screen shot.
NOTE Instead of a real destination that writes to a file or database, it is often useful to write to a so-called trash destination. You can use a Row Count Transformation or Union All Transformation for this purpose.
You now 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 the Data Flow Task itself and select Edit Breakpoints. The Set Breakpoints dialog will appear, as shown in below screen shot.
To execute your SSIS package, press 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 for designtime debugging 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 below screen shot. You will probably see two of these processes listed; the one you want has Managed listed under the Type column (don’t attach to the process showing x86 as the type).
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. 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 Reverse String Component hits its PreExecute method, you should be able to debug what it is doing. In below screen shot, this user put a breakpoint on a line of code that enables him or her to look at the “input” variable in the Locals window.
That concludes your look at the first method for debugging the runtime. The second method involves SQL Server Data Tools indirectly because you need to create a package like this one that you can call later. After that, you don’t need SQL Server Data Tools at all. You do, however, still need the component’s design project open. Open your Visual Studio Reverse String C# project’s properties and look at the Debug tab on the left, which should look similar to below screen shot.
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 pass a parameter /FILE to DTExec. This tells DTExec the name and location of the package you just built. Make sure the file path to your package is valid, and ensure that you still have a C# breakpoint set on PreExecute, and press F5 in your project. A DOS window will appear where you will see some messages fly past; these 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 that it did when you were using SQL Server Data Tools. Why might you use one approach 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 SQL Server Data Tools. The other advantage is that you don’t need two tools open at the same time. You can focus on your component’s design project and not worry about SQL Server Data Tools at all.