Tuesday 21 April 2015

SSIS Custom Components and a Tale of Versions


I have been investigating SSIS custom components. These will allow non programmers in my team to use SSIS components which will perfectly fit our business data needs. e.g. custom data conversion. 
I am assuming that you know how to make an SSIS custom component?

  1. Create a class library: 
    1. Reference some dlls from the SQL Server Client Tools SDK.
    2. Inherit from PipelineComponent.
    3. Override some methods.
    4. See this for a starting point: https://msdn.microsoft.com/en-us/library/ms135993.aspx
  2. Take your newly built custom component:
    1. Copy it into a SQL Server installation folder.
    2. Registered into the GAC.
  3. Open your SSIS package IDE and register the component/refresh your toolbox.
  4. Your SSIS toolbox will then hopefully show this component.
  5. Bish bash bosh!
Hmmm....


I had a few issues getting this to work though because I had so many environment version 'variables' to deal with. For example:
  • Architecture - 32bit or 64bit? (of SQL Server/.Net/gacutil)
  • .Net framework - 2.0, 3.5, 4.0, 4.5 or 4.51?
  • Gacutil version?
  • SQL Server - 2008 or 2014?
  • SSIS Version - Business Intelligence Development Studio (BIDs) or Data tools for Visual Studio 2013?
What has made my situation more complicated is that I am running two versions of SQL Server: 2008 (for production) and 2014 (for development - getting ready for production to be upgraded). 
I am also running 64 bit version of SQL Server/Windows 7 and so my installation folders are across Program Files and Program Files (x86). On top of this, when I installed 2014 I didn't include the Client Tools SDK feature - so for a while I was missing the latest dlls - I wasn't even sure there were later dlls as my installation looked like it had everything...

On that note - you need to know about your SQL Server installation folders. Within the Microsoft SQL Server installation folder you will find lots of numbered folders. The folders relate to the versions of SQL Server installed. For example:
  • 80 = SQL Server 2000
  • 90 = SQL Server 2005
  • 100 = SQL Server 2008
  • 105 = SQL Server 2008 R2
  • 110 = SQL Server 2012
  • 120 = SQL Server 2014
These numbered folders can exist in both Program Files and Program Files (x86) installation folders. However, just because you have the folder, it doesn't mean you that have any or all of the features installed for that version. Folders are created regardless for backward compatibility reasons - e.g. access to older versions of DLL's & tools.

Ok, Now what?

Below are the dlls that you need to reference from your custom component dll. They come from the SQL Server Client Tools SDK, They are not listed under the .net framework assemblies (In Visual Studio 'Add Reference' dialog). You will need to manually browse to this directory and add them. 
  • Microsoft.SqlServer.DTSPipelineWrap.dll
  • Microsoft.SQLServer.DTSRuntimeWrap.dll
  • Microsoft.SQLServer.ManagedDTS.dll
  • Microsoft.SqlServer.PipelineHost.dll
With all this said, we have two routes for creating a custom component:

SQL Server 2005/2008 & BIDs 

  • Reference dlls from:
    • C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\
  • Copy your custom component dll to: 
    • C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents\
  • Your custom component should target .Net 2.0, 3.0 or 3.5.
  • Use SQL Server Business Intelligence Development Studio to make your SSIS packages.
  • Ensure that the SQL Server 2005/2008 Client Tools SDK feature is fully installed.
  • Use gacutil from .Net 2.0, 3.0 or 3.5 or higher to register your component dll.
  • Add component to BIDs toolbox by right clicking the tool box and select 'Choose Items...'. Find your component under the 'SSIS Data Flow Items' or 'SSIS Control Flow Items' tab (depending on what type of component you have created). Check the component checkbox. Click OK.

SQL Server 2012/2014 & Data Tools

  • Reference dlls from:
    • C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\
  • Copy your custom component dll to: 
    • C:\Program Files (x86)\Microsoft SQL Server\120\DTS\PipelineComponents\
  • Your custom component should target .Net 4.0 or higher.
  • Use SQL Server Data Tools for Visual Studio to make your SSIS packages.
  • Ensure that the SQL Server 2012/2014 Client Tools SDK feature is fully installed.
  • Use gacutil from .Net 4.0 or higher to register your component dll.
  • Add component to Data Tools toolbox simply by right clicking the tool box and select 'Refresh Toolbox'.


Hope this helps you through a bad day!


No comments:

Post a Comment