1

We have a database running SQL Server 2008R2 which requires an upgrade (to SQL Server 2014) because it is a pre-requisite for the latest edition of the software which the database is the back end of.

Integration Services (2008R2) is also running on this server, with many .dtsx packages stored in MSDB. They are mainly used for ETL purposes and transforming data for reporting reasons.

Due to the nature of the environment, it will need to be an in-place upgrade. The database engine upgrade will be a breeze, but I was wondering specifically about the .dtsx packages in MSDB. Will they automatically upgrade when the database engine and integration services is upgraded, or will there be a need to manually upgrade them after this.

Are there any known issues with upgrading Integration Services between 2008R2 and 2014 SP2?

Fortunately we have a mirrored environment running in a VM which we can mess around with in terms of testing the application (and packages) before we move to Live.

Thanks

1

As per BOL

When you upgrade to SQL Server 2014 Integration Services (SSIS) on a machine that has one of these earlier versions of Integration Services installed, SQL Server 2014 Integration Services (SSIS) is installed side-by-side with the earlier version.

It also says that:

  • You can upgrade by using one of the following methods: Run SQL Server 2014 Setup and select the option to Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2, or SQL Server 2012.
  • Run setup.exe at the command prompt and specify the /ACTION=upgrade option. For more information, see the section, "Installation Scripts for Integration Services," in Install SQL Server 2014 from the Command Prompt.

Source

Now, as per my personal experience with In-Place SQL Server upgrade from SQL Server 2008 R2 to SQL Server 2012 I didn't face compatibility issues. Packages ran fine, except for few where I had to open packages and compile script task again.

0

If you backup and restore your msdb from 2008 to 2014, then the SSIS packages currently stored there will exist in your 2014 instance but they will still be in a 2008 format. Every time they run, they will first be upgraded in memory to a 2014 format, executed and then the updated version discarded. If that additional overhead doesn't affect your SLA, then all is well.

The change in Scripting can cause upgrade issues as well as any third party components.

See also Upgrading SSIS packages

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.