将本地 SSIS 工作负荷迁移到 ADF 中的 SSISMigrate on-premises SSIS workloads to SSIS in ADF

适用于: Azure 数据工厂 Azure Synapse Analytics(预览版)

概述Overview

将数据库工作负荷从本地 SQL Server 迁移到 Azure 数据库服务(即 Azure SQL 数据库或 Azure SQL 托管实例)时,也需要迁移 SQL Server Integration Services (SSIS)(主要的增值服务之一)上的 ETL 工作负荷。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 数据工厂 (ADF) 中 Azure-SSIS Integration Runtime (IR) 支持运行 SSIS 包。Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF) supports running SSIS packages. 预配 Azure-SSIS IR 后,可以使用熟悉的工具(例如 SQL Server Data Tools (SSDT)/SQL Server Management Studio (SSMS),以及 dtinstall/dtutil/dtexec 等命令行实用工具)在 Azure 中部署和运行包。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. 有关详细信息,请参阅 Azure SSIS 直接迁移概述For more info, see Azure SSIS lift-and-shift overview.

本文重点介绍将 ETL 工作负荷从本地 SSIS 迁移到 ADF 中的 SSIS 的过程。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

若要建立完整的迁移计划,可以进行全面的评估,以帮助识别源 SSIS 包中存在的会妨碍成功迁移的问题。To establish a complete migration plan, a thorough assessment will help identify issues with the source SSIS packages that would prevent a successful migration.

数据迁移助手 (DMA) 是一个可免费下载的工具,可在本地安装和执行以实现此目的。Data Migration Assistant (DMA) is a freely downloadable tool for this purpose that can be installed and executed locally. 可以创建“Integration Services”类型的 DMA 评估项目来分批评估 SSIS 包,并识别以下类别的兼容性问题: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:

  • 迁移阻碍因素:阻碍迁移源包在 Azure-SSIS IR 上运行的兼容性问题。Migration blockers: compatibility issues that block the migration source packages to run on Azure-SSIS IR. DMA 将提供指导来帮助你解决这些问题。DMA provides guidance to help you address these issues.

  • 信息性问题:源包中使用的部分受支持的或已弃用的功能。Informative issues: partially supported or deprecated features that are used in source packages. DMA 将提供全面的建议、Azure 中可用的替代方法和缓解步骤来解决这些问题。DMA provides a comprehensive set of recommendations, alternative approaches available in Azure, and mitigating steps to resolve.

SSIS 包的四种存储类型Four storage types for SSIS packages

  • SSIS 目录 (SSISDB)。SSIS catalog (SSISDB). 在 SQL Server 2012 中引入,其中包含一组用于处理 SSIS 项目/包的存储过程、视图和表值函数。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 系统数据库 (MSDB)。SQL Server system database (MSDB).
  • SSIS 包存储。SSIS Package Store. 位于两个子类型之上的包管理层:A package management layer on top of two subtypes:
    • MSDB:SQL Server 中用于存储 SSIS 包的系统数据库。MSDB, which is a system database in SQL Server used to store SSIS packages.
    • 托管文件系统:SQL Server 安装路径中用于存储 SSIS 包的特定文件夹。Managed file system, which is a specific folder in SQL Server installation path used to store SSIS packages.

DMA 当前支持对自 DMA 版本 5.0 以来存储在 文件系统包存储SSIS 目录 中的包进行批量评估。DMA currently supports the batch-assessment of packages stored in File System , Package Store , and SSIS catalog since DMA version v5.0 .

获取 DMA,并使用它来执行包评估Get DMA, and perform your package assessment with it.

迁移Migration

根据源 SSIS 包的 存储类型以及数据库工作负荷的迁移目标,迁移 SSIS 包 的步骤,以及迁移计划 SSIS 包执行的 SQL 服务器代理作业 的步骤可能不同。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:

将 Azure SQL 托管实例用作数据库工作负荷目标Azure SQL Managed Instance as database workload destination

