• Article

Building, Deploying, and Debugging Custom Objects

After you have written the code for a custom object for Integration Services, you must build the assembly, deploy it, integrate it into SSIS Designer to make it available for use in packages, and test and debug it.

Steps in Building, Deploying, and Debugging a Custom Object for Integration Services

You have already written the custom functionality for your object. Now you have to test it and to make it available to users. The steps are very similar for all the types of custom objects that you can create for Integration Services.

Here are the steps that you follow in building, deploying, and debugging it:

  1. Sign the assembly to be generated with a strong name.

  2. Build the assembly.

  3. Deploy the assembly by moving or copying it to the appropriate Integration Services folder.

  4. Install the assembly in the global assembly cache (GAC).

  5. Add the object to the Toolbox, when the object is a task or data flow component.

  6. Troubleshoot the deployment, if necessary.

  7. Test and debug your code.

Signing the Assembly

When an assembly is meant to be shared, it must be installed in the global assembly cache. After the assembly has been added to the global assembly cache, the assembly can be used by applications such as Business Intelligence Development Studio. A requirement of the global assembly cache is that the assembly must be signed with a strong name, which guarantees that an assembly is globally unique. A strong-named assembly has a fully qualified name that includes the name, culture, public key, and version number of the assembly. The runtime uses this information to locate the assembly and to differentiate it from other assemblies with the same name.

To sign an assembly with a strong name, you must first have or create a public/private key pair. This public and private cryptographic key pair is used at build time to create a strong-named assembly.

For more information about strong names and on the steps that you must followto sign an assembly, see the following topics in the .NET Framework SDK documentation:

  • Strong-Named Assemblies

  • Creating a Key Pair

  • Signing an Assembly with a Strong Name

You can easily sign your assembly with a strong name in Visual Studio at build time. In the Project Properties dialog box, select the Signing tab. Select the option to Sign the assembly and then provide the path of the key (.snk) file.

Building the Assembly

After signing the project, you must build or rebuild the project or the solution by using the commands available on the Build menu of BI Development Studio. Your solution may contain a separate project for a custom user interface, which must also be signed with a strong name, and can be built at the same time.

The most convenient method for performing the next two steps—deploying the assembly and installing it in the global assembly cache—is to script these steps as a post-build event in Visual Studio. Build events are available from the Compile page of Project Properties for a Visual Basic project, and from the Build Events page for a C# project. The full path is required for command prompt utilities such as gacutil.exe. Quotation marks are required both around paths that contain spaces and around macros such as $(TargetPath) that expand to paths that contain spaces.

Here is an example of a post-build event command line for a custom log provider:

"C:\Program Files\Microsoft Visual Studio 9.0\SDK\v3.5\Bin\gacutil.exe" -u $(TargetName)
"C:\Program Files\Microsoft Visual Studio 9.0\SDK\v3.5\Bin\gacutil.exe" -i $(TargetFileName)
copy $(TargetFileName) "C:\Program Files\Microsoft SQL Server\100\DTS\LogProviders "

Deploying the Assembly

The SSIS Designer locates the custom objects available for use in packages by enumerating the files found in a series of folders that are created when SQL Server Integration Services is installed. When the default SQL Server installation settings are used, this set of folders is located under C:\Program Files\Microsoft SQL Server\100\DTS. However if you create a setup program for your custom object, you should check the value of the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\Setup\DtsPath registry key to verify the location of this folder.

You can put the assembly in the folder in two ways:

  • Move or copy the compiled assembly to the appropriate folder after building it. (For convenience, you can include the copy command in a Post-build Event.)

  • Build the assembly directly in the appropriate folder.

The following deployment folders under C:\Program Files\Microsoft SQL Server\100\DTS are used for the various types of custom objects:

Custom object

Deployment folder

Task

Tasks

Connection manager

Connections

Log provider

LogProviders

Data flow component

PipelineComponents

Note

Assemblies are copied to these folders to support the enumeration of available tasks, connection managers, and so on. Therefore you do not have to deploy assemblies that contain only the custom user interface for custom objects to these folders.

Installing the Assembly in the Global Assembly Cache

To install the task assembly into the global assembly cache (GAC), use the command line tool gacutil.exe, or drag the assemblies to the %system%\assembly directory. For convenience, you can also include the call to gacutil.exe in a Post-build Event.

