Archive for April 2012

Upgrading SSIS Custom Components to 2012

Recently we had to upgrade all of our components in Task Factory to utilize the new 2012 SSIS assemblies for integration into SQL 2012. I learned a couple of things along the way that you should know about if you’re doing the same.

Even though Parameters are available using the Variables class (i.e. $Project::MyFile), they cannot be written to from the Variables class. Meaning Variables(“$Project::MyFile”).Value = “C:\ssis\test.xml” will fail.

Parameters cannot be written to within a Task but can be within a PipelineComponent. The reason for this is PipelineComponent has a reference to the Parameters class whereas the Task class does not. You can only access Parameters from the Variables class in a Task.

All Parameters are available for both the Project and Package level parameters within the Variables collection. Meaning if you list all of your variables, they will be part of the Variables collection. The namespace for the parameters available in the Variables collection is either $Project or $Package

There isn’t a need to “Choose Items” for new Control Flow and Data Flow tasks. If you put them in the correct location of %programfiles%\Microsoft SQL Server\110\Dts\Tasks and %programfiles%\Microsoft SQL Server\110\Dts\PipelineComponents, they will automatically show up in the SSIS Toolbox.

You will not have to change very much of your code to get your components to work in SQL 2012. All of the interfaces are the same for both Tasks and PipelineComponents. You WILL have to add references to the SQL 2012 assemblies like Microsoft.SqlServer.DTSPipelineWrap and Microsoft.SqlServer.DTSRuntimeWrap.

You will need to use .NET 4.0 for SQL 2012 Tasks and PipelineComponents as well as use GACUTIL from .NET 4.0 to add the assemblies to the GAC. GACUTIL for .NET 4.0 is usually located in %programfiles%\Microsoft SDKs\Windows\v7.0a\.NETFX 4.0 Tools\.

That’s it for now. I will update this as we continue to build Task Factory for SQL 2012.