Upgrading SSIS Custom Components for SQL Server 2012
Having finally got around to upgrading my custom components to SQL Server 2012, I thought I’d share some notes on the process. One of the goals was minimal duplication, so the same code files are used to build the 2008 and 2012 components, I just have a separate project file. The high level steps are listed below, followed by some more details. Create a 2012 copy of the project file Upgrade project, just open the new project file is VS2010 Change target framework to .NET 4.0 Set conditional compilation symbol for DENALI Change any conditional code, including assembly version and UI type name Edit project file to change referenced assemblies for 2012 Change target framework to .NET 4.0 Open the project properties. On the Applications page, change the Target framework to .NET Framework 4. Set conditional compilation symbol for DENALI Re-open the project properties. On the Build tab, first change the Configuration to All Configurations, then set a Conditional compilation symbol of DENALI. Change any conditional code, including assembly version and UI type name The value doesn’t have to be DENALI, it can actually be anything you like, that is just what I use. It is how I control sections of code that vary between versions. There were several API changes between 2005 and 2008, as well as interface name changes. Whilst we don’t have the same issues between 2008 and 2012, I still have some sections of code that do change such as the assembly attributes. #if DENALI
[assembly: AssemblyDescription("Data Generator Source for SQL Server Integration Services 2012")]
[assembly: AssemblyCopyright("Copyright © 2012 Konesans Ltd")]
[assembly: AssemblyVersion("3.0.0.0")]
#else
[assembly: AssemblyDescription("Data Generator Source for SQL Server Integration Services 2008")]
[assembly: AssemblyCopyright("Copyright © 2008 Konesans Ltd")]
[assembly: AssemblyVersion("2.0.0.0")]
#endif
The Visual Studio editor automatically formats the code based on the current compilation symbols, hence in this case the 2008 code is grey to indicate it is disabled.
As you can see in the previous example I have distinct assembly version attributes, ensuring I can run both 2008 and 2012 versions of my component side by side.
For custom components with a user interface, be sure to update the UITypeName property of the DtsTask or DtsPipelineComponent attributes. As above I use the conditional compilation symbol to control the code.
#if DENALI
[DtsTask
(
DisplayName = "File Watcher Task",
Description = "File Watcher Task",
IconResource = "Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTask.ico",
UITypeName = "Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTaskUI,Konesans.Dts.Tasks.FileWatcherTask,Version=3.0.0.0,Culture=Neutral,PublicKeyToken=b2ab4a111192992b",
TaskContact = "File Watcher Task; Konesans Ltd; Copyright © 2012 Konesans Ltd; http://www.konesans.com"
)]
#else
[DtsTask
(
DisplayName = "File Watcher Task",
Description = "File Watcher Task",
IconResource = "Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTask.ico",
UITypeName = "Konesans.Dts.Tasks.FileWatcherTask.FileWatcherTaskUI,Konesans.Dts.Tasks.FileWatcherTask,Version=2.0.0.0,Culture=Neutral,PublicKeyToken=b2ab4a111192992b",
TaskContact = "File Watcher Task; Konesans Ltd; Copyright © 2004-2008 Konesans Ltd; http://www.konesans.com"
)]
#endif
public sealed class FileWatcherTask: Task, IDTSComponentPersist, IDTSBreakpointSite, IDTSSuspend
{
// .. code goes on...
}
Shown below is another example I found that needed changing. I borrow one of the MS editors, and use it against a custom property, but need to ensure I reference the correct version of the MS controls assembly. This section of code is actually shared between the 2005, 2008 and 2012 versions of my component hence it has test for both DENALI and KATMAI symbols.
#if DENALI
const string multiLineUI = "Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=11.0.00.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
#elif KATMAI
const string multiLineUI = "Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
#else
const string multiLineUI = "Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
#endif
// Create Match Expression parameter
IDTSCustomPropertyCollection100 propertyCollection = outputColumn.CustomPropertyCollection;
IDTSCustomProperty100 property = propertyCollection.New();
property = propertyCollection.New();
property.Name = MatchParams.Name;
property.Description = MatchParams.Description;
property.TypeConverter = typeof(MultilineStringConverter).AssemblyQualifiedName;
property.UITypeEditor = multiLineUI;
property.Value = MatchParams.DefaultValue;
Edit project file to change referenced assemblies for 2012
We now need to edit the project file itself. Open the MyComponente2012.cproj in you favourite text editor, and then perform a couple of find and replaces as listed below:
Find
Replace
Comment
Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Change the assembly references version from SQL Server 2008 to SQL Server 2012.
Microsoft SQL Server\100\
Microsoft SQL Server\110\
Change any assembly reference hint path locations from from SQL Server 2008 to SQL Server 2012.
If you use any Build Events during development, such as copying the component assembly to the DTS folder, or calling GACUTIL to install it into the GAC, you can also change these now. An example of my new post-build event for a pipeline component is shown below, which uses the .NET 4.0 path for GACUTIL. It also uses the 110 folder location, instead of 100 for SQL Server 2008, but that was covered the the previous find and replace.
"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe" /if "$(TargetPath)"
copy "$(TargetPath)" "%ProgramFiles%\Microsoft SQL Server\110\DTS\PipelineComponents" /Y
