如何按计划启动和停止 Azure-SSIS Integration RuntimeHow to start and stop Azure-SSIS Integration Runtime on a schedule

本文介绍如何使用 Azure 数据工厂 (ADF) 计划 Azure-SSIS Integration Runtime (IR) 的启动和停止。This article describes how to schedule the starting and stopping of Azure-SSIS Integration Runtime (IR) by using Azure Data Factory (ADF). Azure-SSIS IR 是专用于执行 SQL Server Integration Services (SSIS) 包的 ADF 计算资源。Azure-SSIS IR is ADF compute resource dedicated for executing SQL Server Integration Services (SSIS) packages. 运行 Azure-SSIS IR 会产生相关成本。Running Azure-SSIS IR has a cost associated with it. 因此,通常只有在需要在 Azure 中运行 SSIS 包时才运行 IR,而不再需要该包时则停止 IR。Therefore, you typically want to run your IR only when you need to execute SSIS packages in Azure and stop your IR when you do not need it anymore. 可以使用 ADF 用户界面 (UI)/应用或 Azure PowerShell 手动启动或停止 IRYou can use ADF User Interface (UI)/app or Azure PowerShell to manually start or stop your IR).

或者,可以在 ADF 管道中创建 Web 活动,以按计划启动/停止 IR,例如,在早上执行每日 ETL 工作负载之前启动 IR,并在下午完成后将其停止。Alternatively, you can create Web activities in ADF pipelines to start/stop your IR on schedule, e.g. starting it in the morning before executing your daily ETL workloads and stopping it in the afternoon after they are done. 还可以在启动和停止 IR 的两个 Web 活动之间链接一个执行 SSIS 包活动,这样 IR 就会按需在包执行之前/之后及时启动/停止。You can also chain an Execute SSIS Package activity between two Web activities that start and stop your IR, so your IR will start/stop on demand, just in time before/after your package execution. 有关执行 SSIS 包活动的详细信息,请参阅在 ADF 管道中使用执行 SSIS 包活动运行 SSIS 包一文。For more info about Execute SSIS Package activity, see Run an SSIS package using Execute SSIS Package activity in ADF pipeline article.

Note

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

先决条件Prerequisites

如果尚未配置 Azure-SSIS IR,请按照教程中的说明进行配置。If you have not provisioned your Azure-SSIS IR already, provision it by following instructions in the tutorial.

创建和安排启动和/或停止 Azure-SSIS IR 的 ADF 管道Create and schedule ADF pipelines that start and or stop Azure-SSIS IR

本部分演示如何在 ADF 管道中使用 Web 活动按计划或按需启动/停止 Azure-SSIS IR。This section shows you how to use Web activities in ADF pipelines to start/stop your Azure-SSIS IR on schedule or start & stop it on demand. 我们将指导你创建三个管道:We will guide you to create three pipelines:

  1. 第一个管道包含启动 Azure-SSIS IR 的 Web 活动。The first pipeline contains a Web activity that starts your Azure-SSIS IR.
  2. 第二个管道包含停止 Azure-SSIS IR 的 Web 活动。The second pipeline contains a Web activity that stops your Azure-SSIS IR.
  3. 第三个管道包含一个执行 SSIS 包活动,该活动链接在两个启动/停止 Azure-SSIS IR 的 Web 活动之间。The third pipeline contains an Execute SSIS Package activity chained between two Web activities that start/stop your Azure-SSIS IR.

创建并测试管道后,可以创建一个计划触发器,并将其与任何管道相关联。After you create and test those pipelines, you can create a schedule trigger and associate it with any pipeline. 计划触发器定义了运行相关管道的计划。The schedule trigger defines a schedule for running the associated pipeline.