The following command installs a component named MyTask.dll into the GAC by using gacutil.exe.

gacutil /iF MyTask.dll

You must close and reopen SSIS Designer after you install a new version of your custom object. If you have installed earlier versions of your custom object in the global assembly cache, you must remove them before installing the new version. To uninstall an assembly, run gacutil.exe and specify the assembly name with the /u option.

For more information about the global assembly cache, see Global Assembly Cache Tool (Gactutil.exe) in the .NET Framework Tools.

Adding a Task or Data Flow Component to the Toolbox

After you have deployed the custom object and installed it in the global assembly cache, it can be used in Business Intelligence Development Studio like any object that is included with SQL Server Integration Services. However, it must be added to the Toolbox explicitly.

To add a custom component to the Toolbox

  1. Right-click the toolbox and then click Choose Items.

  2. In the Choose Toolbox Items dialog box, click the SSIS Control Flow Items tab for a task, or the SSIS Data Flow Items tab for a data flow component.

  3. Click the check box next to your component, and then click OK.

Note

If the component is not displayed in the list, you can click Browse to locate the component yourself. But in this case it may not be installed correctly.

Troubleshooting the Deployment

If your custom object appears in the Toolbox or the list of available objects, but you are not able to add it to a package, try the following:

  1. Look in the global assembly cache for multiple versions of your component. If there are multiple versions of the component in the global assembly cache, the designer may not be able to load your component. Delete all instances of the assembly from the global assembly cache, and re-add the assembly.

  2. Make sure that only a single instance of the assembly exists in the deployment folder.

  3. Reset the Toolbox.

  4. Attach Visual Studio to devenv.exe and set a breakpoint to step through your initialization code to ensure that no exceptions occur.

Testing and Debugging Your Code

The simplest approach to debugging the run-time methods of a custom object is to start dtexec.exe from Visual Studio after building your custom object and run a package that uses the component.

If you want to debug the component's design-time methods, such as the Validate method, open a package that uses the component in a second instance of Visual Studio, and attach to its devenv.exe process.

If you also want to debug the component's run-time methods when a package is open and running in SSIS designer, you must force a pause in the execution of the package so that you can also attach to the DtsDebugHost.exe process.

To debug an object's run-time methods by attaching to dtexec.exe

  1. Sign and build your project in the Debug configuration, deploy it, and install it in the global assembly cache as described in this topic.

  2. On the Debug tab of Project Properties, select Start external program as the Start Action, and locate dtexec.exe, which is installed by default in C:\Program Files\Microsoft SQL Server\100\DTS\Binn.

  3. In the Command line options text box, under Start Options, enter the command line arguments required to run a package that uses your component. Often the command-line argument will consist of the /F[ILE] switch followed by the path and file name of the .dtsx file. For more information, see dtexec Utility.

  4. Set breakpoints in the source code where appropriate in the run-time methods of your component.

  5. Run your project.

To debug a custom object's design-time methods by attaching to Business Intelligence Development Studio

  1. Sign and build your project in the Debug configuration, deploy it, and install it in the global assembly cache as described in this topic.

  2. Set breakpoints in the source code where appropriate in the design-time methods of your custom object.

  3. Open a second instance of Visual Studio and load an Integration Services project that contains a package that uses the custom object.

  4. From the first instance of Visual Studio, attach to the second instance of devenv.exe in which the package is loaded by selecting Attach to Process from the Debug menu of the first instance.

  5. Run the package from the second instance of Visual Studio.

To debug a custom object's run-time methods by attaching to Business Intelligence Development Studio

  1. After you have completed the steps listed in the previous procedure, force a pause in the execution of your package so that you can attach to DtsDebugHost.exe. You can force this pause by adding a breakpoint to the OnPreExecute event, or by adding a Script task to your project and entering script that displays a modal message box.

  2. Run the package. When the pause occurs, switch to the instance of Visual Studio in which your code project is open, and select Attach to Process from the Debug menu. Make sure to attach to the instance of DtsDebugHost.exe listed as Managed, x86 in the Type column, not to the instance listed as x86 only.

  3. Return to the paused package and continue past the breakpoint, or click OK to dismiss the message box raised by the Script task, and continue package execution and debugging.

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.