Feedback Edit

Migrate on-premises SSIS workloads to SSIS in ADF

  • Article
  • 4 minutes to read

APPLIES TO: Azure Data Factory Azure Synapse Analytics

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) 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.

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.

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 and the migration destination of database workloads, the steps to migrate SSIS packages and SQL Server Agent jobs that schedule SSIS package executions may vary. There are two scenarios:

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

Azure SQL Managed Instance as database workload destination

Package storage type How to batch-migrate SSIS packages How to batch-migrate SSIS jobs
SSISDB
  • Redeploy packages via SSDT/SSMS to SSISDB hosted in Azure Managed Instance. For more info, see Deploying SSIS packages in Azure.
  • Migrate SSISDB
    		•
  • Migrate SSIS jobs to 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 SSIS jobs to 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 SSIS jobs to Azure SQL Managed Instance agent
  • Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. For more info, see SSMS scheduling feature.
    		•

    Azure SQL Database as database workload destination

    Package storage type How to batch-migrate SSIS packages How to batch-migrate jobs
    SSISDB Redeploy packages via SSDT/SSMS to SSISDB hosted in Azure SQL Database. For more info, see Deploying SSIS packages in Azure. 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 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 file systems/file shares/Azure Files via SSMS/dtutil or redeploy them to file shares/Azure Files via dtinstall/dtutil/manual copy or keep them in file systems to access via VNet/Self-Hosted IR. For more info, see dtutil utility. For more info, see dtutil utility. Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. For more info, see SSMS scheduling feature.

    Additional resources

    Next steps