Managing the editable properties of your custom objects in SQL Server Integration Services (SSIS) is a great way to improve their usability, but how can you? By properties, I mean the common fields exposed on the Properties tool window in Visual Studio, any time you’re working with a visual designer, and which you’ve probably used any time you wanted to make a precise change, or a quick change.
In SSIS, you can create your own class to handle all of the following types:
Data Flow / Pipeline Components
Control Flow Tasks
For Each Enumerators (for the for each loop task)
For the most part, these custom classes can be directly modified when editing SSIS packages in Visual Studio, and so anything properties you expose on the class can be edited right from Visual Studio without opening up any new UIs. For simple objects especially, these can…
You’re using the new One Designer cross-versioning in SQL Server Integration Services, and everything breaks when you try to downgrade to SQL Server 2012. The little icon that indicates that everything has gone wrong shows up,
or when you try to interact with any custom components or tasks you get the following error, or something similar:
Now, there are three things worth checking:
Are your UpgradeMapping files set up correctly? They should point to a valid strong-named assembly, and use the same alias, for both versions of SQL Server that you’re attempting to deploy to. If not, fix this issue first and try again.
After migrating your custom objects, navigate to the UserComponentTypeName property (for PipelineComponents) or to the CreationName field of the corresponding DTS:Executable in the package XML.
These should contain either the alias (typically the qualified name of the class, i.e. Sample.SSIS.CustomTask),
or the strong-name associated with…
When I first wrote custom objects for SQL Server Integration Services (SSIS), distributing my DLL for use and for installation on servers seemed troublesome. Of course, I had to set up an installer. But after the deprecation of the normal setup/installer project included with Visual Studio, which tool could be used that would be easily shared and maintained, and still meet all of the needs for distributing the custom SSIS objects?
Ultimately, I chose the WiX Toolset, as it is already integrated with the Visual Studio gallery and makes a simple job of product versioning, Global Assembly Cache (GAC) / assembly installation, and install path selection. In this tutorial, I will walk through the construction of a skeleton custom SSIS Task and a corresponding WiX installer, that can locate the correct version of SQL Server, install to both the DTS folders and…