Deploying Custom SSIS Connection Manager

When deploying a custom SSIS Connection Manager component you need to ensure the DLL assembly is deployed into the GAC (C:\Windows\Assembly) as well as the \DTS\Connections folder.  This is true even when deploying to a non-developer PC, such as a test server or production server.

The documentation found in the SQL Server Books Online seems to indicate you need to copy the DLL to the \DTS\Connections folder so that the BIDS designer can locate the custom components to use when authoring a package.  This is true, but a little misleading.  In my experience the assembly needs to be copied to the \DTS\Connections folder even on servers where the packages will just be executing.  The documentation does provide some help on how to find the specific path the assembly needs to be copied to, including the registry key which contains the full path to the \DTS folder.

Note, I did not find this to be the case for custom SSIS Data Flow components.  Placing the DLL in the \DTS\PipelineComponents folder was only necessary on the developer PC when designing the package.  Just copying it to the GAC was sufficient for servers which execute the packages using the custom data flow components.

Build SSIS Custom Components on TeamBuild

For our SSIS custom components (custom connection managers, custom data sources, custom data transformations, etc.) we have them deployed into the GAC and copied into the appropriate \DTS folder so that they become available in the design tools to be dropped on a design canvas.  This is accomplished via the Build Events in Visual Studio:

We also use TFS and TeamBuild for daily builds.  We run unit tests on our database stored procs and functions, and on our custom business assemblies.  No unit testing on SSIS components yet, but I am interested in what’s happening in this space.  The problem we ran into with TeamBuilds on the SSIS custom components is that we didn’t want/need the build events to occur.  In fact our TeamBuilds failed because the paths couldn’t be resolved.

To resolve this I opened up the project files and modified the PreBuildEvent and PostBuildEvent tags to include a condition checking if the build was occurring within Visual Studio or not.  The tags look like this now:

<PropertyGroup>
  <PreBuildEvent Condition="'$(BuildingInsideVisualStudio)'=='true'">"$(DevEnvDir)..\..\SDK\v2.0\Bin\gacutil.exe" /f /u "$(TargetPath)"
del "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\$(TargetFileName)"</PreBuildEvent>
  <PostBuildEvent Condition="'$(BuildingInsideVisualStudio)'=='true'">"$(DevEnvDir)..\..\SDK\v2.0\Bin\gacutil.exe" /if "$(TargetPath)"
xcopy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents"</PostBuildEvent>
</PropertyGroup>