Steps to Build and Deploy Custom SSIS Components

 This post explains the steps required to build and deploy custom SSIS components. For the purpose of illustration, the Custom SSIS Data Flow Component – Credit Card Number Validator (CCNV) is used.

 CodePlex

Step 1 – Create Strong Name Key (SNK) for the Assembly

 From Visual Studio, open the Class Library of your Custom SSIS Component. Navigate to Project→Properties from toolbar

 

Figure 1 - Project Properties
Figure 1 – Project Properties

 

Navigate to the Signing tab and Select the options as shown in Figure 2.

Figure 2 - Sign Assembly
Figure 2 – Sign Assembly

Enter a Strong Name Key (SNK) file as shown in Figure 3. Protect it with a password (optional).

Figure 3- Strong Name Key (SNK)
Figure 3- Strong Name Key (SNK)

Step 2 – Build the Assembly

 

Step 3 – Deploy the Assembly to BIDS SSIS Folder

The SSIS Designer in BIDS locates the custom components available for use in packages by enumerating the files found in a series of folders located under C:\Program Files\Microsoft SQL Server\100\DTS\ (for SQL Server 2008) or C:\Program Files\Microsoft SQL Server\90\DTS\ (for SQL Server 2005). If your default SQL Server Installation uses a different path, then use the registry key HKLM\SOFTWARE\Microsoft\MSDTS\Setup\DtsPath to locate this folder.

Copy the Assembly from \bin folder to the appropriate sub-folder.  The assembly for Data Flow Components like Credit Card Number Validator (CCNV05) is copied to the sub-folder PipelineComponents. Overwrite the Assembly if it already exists.

❗IMPORTANT:  On 64-bit OS use the 32-bit Program Files folder e.g.  C:\Program Files(x86)\Microsoft SQL Server\100\DTS\, since the current version of BIDS is still a 32-bit application.

Step 4 – Install the Assembly to Global Assembly Cache (GAC)

Navigate to %system%\assembly folder . It’s usually C:\Windows\Assembly. Drag and Drop the Assembly from \bin folder to %system%\assembly folder. If the assembly already exists, right-click and uninstall existing assembly and then install the new version.

IMPORTANT:  You must close and reopen BIDS SSIS Designer after you install a new version of your custom object.

Step 5 – Adding the Custom Component to the Toolbox

Now comes the best part where you can see your custom component in action. From BIDS, open any SSIS project.

Right-click the toolbox and then click Choose Items (Figure 4).

Figure 4- Choose Items
Figure 4- Choose Items

 In the Choose Toolbox Items dialog box, click the Data Flow Items and Select your custom component (Figure 5), in this case Credit Card Number Validator 

 

Figure 6- Toolbox Showing Credit Card Number Validator
Figure 6- Toolbox Showing Credit Card Number Validator

Now your toolbar displays your Custom SSIS Data Flow Component (Figure 6).

 Your Custom SSIS Component is now ready for Action.

 

Benny Austin

12 thoughts on “Steps to Build and Deploy Custom SSIS Components”

  2. Once I have added the component to a package, making changes to the component code and rebuilding the solution does not appear to refresh/update the component in the SSIS package. Is there something special that I need to do to get changes to “take”? As an example, I changed the text in ComponentMetaData.Description, rebuilt the solution, and when I open the SSIS pakcage, it still shows the old description. Thank you!

    Reply

    1. Chris, few things to check
      a) After making the code change did you deploy the component to GAC ? You should un-install the existing component and re-deploy the new version to GAC.
      b) if you are an SSIS developer you would also need to copy the component to the BIDS folder (step 3 on the post) so that BIDS would reflect the latest change. This step is optional if you are doing a deployment on server.
      c) if after checking a) and b) and still it does not work, remove your component from BIDS toolbar. Save your work and restart BIDS and add the component again to the toolbar.

      Reply

  5. When I attempt to build the application, I receive the following error,

    Error 1 The type or namespace name ‘IDTSOutputColumn90’ could not be found (are you missing a using directive or an assembly reference?) C:\Workspace\ConvertMigrateFromDCTtoSSIS\CCNV05-0908Aug-R1\CCNV05-0908Aug-R1\CreditCardValidator\CreditCardValidator\CCNValidator.cs 167 24 CreditCardNumberValidator

    I have added the reference for microsoft.sqlserver.dtspipelinewrap.dll.

    Do you have any suggestions?

    TIA.

    Reply

    1. Roger, pls add reference to Microsoft.SQLServer.DTSRuntimeWrap.dll and Microsoft.SqlServer.PipelineHost.dll in addition to Microsoft.SqlServer.DTSPipelineWrap.dll. Let me know if that works. Thanks

      Reply
  7. Pingback: Debugging Custom SSIS Components « Benny Austin
  8. Pingback: Custom SSIS Data Flow Component – Credit Card Number Validator « Benny Austin

Leave a comment