Upgrading SSIS from 2005 to 2008 R2: Pitfalls and Fixes
By Tom Nonmacher
Upgrading from SQL Server Integration Services (SSIS) 2005 to 2008 R2 is a necessary step for many organizations and developers. This transition allows you to take advantage of new features and improvements in performance and security. However, the process is not without its challenges. This blog post will describe some common pitfalls and their fixes during this upgrade.
One common issue arises with deprecated features. SSIS 2005 features such as ActiveX Script Tasks and Data Transformation Services (DTS) 2000 package execution are not supported in SSIS 2008 R2. When upgrading, you must replace or rewrite these features. For example, you can replace ActiveX Script Tasks with Script Tasks, which use a more modern, .NET-based scripting language.
-- Replace ActiveX Script Tasks with Script Tasks
-- This is an example of a rewritten Script Task in VB.NET
Dts.Variables("User::MyVariable").Value = "New Value"
Dts.TaskResult = ScriptResults.Success
Another common pitfall is the issue with configurations. SSIS 2005 allows configurations to be applied at the package level. However, in SSIS 2008 R2, configurations are applied at the project level. Thus, when upgrading, you must ensure that configurations are properly transitioned to avoid unexpected behavior.
Connectivity can also pose problems during the upgrade. Connection strings and providers that worked in SSIS 2005 may not work in SSIS 2008 R2. In particular, if you're connecting to newer databases such as SQL Server 2012, SQL Server 2014, MySQL 5.6, or DB2 10.5, or to cloud databases like Azure SQL, you may need to update your connection strings and install new providers.
The last common pitfall we will discuss is the change in the deployment model. SSIS 2005 uses the package deployment model, where each package is deployed individually. However, SSIS 2008 R2 introduces the project deployment model, where all the packages in a project are deployed at once. This change can cause issues if some packages in a project are not ready for deployment. To avoid this, you may need to reorganize your projects and packages.
Upgrading SSIS from 2005 to 2008 R2 can be a complex process, but understanding these common pitfalls can help you navigate it. By carefully planning your upgrade and testing thoroughly, you can minimize disruption and take full advantage of the new features and improvements in SSIS 2008 R2.