THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, developer of the Data Integration Lifecycle Management (DILM) Suite, a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and the Stairway to Integration Services.

Use Catalog Compare to Migrate to the SSIS 2016 Catalog

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”:

SSISDBUpgrade_10

The Browse for Folder dialog displays. Select or create a target folder to hold the Catalog scripts and ISPAC files:

SSISDBUpgrade_11

Click the OK button. Once the export is complete, Catalog Compare’s status will display something similar to this message:

SSISDBUpgrade_12

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:

SSISDBUpgrade_13

Each script contains documentation that specifies where it was generated, when, by whom, and which version of Catalog Compare was used:

SSISDBUpgrade_14

Each ISPAC file uses the Integration Services Deployment Wizard:

SSISDBUpgrade_15

I conducted a quick test after the migration and found the newly deployed SSIS packages function in the SSIS 2016 Catalog:

SSISDBUpgrade_16

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
SSIS Design Patterns

Help!
Enterprise Data & Analytics – We are here to help.

Published Thursday, July 07, 2016 4:15 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News


My Companies



Community Awards

Friend of Red Gate

Contact Me

Archives

Privacy Statement