例如,可以创建两个触发器,第一个触发器计划在每天上午 6 点运行并与第一个管道相关联,而第二个触发器计划在每天晚上 6 点运行并与第二个管道相关联。For example, you can create two triggers, the first one is scheduled to run daily at 6 AM and associated with the first pipeline, while the second one is scheduled to run daily at 6 PM and associated with the second pipeline. 通过这种方式,IR 会在每天上午 6 点到晚上 6 点这一时段运行,可用于执行每日 ETL 工作负载。In this way, you have a period between 6 AM to 6 PM every day when your IR is running, ready to execute your daily ETL workloads.

如果创建第三个触发器,计划在每天午夜运行并与第三个管道相关联,那么该管道将在每天午夜运行,在包执行前才启动 IR,随后执行包,然后在包执行后立即停止 IR,这样 IR 就不会空闲运行。If you create a third trigger that is scheduled to run daily at midnight and associated with the third pipeline, that pipeline will run at midnight every day, starting your IR just before package execution, subsequently executing your package, and immediately stopping your IR just after package execution, so your IR will not be running idly.

创建 ADFCreate your ADF

  1. 登录到 Azure 门户Sign in to Azure portal.

  2. 在左侧菜单中单击“新建”,并依次单击“数据 + 分析”、“数据工厂”。 Click New on the left menu, click Data + Analytics, and click Data Factory.

    新建 -> DataFactory

  3. 在“新建数据工厂”页中,输入“MyAzureSsisDataFactory”作为名称 。In the New data factory page, enter MyAzureSsisDataFactory for Name.

    “新建数据工厂”页

    ADF 的名称必须全局唯一。The name of your ADF must be globally unique. 如果收到错误,请更改数据工厂的名称(例如,yournameMyAzureSsisDataFactory),并重新尝试创建。If you receive the following error, change the name of your ADF (e.g. yournameMyAzureSsisDataFactory) and try creating it again. 有关 ADF 项目的命名规则,请参阅数据工厂 - 命名规则一文。See Data Factory - Naming Rules article to learn about naming rules for ADF artifacts.

    Data factory name MyAzureSsisDataFactory is not available

  4. 选择要在其下创建 ADF 的 Azure 订阅 。Select your Azure Subscription under which you want to create your ADF.

  5. 对于资源组,请执行以下步骤之一:For Resource Group, do one of the following steps:

    • 选择“使用现有资源组”,并从下拉列表选择现有的资源组。 Select Use existing, and select an existing resource group from the drop-down list.
    • 选择“新建”,并输入新资源组的名称。 Select Create new, and enter the name of your new resource group.

    若要了解有关资源组的详细信息,请参阅 使用资源组管理 Azure 资源一文。To learn about resource groups, see Using resource groups to manage your Azure resources article.

  6. 对于“版本”,选择“V2” 。For Version, select V2 .

  7. 对于“位置”,从下拉列表中选择 ADF 创建支持的其中一个位置 。For Location, select one of the locations supported for ADF creation from the drop-down list.

  8. 选择“固定到仪表板” 。Select Pin to dashboard.

  9. 单击创建Click Create.

  10. 在 Azure 仪表板上,你会看状态如下的以下磁贴:正在部署数据工厂On Azure dashboard, you will see the following tile with status: Deploying Data Factory.

    “正在部署数据工厂”磁贴

  11. 创建完成后,ADF 页面显示如下。After the creation is complete, you can see your ADF page as shown below.

    数据工厂主页

  12. 单击“创建者和监视器”,在单独的选项卡中启动 ADF UI/应用 。Click Author & Monitor to launch ADF UI/app in a separate tab.

