Migrate on-premises SSIS workloads to SSIS in ADF or Synapse Pipelines

  • Article

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Tip

Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

Overview

When you migrate your database workloads from SQL Server on premises to Azure database services, namely Azure SQL Database or Azure SQL Managed Instance, your ETL workloads on SQL Server Integration Services (SSIS) as one of the primary value-added services will need to be migrated as well.

Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF) or Synapse Pipelines supports running SSIS packages. Once Azure-SSIS IR is provisioned, you can then use familiar tools, such as SQL Server Data Tools (SSDT)/SQL Server Management Studio (SSMS), and command-line utilities, such as dtinstall/dtutil/dtexec, to deploy and run your packages in Azure. For more info, see Azure SSIS lift-and-shift overview.

This article highlights migration process of your ETL workloads from on-premises SSIS to SSIS in ADF. The migration process consists of two phases: Assessment and Migration.

Important

Data Migration Assistant (DMA) is deprecated. For more information, see the DMA product documentation.

Assessment

To establish a complete migration plan, a thorough assessment will help identify issues with the source SSIS packages that would prevent a successful migration.

Data Migration Assistant (DMA) is a freely downloadable tool for this purpose that can be installed and executed locally. DMA assessment project of type Integration Services can be created to assess SSIS packages in batches and identify compatibility issues that are presented in the following categories:

  • Migration blockers: compatibility issues that block the migration source packages to run on Azure-SSIS IR. DMA provides guidance to help you address these issues.

  • Informative issues: partially supported or deprecated features that are used in source packages. DMA provides a comprehensive set of recommendations, alternative approaches available in Azure, and mitigating steps to resolve.

You get detail list of migration blockers and informative issues here.

Four storage types for SSIS packages

  • SSIS catalog (SSISDB). Introduced with SQL Server 2012 and contains a set of stored procedures, views, and table-valued functions used for working with SSIS projects/packages.
  • File System.
  • SQL Server system database (MSDB).
  • SSIS Package Store. A package management layer on top of two subtypes:
    • MSDB, which is a system database in SQL Server used to store SSIS packages.
    • Managed file system, which is a specific folder in SQL Server installation path used to store SSIS packages.

DMA currently supports the batch-assessment of packages stored in File System, Package Store, and SSIS catalog since DMA version v5.0.

Get DMA, and perform your package assessment with it.

Migration

Depending on the storage types of source SSIS packages, the steps to migrate SSIS packages and SQL Server Agent jobs that schedule SSIS package executions may vary.

It is also a practical way to use SSIS DevOps Tools, to do batch package redeployment to the migration destination.

Package storage type How to migrate SSIS packages How to migrate SSIS jobs
SSISDB Redeploy packages via SSDT/SSMS to SSISDB hosted in Azure SQL or SQL Managed Instance. For more info, see Deploying SSIS packages in Azure.
  • Migrate from SQL Server Agent on premises to SQL Managed Instance agent via scripts/manual copy. For more info, see run SSIS packages via Azure SQL Managed Instance Agent
  • Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. For more info, see SSMS scheduling feature.
    		•
    File System Redeploy them to file shares/Azure Files via dtinstall/dtutil/manual copy, or to keep in file systems to access via VNet/Self-Hosted IR. For more info, see dtutil utility.
  • Migrate from SQL Server Agent on premises to SQL Managed Instance agent via scripts/manual copy. For more info, see run SSIS packages via Azure SQL Managed Instance Agent
  • Migrate with SSIS Job Migration Wizard in SSMS
  • Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. For more info, see SSMS scheduling feature.
    		•
    SQL Server (MSDB) Export them to file systems/file shares/Azure Files via SSMS/dtutil. For more info, see Exporting SSIS packages. Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. For more info, see SSMS scheduling feature.
    Package Store Export them to package store via SSMS/dtutil or redeploy them to package store via dtinstall/dtutil/manual copy. For more info, see Manage packages with Azure-SSIS Integration Runtime package store.
  • Migrate from SQL Server Agent on premises to SQL Managed Instance agent via scripts/manual copy. For more info, see run SSIS packages via Azure SQL Managed Instance Agent
  • Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. For more info, see SSMS scheduling feature.
    		•

    Additional resources

    Related content