Behaving Badly with SSIS 2008…

Normally, Microsoft makes great effort on the backward compatibility components of a new release so that anything developed in an older release continues to function as designed. As SQL Server supports more and more functionality this becomes a more difficult task. We have seen some tough upgrades over the years. SQL Server 6.5 to 7.0 comes to mind. SQL Server 2000 to 2005 was not easy either.

For instance, SQL Server Integration Services was introduced in SQL Server 2005 as the new version of DTS 2000. However, Microsoft let us know that since SSIS was a complete rewrite, it could not guarantee that 100% of DTS code could be converted to the SSIS model. OK, SSIS provided much greater scalability and functionality, but backward compatibility is a big issue for users that have invested countless hours of development.

The solution was to continue to support DTS packages in parallel with SSIS so that migration of packages could be delayed to a later time. An existing DTS package could be executed from within an SSIS package with no changes. However, the reality was that most packages would need to be re-written in SSIS to benefit from the new architecture. Microsoft has now let us know that DTS will no longer be supported at the next major release which presumably will be within the next couple years so the clock is ticking.

With SQL Server 2008 being an incremental release you would have thought that upgrading packages from SSIS 2005 would be fairly straight forward. Not so fast. One big difference I have noticed is the execution of packages using configuration files. These files allow you to modify variables at run-time giving a dynamic approach to your packages. At design time, you define the variables, provide default values and generate a configuration file. At deployment, that configuration file is used by the Package Installer Wizard.

In SSIS 2005, we could use a copy of the configuration file at run-time with changes to the variable values to override the design-time values using the /ConfigFile switch of DTEXEC.exe. Now in SSIS 2008, this strategy will not work. This is because the design-time variable values are reloaded after any configuration changes. So, executing a package with the same DTEXEC command from 2005 will produce different results in 2008. Not good.

At first I thought this must be my mistake. After thorough testing, I thought it was a bug. Then I found out that it was a feature. Don’t believe me? Check out “Behavior Changes to Integration Services Features in SQL Server 2008” at http://msdn.microsoft.com/en-us/library/bb500430.aspx and look for “Behavior Changes Related to Package Configurations”.  It’s meant to work that way. So Microsoft can say “it works as designed”. If you are upgrading from SSIS 2005, I recommend studying that topic carefully and performing thorough testing.

I am not entirely sure why this was changed, (ours is not to question why) but now in SSIS 2008 our strategy has to change. You can still provide a run-time configuration file but the variables referenced cannot be the same as in the design-time configuration file otherwise they will be reset to the default values. All this comes out in testing but it’s a shame that the behavior had to change with no option to maintain the 2005 behavior.  I guess that would be too confusing. And this is not?

As I always say, think of it as job security. If it was easy, everyone would be doing it.

Cheers,

Brian

In this article

Join the Conversation