创建管道Create your pipelines

  1. 在“开始使用”页中,选择“创建管道” 。In Let's get started page, select Create pipeline.

    “入门”页

  2. 在“活动”工具箱中,展开“常规”菜单,并将“Web”活动拖放到管道设计器图面 。In Activities toolbox, expand General menu, and drag & drop a Web activity onto the pipeline designer surface. 在活动属性窗口的“常规”选项卡中,将活动名称更改为“startMyIR” 。In General tab of the activity properties window, change the activity name to startMyIR. 切换到“设置”选项卡,然后执行以下操作 。Switch to Settings tab, and do the following actions.

    1. 对于“URL”,请为启动 Azure-SSIS IR 的 REST API 输入以下 URL,将 {subscriptionId}{resourceGroupName}{factoryName}{integrationRuntimeName} 替换为 IR 的实际值 :https://management.chinacloudapi.cn/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.DataFactory/factories/{factoryName}/integrationRuntimes/{integrationRuntimeName}/start?api-version=2018-06-01 或者,也可以从 ADF UI/应用上的监视页面复制并粘贴 IR 的资源 ID,替换上述 URL 的以下部分:/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.DataFactory/factories/{factoryName}/integrationRuntimes/{integrationRuntimeName}For URL, enter the following URL for REST API that starts Azure-SSIS IR, replacing {subscriptionId}, {resourceGroupName}, {factoryName}, and {integrationRuntimeName} with the actual values for your IR: https://management.chinacloudapi.cn/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.DataFactory/factories/{factoryName}/integrationRuntimes/{integrationRuntimeName}/start?api-version=2018-06-01 Alternatively, you can also copy & paste the resource ID of your IR from its monitoring page on ADF UI/app to replace the following part of the above URL: /subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.DataFactory/factories/{factoryName}/integrationRuntimes/{integrationRuntimeName}

      ADF SSIS IR 资源 ID

    2. 对于“方法”,请选择“POST”。 For Method, select POST.

    3. 对于“正文”,请输入 {"message":"Start my IR"}For Body, enter {"message":"Start my IR"}.

    4. 对于“身份验证” ,请选择 MSI 以使用 ADF 的托管标识,有关详细信息,请参阅数据工厂的托管标识一文。For Authentication, select MSI to use the managed identity for your ADF, see Managed identity for Data Factory article for more info.

    5. 对于“资源”,请输入 https://management.chinacloudapi.cn/For Resource, enter https://management.chinacloudapi.cn/.

      ADFWeb 活动计划 SSIS IR

  3. 克隆第一个管道以创建第二个管道,将活动名称更改为 stopMyIR 并替换以下属性 。Clone the first pipeline to create a second one, changing the activity name to stopMyIR and replacing the following properties.

    1. 对于“URL”,请为停止 Azure-SSIS IR 的 REST API 输入以下 URL,将 {subscriptionId}{resourceGroupName}{factoryName}{integrationRuntimeName} 替换为 IR 的实际值https://management.chinacloudapi.cn/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.DataFactory/factories/{factoryName}/integrationRuntimes/{integrationRuntimeName}/stop?api-version=2018-06-01For URL, enter the following URL for REST API that stops Azure-SSIS IR, replacing {subscriptionId}, {resourceGroupName}, {factoryName}, and {integrationRuntimeName} with the actual values for your IR: https://management.chinacloudapi.cn/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.DataFactory/factories/{factoryName}/integrationRuntimes/{integrationRuntimeName}/stop?api-version=2018-06-01

    2. 对于“正文”,请输入 {"message":"Stop my IR"}For Body, enter {"message":"Stop my IR"}.

  4. 创建第三个管道,将“执行 SSIS 包”活动从“活动”工具箱拖放到管道设计器图面,然后按照使用 ADF 中的执行 SSIS 包活动调用 SSIS 包一文中的说明配置 SSIS 包活动 。Create a third pipeline, drag & drop an Execute SSIS Package activity from Activities toolbox onto the pipeline designer surface, and configure it following the instructions in Invoke an SSIS package using Execute SSIS Package activity in ADF article. 或者,可以使用“存储过程”活动,并按照使用 ADF 中的存储过程活动调用 SSIS 包一文中的说明配置 SSIS 包活动 。Alternatively, you can use a Stored Procedure activity instead and configure it following the instructions in Invoke an SSIS package using Stored Procedure activity in ADF article. 接下来,在启动/停止 IR 的两个 Web 活动之间链接执行 SSIS 包/存储过程活动,类似于第一个/第二个管道中的 Web 活动。Next, chain the Execute SSIS Package/Stored Procedure activity between two Web activities that start/stop your IR, similar to those Web activities in the first/second pipelines.

    ADF Web 活动按需 SSIS IR

  5. 为 ADF 分配一个“参与者”角色的托管标识,因此其管道中的 Web 活动可以调用 REST API 来启动/停止在其中预配的 Azure-SSIS IR 。Assign the managed identity for your ADF a Contributor role to itself, so Web activities in its pipelines can call REST API to start/stop Azure-SSIS IRs provisioned in it. 在 Azure 门户的 ADF 页面上,单击“访问控制 (IAM)”,单击“+ 添加角色分配”,然后在“添加角色分配”边栏选项卡中,执行以下操作 。On your ADF page in Azure portal, click Access control (IAM), click + Add role assignment, and then on Add role assignment blade, do the following actions.

    1. 对于“角色” ,选择“参与者” 。For Role, select Contributor.
    2. 对于“分配访问权限至”,选择“Azure AD 用户、组或服务主体” 。For Assign access to, select Azure AD user, group, or service principal.
    3. 对于“选择”,搜索你的 ADF 名称并选择该 ADF 。For Select, search for your ADF name and select it.
    4. 单击“保存” 。Click Save.

    ADF 托管标识角色分配

  6. 单击“工厂/管道”工具栏上的“验证所有/验证”,验证 ADF 和所有管道设置 。Validate your ADF and all pipeline settings by clicking Validate all/Validate on the factory/pipeline toolbar. 单击 >> 按钮关闭“工厂/管道验证输出” 。Close Factory/Pipeline Validation Output by clicking >> button.

    验证管道

