使用 SSMS 将 SQL Server 代理作业迁移到 ADFMigrate SQL Server Agent jobs to ADF with SSMS

适用于: Azure 数据工厂 Azure Synapse Analytics

迁移 SSIS 包后,在 。When migrating on-premises SQL Server Integration Services (SSIS) workloads to SSIS in ADF, after SSIS packages are migrated, you can do batch migration of SQL Server Agent jobs with job step type of SQL Server Integration Services Package to Azure Data Factory (ADF) pipelines/activities/schedule triggers via SQL Server Management Studio (SSMS) SSIS Job Migration Wizard .

一般情况下,对于作业步骤类型适当的选定 SQL 代理作业,SSIS 作业迁移向导可以执行以下操作 :In general, for selected SQL agent jobs with applicable job step types, SSIS Job Migration Wizard can:

  • 将本地 SSIS 包位置映射到包所迁移到的、可由 ADF 中的 SSIS 访问的位置。map on-premises SSIS package location to where the packages are migrated to, which are accessible by SSIS in ADF.

    备注

    仅支持文件系统的包位置。Package location of File System is supported only.

  • 按如下所示将具有适用作业步骤的适用作业迁移到相应的 ADF 资源:migrate applicable jobs with applicable job steps to corresponding ADF resources as below:
SQL 代理作业对象SQL Agent job object ADF 资源ADF resource 注释Notes
SQL 代理作业SQL Agent job 管道pipeline 将为 <job name> 生成管道名称 。Name of the pipeline will be Generated for <job name> .

