What is the recommended approach to migrate SSIS and related packages from sql server 2016 to sql server 2019?

Question

I have SSIS packages in msdb database as well as in the ssis catalog (ssis db).

For example -

Do I need to backup and restore the ssisdb?
or do I need to re-create the ssis catalog that will auto create the new ssisdb followed by manual deployment of the ssis packages?
How to handle ssis packages in the msdb?

You should find Upgrade and Migrate SSIS Catalog and SSISDB to a New Server helpful. — Shanky, Apr 13 at 14:25

MBuschi · Accepted Answer · 2022-04-13 11:46:07Z

-1

Option 1 and 2 are good options for ssisdb.

About the dtsx in the msdb, those usually come from wizards like import/export features in ssms or maintenace plans.

If you need them for sure it's quite complicated.

here you can find a good guide: link

answered Apr 13 at 10:54

MBuschi

3,4621 gold badge3 silver badges16 bronze badges

Does backup and restore of the ssisdb auto create the SSIS catalog?
– variable
Apr 13 at 11:06
@MBuschi You cannot restore msdb backup of sql server 2016 on SQL Server 2019, so how is option 1 good ?
– Shanky
Apr 13 at 11:22
updated. for ssisdb
– MBuschi
Apr 13 at 11:46
1

@MBuschi But option 1 is NOT possible at all. So you need to completely remove that because as per OP he is moving from 2016 to 2019.
– Shanky
Apr 13 at 12:06
The database will be updated by the engine to the last version if you run the SSISDB Upgrade Wizard. Be sure to backup/restore encryption key too. Otherwise go for the option 2.
– MBuschi
Apr 13 at 12:28

| Show 2 more comments

1 Answer 1