测试运行管道Test run your pipelines

  1. 在工具栏上为每个管道选择“测试运行”,然后在底部窗格中查看“输出”窗口 。Select Test Run on the toolbar for each pipeline and see Output window in the bottom pane.

    测试运行

  2. 若要测试第三个管道,请启动 SQL Server Management Studio (SSMS)。To test the third pipeline, launch SQL Server Management Studio (SSMS). 在“连接到服务器”窗口中执行以下操作 。In Connect to Server window, do the following actions.

    1. 对于“服务器名称”,请输入 <Azure SQL 数据库服务器名称>.database.chinacloudapi.cn 。For Server name, enter <your Azure SQL Database server name>.database.chinacloudapi.cn.
    2. 选择“选项Options >>”。 Select Options >>.
    3. 对于“连接到数据库”,请选择“SSISDB”。 For Connect to database, select SSISDB.
    4. 选择“连接” 。Select Connect.
    5. 展开“Integration Services 目录” -> “SSISDB”-> 你的文件夹 ->“项目”-> 你的 SSIS 项目 ->“包”。 Expand Integration Services Catalogs -> SSISDB -> Your folder -> Projects -> Your SSIS project -> Packages.
    6. 右键单击指定的 SSIS 包,运行并选择“报告” -> “标准报告” -> “所有执行” 。Right-click the specified SSIS package to run and select Reports -> Standard Reports -> All Executions.
    7. 验证是否已运行该包。Verify that it ran.

    验证 SSIS 包是否已运行

计划管道Schedule your pipelines

