Upgrading Custom Objects for SQL Server 2008 Integration Services
In SQL Server 2008, there are some new Integration Services features that cause breaking changes that affect custom objects that were built in SQL Server 2005 Integration Services. If you want to use these custom objects in SQL Server 2008 Integration Services packages, you will have to upgrade the objects.
This topic explains how to decide what the best upgrade option is for your custom objects and how to upgrade and deploy those objects from SQL Server 2005 Integration Services to SQL Server 2008 Integration Services. It also explains how to ensure that your updated custom objects can be located during the automated upgrade of packages.
Deciding How to Upgrade Your Custom Objects
You have the option of simply upgrading your custom objects or of upgrading and reversioning your objects:
Upgrading only. In this option, you change the custom objects so that they are compatible with SQL Server 2008 Integration Services.
Upgrading and reversioning. In this option, you first upgrade the objects. Then, you assign a new assembly version to the upgraded objects and create a mapping file that identifies the locations for these new objects.
Whichever upgrade option you select, you must upgrade your custom objects before you or users of your objects try to upgrade packages that contain those objects. When the package upgrade engine tries to upgrade packages, the engine tries to instantiate the custom objects in those packages. If the computer does not have upgraded versions of those custom objects, the package upgrade engine will not be able to instantiate the objects. Because the objects cannot be instantiated, the package upgrade engine will not be able to upgrade the packages.
Note
The SQL Server 2008 version of BI Development Studio automatically starts the package upgrade engine when you open an individual SQL Server 2005 package. The Package Upgrade Wizard also uses the same package upgrade engine when you upgrade a batch of packages.
To help you decide whether to upgrade only or to upgrade and reversion, the following table identifes the various factors to consider.
Option |
Effect on package upgrade |
Effect on side-by-side existence |
|---|---|---|
Upgrade custom objects only |
You do not have to take any additional steps. The package upgrade engine can successfully upgrade packages that contain your custom objects. |
You can only use the SQL Server 2008 versions or the SQL Server 2005 versions of your custom objects on a particular computer. You cannot use the SQL Server 2005 versions of your custom objects on the same computer as the SQL Server 2008 versions. |
Upgrade and reversion custom objects |
You have to create a mapping file to distribute with your upgraded objects. Otherwise, the package upgrade engine cannot successfully upgrade packages that contain your custom objects. |
You can use the SQL Server 2005 versions of your custom objects in SQL Server 2005 packages, and use the SQL Server 2008 versions of your custom objects in SQL Server 2008 packages, on the same computer. |
Upgrading Your Custom Objects
The following procedure outlines how to upgrade your custom objects so that they are compatible with SQL Server 2008 Integration Services.
To update a custom object
Remove references to Integration Services assemblies with version 9.0 and replace them with references to version 10.0 of the same assemblies.
Rename IDTSxxx90 objects in code to the equivalent IDTSxxx100 objects.
Look for warnings about obsolete or deprecated code members and replace them with the equivalent new members.
After you have upgraded your custom objects, you have the choice of whether you want to also reversion these object. You do not have to reversion your upgraded custom objects. If you decide not to reversion your custom objects, you will then need to deploy your upgraded custom objects as described later in this topic.
However, if you want to use different versions of your custom objects side-by-side in both SQL Server 2005 Integration Services and SQL Server 2008 Integration Services packages, you will need to reversion the upgraded objects as described in the next section.
Reversioning Your Custom Objects
How you reversion your custom objects depends on whether you wrote those objects in managed or native code:
Managed code. For custom objects written in managed code, reversioning means incrementing the assembly version number.
Native code. For custom objects written in native code, reversioning means changing the ProgID and CLSID.
After you reversion your custom objects, you have to provide upgrade mapping files for those objects. During package upgrade, these mapping files tell the package upgrade engine how to replace the previous versions of your custom objects with the new versions.
Mapping the Versions of Your Custom Object for Upgrade
SQL Server 2008 Integration Services adds a new subfolder, UpgradeMappings, in the %ProgramFiles%\Microsoft SQL Server\100\DTS folder. This subfolder is where you deploy the mapping files that tell the package upgrade engine how to map SQL Server 2005 Integration Services objects to their new SQL Server 2008 equivalents.
After you have deployed your mapping files to the UpgradeMappings subfolder, Integration Services will be able to upgrade packages that contain your custom objects. The mapping files are only necessary during package upgrade. After all packages have been upgraded, you can remove the mapping files. For more information, see Upgrading Integration Services Packages.
All mapping files must be in XML format, as shown in the following sample mapping file.
Note
Your mapping files must have the ".xml" file extension, or these files will not be discovered by the package upgrade engine.
Sample Mapping File
<?xml version="1.0" encoding="utf-8"?>
<Mappings xmlns="https://www.microsoft.com/SqlServer/Dts/UpgradeMapping.xsd">
<!-- Connection Managers -->
<ConnectionManagerMapping tag="MSOLAP - Analysis Services connections"
oldValue="MSOLAP90"
newValue="MSOLAP100" />
<!-- Extensions -->
<ExtensionMapping tag="my custom object"
oldAssemblyStrongName="MyCustomAssembly.MyCustomTask, MyCustomAssembly, Version=1.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
newAssemblyStrongName="MyCustomAssembly.MyCustomTask, MyCustomAssembly, Version=2.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
<!-- Providers -->
<ProviderMapping tag="SQL Server Native Client - Version dependent"
oldValue="sqlncli.1"
newValue="sqlncli10.1" />
<ProviderMapping tag="SQL Server Native Client - Version independent"
oldValue="sqlncli"
newValue="sqlncli10" />
<ProviderMapping tag="MSOLAP - Analysis Services connections"
oldValue="msolap.3"
newValue="msolap.4" />
</Mappings>
Creating Your Own <ExtensionMapping> Element
As shown in the previous sample mapping file, the <ExtensionMapping> element is the element that maps the SQL Server 2005 Integration Services objects to their new equivalents for SQL Server 2008 Integration Services. After you create a new <ExtensionMapping> element, you have to supply values for the attributes in the following table.
Attribute |
Description |
|---|---|
tag |
Text that describes your custom object. (This description is used for logging.) |
oldAssemblyStrongName |
The strong name of your custom object's assembly in SQL Server 2005 Integration Services. |
newAssemblyStrongName |
The strong name of your custom object's assembly in SQL Server 2008 Integration Services. |
You have the following options when mapping assemblies:
You can use the fully qualified class name: <assembly name>.<class name>, <assembly name>, Version=<version>, Culture=<culture>, PublicKeyToken=<key>. This format is shown in the sample mapping file earlier in this topic.
You can provide the strong name of the assembly itself, without the class name, as shown in the following example:
<ExtensionMapping tag="my custom object" oldAssemblyStrongName="MyCustomAssembly, Version=1.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" newAssemblyStrongName="MyCustomAssembly, Version=2.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />If you provide the strong name of the assembly itsefl, all classes from the old assembly will be mapped to the new assembly.
Deploying Your Upgraded Custom Objects
The steps for deploying your updated custom object are similar to those for SQL Server 2005. The only difference is that you now copy your custom assembly to a subfolder under the %ProgramFiles%\Microsoft SQL Server\100\DTS folder, instead of to the %ProgramFiles%\Microsoft SQL Server\90\DTS folder.
If you reversion your custom objects, remember to deploy their mapping files to the UpgradeMappings folder to support the automated upgrade of packages that use these reversioned objects.
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services