包存储类型Package storage type 如何批量迁移 SSIS 包How to batch-migrate SSIS packages 如何批量迁移 SSIS 作业How to batch-migrate SSIS jobs
SSISDBSSISDB 迁移 SSISDBMigrate SSISDB
  • 将 SSIS 作业迁移到 Azure SQL 托管实例代理Migrate SSIS jobs to Azure SQL Managed Instance agent
  • 通过脚本/SSMS/ADF 门户将其转换为 ADF 管道/活动/触发器。Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. 有关详细信息,请参阅 SSMS 计划功能For more info, see SSMS scheduling feature.
  • 文件系统File System 通过 dtinstall/dtutil/手动复制将其重新部署到文件共享/Azure 文件,或将其保留在文件系统中,以通过 VNet/自承载 IR 进行访问。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. 有关详细信息,请参阅 dtutil 实用工具For more info, see dtutil utility.
  • 将 SSIS 作业迁移到 Azure SQL 托管实例代理Migrate SSIS jobs to Azure SQL Managed Instance agent
  • 通过 SSMS 中的 SSIS 作业迁移向导进行迁移Migrate with SSIS Job Migration Wizard in SSMS
  • 通过脚本/SSMS/ADF 门户将其转换为 ADF 管道/活动/触发器。Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. 有关详细信息,请参阅 SSMS 计划功能For more info, see SSMS scheduling feature.
  • SQL Server (MSDB)SQL Server (MSDB) 通过 SSMS/dtutil 将其导出到文件系统/文件共享/Azure 文件。Export them to file systems/file shares/Azure Files via SSMS/dtutil. 有关详细信息,请参阅导出 SSIS 包For more info, see Exporting SSIS packages. 通过脚本/SSMS/ADF 门户将其转换为 ADF 管道/活动/触发器。Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. 有关详细信息,请参阅 SSMS 计划功能For more info, see SSMS scheduling feature.
    包存储Package Store 通过 SSMS/dtutil 将它们导出到包存储,或通过 dtinstall/dtutil/手动复制将它们重新部署到包存储。Export them to package store via SSMS/dtutil or redeploy them to package store via dtinstall/dtutil/manual copy. 有关详细信息,请参阅使用 Azure-SSIS Integration Runtime 包存储来管理包For more info, see Manage packages with Azure-SSIS Integration Runtime package store.
  • 将 SSIS 作业迁移到 Azure SQL 托管实例代理Migrate SSIS jobs to Azure SQL Managed Instance agent
  • 通过脚本/SSMS/ADF 门户将其转换为 ADF 管道/活动/触发器。Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. 有关详细信息,请参阅 SSMS 计划功能For more info, see SSMS scheduling feature.
  • Azure SQL 数据库 用作数据库工作负荷目标Azure SQL Database as database workload destination

    包存储类型Package storage type 如何批量迁移 SSIS 包How to batch-migrate SSIS packages 如何批量迁移作业How to batch-migrate jobs
    SSISDBSSISDB 通过 SSDT/SSMS 重新部署到 Azure-SSISDB。Redeploy to Azure-SSISDB via SSDT/SSMS. 有关详细信息,请参阅在 Azure 中部署 SSIS 包For more info, see Deploying SSIS packages in Azure. 通过脚本/SSMS/ADF 门户将其转换为 ADF 管道/活动/触发器。Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. 有关详细信息,请参阅 SSMS 计划功能For more info, see SSMS scheduling feature.
    文件系统File System 通过 dtinstall/dtutil/手动复制将其重新部署到文件共享/Azure 文件,或将其保留在文件系统中,以通过 VNet/自承载 IR 进行访问。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. 有关详细信息,请参阅 dtutil 实用工具For more info, see dtutil utility.
  • 通过 SSMS 中的 SSIS 作业迁移向导进行迁移Migrate with SSIS Job Migration Wizard in SSMS
  • 通过脚本/SSMS/ADF 门户将其转换为 ADF 管道/活动/触发器。Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. 有关详细信息,请参阅 SSMS 计划功能For more info, see SSMS scheduling feature.
  • SQL Server (MSDB)SQL Server (MSDB) 通过 SSMS/dtutil 将其导出到文件系统/文件共享/Azure 文件。Export them to file systems/file shares/Azure Files via SSMS/dtutil. 有关详细信息,请参阅导出 SSIS 包For more info, see Exporting SSIS packages. 通过脚本/SSMS/ADF 门户将其转换为 ADF 管道/活动/触发器。Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. 有关详细信息,请参阅 SSMS 计划功能For more info, see SSMS scheduling feature.
    包存储Package Store 通过 SSMS/dtutil 将其导出到文件系统/文件共享/Azure 文件,或通过 dtinstall/dtutil/手动复制将其重新部署到文件共享/Azure 文件,或将其保留在文件系统中,以通过 VNet/自承载 IR 进行访问。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. 有关详细信息,请参阅“dtutil 实用工具”。For more info, see dtutil utility. 有关详细信息,请参阅 dtutil 实用工具For more info, see dtutil utility. 通过脚本/SSMS/ADF 门户将其转换为 ADF 管道/活动/触发器。Convert them into ADF pipelines/activities/triggers via scripts/SSMS/ADF portal. 有关详细信息,请参阅 SSMS 计划功能For more info, see SSMS scheduling feature.

    其他资源Additional resources

    后续步骤Next steps