Share via

How to migrate SSIS components from SQL Server 2012 Instance to newer SQL Server 2016 Instance

techresearch7777777 1,801 Reputation points
2024-03-25T23:04:59.48+00:00

Hello, we have a SQL Server 2012 Instance (includes SSIS services installed/enabled) on one server and needs to be migrated to an existing SQL Server 2016 Instance (does not have SSIS services installed/enabled) on a separate different server.

This SQL 2012 Instance has SSIS installed (which includes the automatically created [SSISDB] DB and noticed within the "Integration Services Catalogs" section there are a bunch of .dtsx Packages listed and also some SQL Server 2012 Agent jobs that uses SSIS which looks like does some type of data load from another external SQL Server.

How would one go to migrating this whole SSIS portion from this old SQL 2012 Instance to the newer SQL 2016 Instance?

(Just brainstorming like take backup of [SSISDB] on SQL 2012 and restore onto newer SQL 2016 and some how script out their related old SQL 2012 Agent jobs and run/copy them onto newer SQL 2016, and some how export all of those .dtsx Packages to newer SQL 2016 Instance?... or install/enable SSIS services and let newer SQL 2016 SSIS re-create it's own [SSISDB], or something else like need to re-create those SSIS related .dtsx Packages & Agent Jobs from scratch?)

We would like to avoid enabling SSIS services on the newer SQL 2016... is there a way I can dig deeper within the SQL 2012 Agent jobs to see what the SSIS portion is actually doing?

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 35,556 Reputation points
    2024-03-26T02:51:07.7366667+00:00

    Hi @techresearch7777777,

    Metod1.

    You may restore the SSIS database and encryption key from the backup of the source server to the new server. This approach retains the execution history, project versions, permissions, environment variables, SSIS catalog properties, etc. If your business needs execution history for reporting usage statistics or trend analysis, you may want to choose this approach.

    Metod2.

    Use SSIS Catalog Migration Wizard

    Details you may check Upgrade and Migrate SSIS Catalog and SSISDB to a New Server.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.