Archive for Uncategorized

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.

New Release of Task Factory

Sorry for the lack of updates. In the middle of developing some of the most advanced components we’ve ever tackled with Task Factory, I just released the latest version of Task Factory. This release includes a BUNCH of updates to the current components and includes three new components.

The three new components are Download File Task, Advanced Execute Package Task and Advanced Execute Process Task.

Download File Task is used to download files using HTTP rather than FTP.

Advanced Execute Package Task allows you to execute a child package while passing and retrieving variables to and from the child package. This makes it WAY easier than setting up a Parent Configuration to pass variables to the child package.

Advanced Execute Process Task allows you to execute multiple processes per instance of the task. It acts as if you’re executing the processes from the command line and allows for variable replacement as part of the command being executed.

While this release was important to fix some bugs in TF and release a few new tasks, the upcoming release (in about 6 weeks) is going to blow you away. The sheer power of the tasks we’re adding now is incredible.