I recently inherited a small data warehouse developed in SSIS 2005. It was only written three months ago so you’d expect it to be pretty compatible with modern technology. What I found was a horror story - the highlights from which are outlined below in case anybody else ever comes across similar issues.

Java Zip

If you search google for how to unzip in SQL Server, the number one hit is some VB.net code that calls a Java class. It works just fine in 2005 but unfortunately that class is not distributed in 2008 (some people claim installing J# adds it in, while others claim that does not help).

In 2008 things are much more complex. One option is to rewrite the script using System.IO.compression but this is far lower level which makes it quite unattractive as an option. Another is to install a third-party zip library - again, not the simplest. I eventually decided the best solution was to call out to an executable (7-zip) which proved painless and quite efficient.

SQLNCLI.1

In 2005, the default connection to SQL Server is done using the SQL native client. This is deprecated in 2008 in favour of a new native client - from memory SQLNCLI10. It isn’t just phased out slowly, the 2005 client is not even installed on 2008.

The migration wizard automatically converts this for you - unfortunately the migration wizard also attempts to connect to the database at various points in the migration - using SQLNCLI.1. As a result your migration will fail as the wizard is unable to connect to the database. The only workaround I have found is to install the 2005 client in 2008 so that it is available to the wizard.

Memory of past connections

One of the bugs in Visual Studio 2008 is it keeps some metadata about the way things used to be. One particularly annoying instance of this is that the package ‘remembers’ the old and invalid SQLNCLI.1 connections and keeps migrating back to them when the package is closed. The only workaround I’ve found is to quit Visual Studio and use find and replace on the XML files.

Critical metadata in XML

One issue I had with the package was spelling errors - E.g. Retail misspelt as Retial. Naturally I corrected these as I found them, but I did not check the XML support files. Since the package parses the support file before loading and a failure there will mean the support file is ignored, correcting the spelling in the GUI and neglecting to correct it in the XML will corrupt the package.

Conclusion

At the end of the process I believe I spent longer on the migration than it would have taken me to recreate the entire package in 2008 (by visually selecting the same options). It seems that there are two big annoyances in what Microsoft has done - firstly by changing connection managers, Microsoft has added a lot of work and secondly hard-coding critical data outside the GUI, Microsoft has made the wizard almost useless. I see that SQL Server 2010 repeats the same mistake, requiring a migration from OLE (the only option supported in 2005 and the default in 2008) to ADO.

I won’t even pretend OLE is better than ADO - but I do not think it is good development practice to have the default in one release unusable by default in the very next release - you have to give people a workable migration strategy.

Corrin's Website