CODEWITHSUNDEEP
ssissql-server-2014script-component
Sweetspot
Sweetspot

Reputation: 91

Deploy SSIS Script to Toolbox

I am trying to deploy a Script component to SSIS toolbox in the Data Flow for reuse. I'm using visual studio 2010, and SQL server 2014.

I have created the script, Signed the assembly, used Gacutil.exe /i on the DLL file to add it. It now recedes in the GAC_MSIL folder under it's strong name folder.

The above procedure is the current way I'm trying to get it to work, but I have additionally tried to use SN utility to generate a strong name and add this key back into the build. Didn't work any better than the above procedure. Is it supposed to be next to impossible to add custom components to SSIS?

In my simple mind it should be just sign, build, GAC and done.

Upvotes: 1

Views: 232

Answers (1)

billinkc
billinkc

Reputation: 61239

The editor for SSIS packages is tightly bound to the version of the SQL Server you're working with.

As a general reference, we have the following

  • VS 2005 = SQL Server 2005 {90}
  • VS 2008 = SQL Server 2008 & SQL Server 2008 R2 {100}
  • VS 2010, VS 2012 = SQL Server 2012 {110}
  • VS 2013 = SQL Server 2014 {120}
  • VS 2015 = SQL Server 2016 (once it's RTMed) {130}

Now, you could be using VS 2010 to build your custom DLL, as long as you've referenced the correct version of the SSIS DLLs, and then used SSDT-BI edition for VS 2013 to author your SSIS packages --- that's fine.

Making it work

Your current build and deploy cycle is what you need to do on your servers. That will allow SSIS to find the components when it runs.

However, you need the development experience so you need to also add that same DLL into the correct folder in your SQL Server installation. For SQL Server 2014, a custom "Script Component", or anything that lives in the data flow, would also need to be copied to 

C:\Program Files\Microsoft SQL Server\120\DTS\PipelineComponents

If you've done it properly, then items automatically show up for VS 2010+. VS 2005/2008 required an additional step of explicitly adding tasks and components into the Toolbox.

If they aren't showing then you've either

  • built your DLL against the wrong version of the SSIS framework
  • using the wrong version of SSIS tools
  • not deployed the DLL to both the GAC and the correct DTS subfolder based upon your SQL Server version.

Clear as mud?

Upvotes: 2

Related Questions