现在,管道按预期工作,可以创建触发器以按指定节奏运行管道。Now that your pipelines work as you expected, you can create triggers to run them at specified cadences. 有关如何将触发器与管道相关联的详细信息,请参阅按计划触发管道一文。For details about associating triggers with pipelines, see Trigger the pipeline on a schedule article.

  1. 在管道工具栏上,依次选择“触发器”、“新建/编辑” 。On the pipeline toolbar, select Trigger and select New/Edit.

    “触发器”->“新建/编辑”

  2. 在“添加触发器”窗格中,选择“+ 新建” 。In Add Triggers pane, select + New.

    “添加触发器”-“新建”

  3. 在“新建触发器”窗格中执行以下操作: In New Trigger pane, do the following actions:

    1. 对于“名称”,输入触发器的名称 。For Name, enter a name for the trigger. 在以下示例中,“每日运行”是触发器名称 。In the following example, Run daily is the trigger name.
    2. 对于“类型”,请选择“计划”。 For Type, select Schedule.
    3. 对于“开始日期 (UTC)”,请在 UTC 中输入开始日期和时间 。For Start Date (UTC), enter a start date and time in UTC.
    4. 对于“重复周期”,请输入触发器的频率 。For Recurrence, enter a cadence for the trigger. 在以下示例中,频率为每日一次 。In the following example, it is Daily once.
    5. 对于“结束”,请选择“不结束”或在选择“开始日期”后输入结束日期和时间 。For End, select No End or enter an end date and time after selecting On Date.
    6. 发布整个 ADF 设置后,选择“激活”以立即激活触发器 。Select Activated to activate the trigger immediately after you publish the whole ADF settings.
    7. 选择“下一步”。Select Next.

    “触发器”->“新建/编辑”

  4. 在“触发器运行参数”页中查看任何警告,然后选择“完成” 。In Trigger Run Parameters page, review any warning, and select Finish.

  5. 通过选择工厂工具栏中的“发布所有”,发布整个 ADF 设置 。Publish the whole ADF settings by selecting Publish All in the factory toolbar.

    全部发布

在 Azure 门户中监视管道和触发器Monitor your pipelines and triggers in Azure portal

  1. 若要监视触发器运行和管道运行,请使用 ADF UI/app 左侧的“监视”选项卡 。To monitor trigger runs and pipeline runs, use Monitor tab on the left of ADF UI/app. 有关详细步骤,请参阅监视管道一文。For detailed steps, see Monitor the pipeline article.

    管道运行

  2. 若要查看与管道运行关联的活动运行,请在“操作”列中选择第一个链接(“查看活动运行”) 。To view the activity runs associated with a pipeline run, select the first link (View Activity Runs) in Actions column. 对于第三个管道,可看到三个活动在运行,每一个活动均用于管道中的每个链式活动(用于启动 IR 的 Web 活动、用于执行包的存储过程活动以及用于停止 IR 的 Web 活动)。For the third pipeline, you will see three activity runs, one for each chained activity in the pipeline (Web activity to start your IR, Stored Procedure activity to execute your package, and Web activity to stop your IR). 若要再次查看管道,请选择顶部的“管道”链接 。To view the pipeline runs again, select Pipelines link at the top.

    活动运行

  3. 若要查看触发器运行,请从顶部“管道运行”下的下拉列表中选择“触发器运行” 。To view the trigger runs, select Trigger Runs from the drop-down list under Pipeline Runs at the top.

    触发器运行

使用 PowerShell 监视管道和触发器Monitor your pipelines and triggers with PowerShell

使用如下示例脚本来监视管道和触发器。Use scripts like the following examples to monitor your pipelines and triggers.

  1. 获取管道运行的状态。Get the status of a pipeline run.

    Get-AzDataFactoryV2PipelineRun -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -PipelineRunId $myPipelineRun
    
  2. 获取有关触发器的信息。Get info about a trigger.

    Get-AzDataFactoryV2Trigger -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -Name  "myTrigger"
    
  3. 获取触发器运行的状态。Get the status of a trigger run.

    Get-AzDataFactoryV2TriggerRun -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -TriggerName "myTrigger" -TriggerRunStartedAfter "2018-07-15" -TriggerRunStartedBefore "2018-07-16"
    

后续步骤Next steps

参阅 SSIS 文档中的以下文章:See the following articles from SSIS documentation: