I recently tried to use the SSISDB Upgrade Wizard to upgrade a restored SSISDB (backed up in an earlier version) to SQL Server 2016. It didn’t go well.
I decided to use SSIS Catalog Compare to generate the scripts and ISPAC files from the previous instance, and deploy them to the SSIS 2016 Catalog.
“You Can Do That?”
Yes. Yes you can. Here’s how…
First, connect to an SSIS 2014 Catalog using Catalog Compare. Right-click the SSISDB node and click “Generate All Catalog Scripts”:
The Browse for Folder dialog displays. Select or create a target folder to hold the Catalog scripts and ISPAC files:
Click the OK button. Once the export is complete, Catalog Compare’s status will display something similar to this message:
A directory is created for each SSIS Catalog folder, and the scripts and ISPAC files are stored in that directory. They are numbered in order of dependencies:
Each script contains documentation that specifies where it was generated, when, by whom, and which version of Catalog Compare was used:
Each ISPAC file uses the Integration Services Deployment Wizard:
I conducted a quick test after the migration and found the newly deployed SSIS packages function in the SSIS 2016 Catalog:
At the time of this writing, SSIS Catalog Compare is in beta, and we’re offering a pretty sweet deal on it. Learn more here.
:{>
Learn more:
Related Training:
IESSIS2: Immersion Event on Advanced SQL Server Integration Services
Related Articles:
Stairway to Integration Services
SSIS 2016 Administration: Create the SSIS Catalog
Announcing SSIS Catalog Reports v0.1
SSIS Framework Community Edition (a free, open-source, SSIS Catalog-integrated execution framework)
Stairway to Biml
Related Books 
Help!
Enterprise Data & Analytics – We are here to help.
Comments