内置代理作业不适用:Built-in agent jobs are not applicable:
  • SSIS 服务器维护作业SSIS Server Maintenance Job
  • syspolicy_purge_historysyspolicy_purge_history
  • collection_set_*collection_set_*
  • mdw_purge_data_*mdw_purge_data_*
  • sysutility_*sysutility_*
  • SSIS 作业步骤SSIS job step 执行 SSIS 包活动Execute SSIS package activity
  • 活动名称将是 <step name>。Name of the activity will be <step name>.
  • 在作业步骤中使用的代理帐户将作为此活动的 Windows 身份验证进行迁移。Proxy account used in job step will be migrated as Windows authentication of this activity.
  • 在迁移中将忽略作业步骤中定义的除“使用 32 位运行时”以外的执行选项 。Execution options except Use 32-bit runtime defined in job step will be ignored in migration.
  • 在迁移中将忽略作业步骤中定义的验证 。Verification defined in job step will be ignored in migration.
  • scheduleschedule 计划触发器schedule trigger 将为 <schedule name> 生成计划触发器的名称 。Name of the schedule trigger will be Generated for <schedule name> .

    在迁移中将忽略 SQL 代理作业计划中的以下选项:Below options in SQL Agent job schedule will be ignored in migration:
  • 二级间隔。Second-level interval.
  • SQL Server 代理启动时自动启动Start automatically when SQL Server Agent starts
  • CPU 空闲时启动Start whenever the CPUs become idle
  • 工作日和周末 weekday and weekend day
    下面是将 SQL 代理作业计划迁移到 ADF 计划触发器后的差异:Below are the differences after SQL Agent job schedule is migrated to ADF schedule trigger:
  • ADF 计划触发器后续运行独立于先前已触发的运行的执行状态。ADF Schedule Trigger subsequent run is independent of the execution state of the antecedent triggered run.
  • ADF 计划触发器重复周期配置不同于 SQL 代理作业中的每日频率。ADF Schedule Trigger recurrence configuration differs from Daily frequency in SQL agent job.
    • 在本地输出文件夹中生成 Azure 资源管理器 (ARM) 模板,并直接部署到数据工厂,或在以后手动进行部署。generate Azure Resource Manager (ARM) templates in local output folder, and deploy to data factory directly or later manually. 有关 ADF 资源管理器模板的详细信息,请参阅 Microsoft.DataFactory 资源类型For more information about ADF Resource Manager templates, see Microsoft.DataFactory resource types.

    先决条件Prerequisites

    本文介绍的功能需要 SQL Server Management Studio 18.5 或更高版本。The feature described in this article requires SQL Server Management Studio version 18.5 or higher. 若要获取 SSMS 最新版本,请参阅下载 SQL Server Management Studio (SSMS)To get the latest version of SSMS, see Download SQL Server Management Studio (SSMS).

    将 SSIS 作业迁移到 ADFMigrate SSIS jobs to ADF

    1. 在 SSMS 的对象资源管理器中依次选择“SQL Server 代理”、“作业”,然后右键单击并选择“将 SSIS 作业迁移到 ADF” 。In SSMS, in Object Explorer, select SQL Server Agent, select Jobs, then right-click and select Migrate SSIS Jobs to ADF . 屏幕截图显示了 SQL Server Management Studio 对象资源管理器,你可以在其中选择“作业”,然后将 SSIS 作业迁移到 ADF。Screenshot shows SQL Server Management Studio Object Explorer, where you can select Jobs, then Migrate S S I S Jobs to A D F.

    2. 登录到 Azure,依次选择“Azure 订阅”、“数据工厂”、“Integration Runtime”。Sign In Azure, select Azure Subscription, Data Factory, and Integration Runtime. “Azure 存储”为可选。如果要迁移的 SSIS 作业具有 SSIS 文件系统包,在包位置映射步骤中将使用 Azure 存储。Azure Storage is optional, which is used in the package location mapping step if SSIS jobs to be migrated have SSIS File System packages. 菜单menu

    3. 将 SSIS 作业中的 SSIS 包和配置文件的路径映射到已迁移管道可以访问的目标路径。Map the paths of SSIS packages and configuration files in SSIS jobs to destination paths where migrated pipelines can access. 在此映射步骤中,可以执行以下操作:In this mapping step, you can:

      1. 选择一个源文件夹,然后选择“添加映射” 。Select a source folder, then Add Mapping .
      2. 更新源文件夹路径。Update source folder path. 有效的路径是包的文件夹路径或父文件夹路径。Valid paths are folder paths or parent folder paths of packages.
      3. 更新目标文件夹路径。Update destination folder path. 默认路径是在步骤 1 中选择的默认存储帐户的相对路径。Default is relative path to the default Storage account, which is selected in step 1.
      4. 通过“删除映射”删除选定的映射 。Delete a selected mapping via Delete Mapping . 屏幕截图显示了“映射 SSIS 包和配置路径”页,你可以在其中添加映射。 屏幕截图显示了“映射 SSIS 包和配置路径”页,你可以在其中更新源和目标文件夹路径。Screenshot shows the Map S S I S Package and Configuration Paths page, where you can add mapping. Screenshot shows the Map S S I S Package and Configuration Paths page, where you can update the source and destination folder paths.
    4. 选择要迁移的适用作业,并配置相应的“已执行的 SSIS 包活动”的设置 。Select applicable jobs to migrate, and configure the settings of corresponding Executed SSIS Package activity .

      • 默认设置:在默认情况下应用到所有选定步骤 。Default Setting , applies to all selected steps by default. 有关每个属性的详细信息,请查看当包位置为“文件系统(包)”时与“ 。For more information of each property, see Settings tab for the Execute SSIS Package activity when package location is File System (Package) . 屏幕截图显示了“选择 SSIS 作业”页,你可以在其中配置相应的“已执行 SSIS 包活动”的设置。Screenshot shows the Select S S I S Jobs page, where you can configure the settings of corresponding Executed SSIS Package activity.

      • 步骤设置:配置选定步骤的设置 。Step Setting , configure setting for a selected step.

        应用默认设置 :默认已选中。Apply Default Setting : default is selected. 如果取消选择此选项,则只配置选定步骤的设置。Unselect to configure setting for selected step only.
        有关其他属性的详细信息,请查看当包位置为“文件系统(包)”时与“ 。For more information of other properties, see Settings tab for the Execute SSIS Package activity when package location is File System (Package) . 屏幕截图显示了“选择 SSIS 作业”页,你可以在其中应用默认设置。Screenshot shows the Select S S I S Jobs page, where you can apply the default settings.

    5. 生成并部署 ARM 模板。Generate and deploy ARM template.

      1. 选择或输入已迁移的 ADF 管道的 ARM 模板的输出路径。Select or input the output path for the ARM templates of the migrated ADF pipelines. 系统会自动创建文件夹(如果不存在)。Folder will be created automatically if not exists.
      2. 选择选项“将 ARM 模板部署到数据工厂” :Select the option of Deploy ARM templates to your data factory :
        • 默认未选中。Default is unselected. 以后可以手动部署生成的 ARM 模板。You can deploy generated ARM templates later manually.
        • 选择将生成的 ARM 模板直接部署到数据工厂。Select to deploy generated ARM templates to data factory directly. 屏幕截图显示了“配置迁移”页,你可以在其中选择或输入已迁移 ADF 管道的 ARM 模板的输出路径,并选择“将 ARM 模板部署到数据工厂”选项。Screenshot shows the Configure Migration page, where you can select or input the output path for the ARM templates of the migrated ADF pipelines and select the option of Deploy ARM templates to your data factory.
    6. 迁移,然后检查结果。Migrate, then check results. 屏幕截图显示了“迁移结果”页,该页显示了迁移进度。Screenshot shows the Migration Result page, which displays the progress of the migration.

    后续步骤Next steps

    运行和监视管道Run and monitor pipeline