Using PerformUpgrade

One of the features in SSIS Data Flow custom component development is the PerformUpgrade method. Terribly documented, and not well explained anywhere else - even in books about SSIS Programming (such as Kirk Haselden's Microsoft SQL Server 2005 Integration Services - which is a great book for starting out). It took me a while to figure out - the best help being Alberto Ferrari's TableDifference component.

How does it work?

It ends up being rather simple - and perhaps that's why it wasn't considered for better documentation. (Poor excuse.) The short explanation is that:

1. Your custom component binary (the DLL) is marked with a version number.


2. The information that describes how your component is configured in the users' Data Flow design surface (metadata) is tagged with a version number inside their dtsx file.


3. When BIDS loads the dtsx, it compares the metadata version number with the binary version. If they're different, it calls the PerformUpgrade method.

Why use it?

The PerformUpgrade method allows you to seamlessly upgrade "version 1" of your component to "version 2" without any nasty null reference exceptions when you attempt to load a metadata property that isn't there.

What that means to the user is that they don't have to delete your component from the design surface, add it again, then spend time configuring it.

How can I use it?

Using PerformUpgrade properly requires a few things. First, it requires that you identify your component's version. And NO - I do not mean the assembly version, file version, or any other version number you're used to in standard .Net development. It needs you to fill in the CurrentVersion property of the DtsPipelineComponent attribute, like this:

In this case, I've set the CurrentVersion to 2, in order to meaningfully demonstrate "upgrading" a component from version 1 to 2. You'll note that the CurrentVersion property only takes integer numbers - so no 0.9 versions are allowed!

The second thing you'll need is an override of the PerformUpgrade method - intellisense ought to start that for you nicely. Inside the PerformUpgrade function, you'll need to completely ignore the "pipelineVersion" argument - it has NOTHING to do with the version(s) of your component or metadata. It's an unused (almost undocumented) argument that may or may not get used by Microsoft at a later date. (Conspiracy theorists undoubtedly think they use it right now to make their components outperform third party ones - but anyone watching closely ought to know it isn't making a difference for them.)

You need to retrieve two version numbers - the current version of the binary component (DLL) installed on the system where the package is being designed or executed, and the version of the metadata stored in the dtsx file. I'll refer to those from here on as "binary version" and "metadata version".

You could simply hand-maintain the binary version awareness - every time you change the metadata (custom properties) of your component sufficiently, you could change the DtsPipelineComponent property by hand (which you MUST do), and then hand-code the same number in your PerformUpgrade method to let it know what the binary version is. Of course, automating that so that you only ever have to hand-maintain one version number works best. So inside your PerformUpgrade method, here's how you retrieve your binary version:

Once you have that, then you need to retrieve the version of the component's metadata that's in the dtsx file - the metadata version:

Now that you know both, you can compare them. If they're the same, you obviously need to do nothing. If your binary version is higher than the metadata version, you need to upgrade your metadata. If your metadata version is higher than the binary version, I'd recommend throwing an Exception...

I also recommend "cascading" your upgrades like this:

What does "upgrading" entail? Well, there are three scenarios. First, you could have added more capabilities or configurability to your component, requiring that the user specify more settings - in this case, you need to allocate more custom property "space" and a default value. Second, you could have deprecated (removed) a capability or configuration - in this case you could remove the custom property "space", although that's not strictly necessary (you'll just ignore those custom properties). Third, and probably most common, is that your options for your component will have changed slightly, and either the data type, or defaults will have changed - in this case you will have to remove the old properties (remembering the user's settings) and create new ones, attempting to populate them as best you can into the new framework.

If you need to add capabilities, it's pretty simple - copy any code that's "new" from your ProvideComponentProperties method, and paste it in the appropriate upgrade block. ProvideComponentProperties only gets called when a component is placed on the design surface for the first time - it's called once, and only ever once, for any instance of a component.

If you need to remove capabilities, it can be easier - do nothing. The "defunct" capabilities from the old version of the component will continue to hang around in the metadata, but if you don't read them, they're simply ignored and harmless (except for the extra bytes they take up in the metadata). To really remove them properly, you need to know the property name, and do the following:

If you fit the third scenario, where you want to change the storage type(s) and/or names of your properties, load the old values first, "convert" them into the new paradigm (if possible) or default them, then create new properties.

The very last thing you need to do in PerformUpgrade is update the metadata's version. If you don't do that, then all the work you just went to goes out the door - and you actually make things worse! The last part of your method should go something like this:

And you're done...

In Summary

1. Set the CurrentVersion argument in the DtsPipelineComponent attribute of your class to the "current binary version".


2. Write your PerformUpgrade method to compare ComponentMetaData.Version (metadata version) to the CurrentVersion DtsPipelineComponent attribute (runtime version).


3. Remove, add, or alter the contents of your custom properties for your component.


4. Set the ComponentMetaData.Version (metadata version) to the CurrentVersion DtsPipelineComponent attribute value (runtime version).