Various approaches for SSIS upgrade or migration from 2012 to 2016

As per my understanding there can be 3 approaches for SSIS upgrade or migration from 2012 to 2016.

We created a new instance of SSIS 2016 (SQL Server 2016 – SSIS catalog db).

  1. First approach is using SSDT open the package and it will get converted and then deploy to the 2016 server.
  2. Take the backup of 2012 package and restore the ISPAC package on 2016 server.
  3. In above 2 approaches we will have to do individually each package. So this third approach.
    1. On the 2016 server create SSIS catalog.
    2. Restore the SSISDB from 2012 to 2016 server.
    3. Run the SSIS Upgrade wizard.
    4. Update the Master Key using:

USE [SSISDB]
OPEN master Key decryption by password = ‘Password’ — when creating 2012 SSISDB
ALTER Master Key ADD encryption by Service Master Key

Update the connection strings and password.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s