What to keep in mind while migrating SSIS packages from SQL Server 2005 to 2008?

Question

What are best practices for moving/exporting SQL Server Integration Services Packages from a SQL Server 2005 DB over to 2008?

What are some of the security concerns?

Answer 1

It turns out that migrating from 2005 to 2008 isn't as ugly as it may seem. Everything I've read indicates that the Visual Studio 2008 environment will automatically convert the SSIS package to the newer format.

An important thing to note is that Visual Studio 2008 will be able to run SSIS 2005 packages. However, the moment you save the package in the designer or use the conversion tool, the package will be converted to SSIS 2008 format and will not be able to be used in Visual Studio 2005 again.

A few important notes I've made about the upgrade process:

• VSA (Visual Studio for Applications) in SSIS 2005 has been replaced with VSTA (Visual Studio Tools for Applications in SSIS 2008. While the upgrade process typically works fine. However, if you use any references to IDTSxxx90 interfaces, you need to make sure they were updated to reference IDTSxxx100 interfaces.

• Provider names have changed slightly between SSIS 2005 and SSIS 2008 for SQL Native Client and OLE DB Connection Strings, so make sure to use the upgrade wizard or to manually check the connection string settings.

A few resources I've found regarding all this information comes from:

• SSIS 2008 Cribsheet

• MSDN Integration Services Backwards Compatability documentation

• Michael Entin's Notebook (MSDN Blog)

...hey, I learned something too since we're getting ready to approach this same issue.