使用存储过程活动运行 SSIS 包

项目
12/23/2024

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

本文介绍如何使用存储过程活动在 Azure 数据工厂管道或 Synapse Pipelines 中运行 SSIS 包。

先决条件

Azure SQL 数据库

本文中的演练使用 Azure SQL 数据库来托管 SSIS 目录。还可使用 Azure SQL 托管实例。

数据工厂

需要一个 Azure 数据工厂实例来实现此演练。如果尚未预配数据工厂，可以按照下文中的步骤进行操作：快速入门：使用 Azure 门户和 Azure 数据工厂工作室创建数据工厂。

Azure-SSIS 集成运行时

最后，如果还没有 Azure-SSIS 集成运行时，还请按照教程：部署 SSIS 包中的分步说明创建一个。

使用存储过程活动创建管道

在此步骤中，将使用数据工厂 UI 创建管道。如果尚未导航到 Azure 数据工厂工作室，请在 Azure 门户中打开数据工厂，然后单击“打开 Azure 数据工厂工作室”按钮将其打开。

Azure 数据工厂主页的屏幕截图。

接下来，向新建管道中添加存储过程活动，并将其配置为使用 sp_executesql 存储过程运行 SSIS 包。

在主页中，单击“协调”：
在“活动”工具箱中，搜索“存储过程”，然后将一个存储过程活动拖放到管道设计器图面。
选择刚刚添加到设计器图面的存储过程活动，然后选择“设置”选项卡，单击“链接服务”旁边的“+ 新建”。与托管 SSIS 目录（SSIDB 数据库）的 Azure SQL 数据库中的数据库建立连接。
在“新建链接服务”窗口中执行以下步骤：
1. 选择“Azure SQL 数据库”作为“类型”。
2. 选择默认的 AutoResolveIntegrationRuntime 以连接到托管 SSISDB 数据库的 Azure SQL 数据库。
3. 在“服务器名称”字段中，选择托管 SSISDB 数据库的 Azure SQL 数据库。
4. 选择“SSISDB”作为“数据库名称”。
5. 对于“用户名”，输入有权访问数据库的用户的名称。
6. 对于“密码”，输入该用户的密码。
7. 单击“测试连接”按钮，测试与数据库之间的连接。
8. 单击“保存”按钮保存链接服务。

返回“设置”选项卡的“属性”窗口，完成以下步骤：

选择“编辑”。
对于“存储过程名称”字段，输入 sp_executesql。
在“存储过程参数”部分中单击“+ 新建”。
对于参数的“名称”，输入“stmt”。
输入“字符串”作为参数类型。

输入以下 SQL 查询作为参数的值：

在 SQL 查询中，指定 folder_name、project_name 和 package_name 参数的右侧值。

DECLARE @return_value INT, @exe_id BIGINT, @err_msg NVARCHAR(150)    EXEC @return_value=[SSISDB].[catalog].[create_execution] @folder_name=N'<FOLDER name in SSIS Catalog>', @project_name=N'<PROJECT name in SSIS Catalog>', @package_name=N'<PACKAGE name>.dtsx', @use32bitruntime=0, @runinscaleout=1, @useanyworker=1, @execution_id=@exe_id OUTPUT    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @exe_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1    EXEC [SSISDB].[catalog].[start_execution] @execution_id=@exe_id, @retry_count=0    IF(SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id=@exe_id)<>7 BEGIN SET @err_msg=N'Your package execution did not succeed for execution ID: ' + CAST(@exe_id AS NVARCHAR(20)) RAISERROR(@err_msg,15,1) END

Azure SQL 数据库链接服务

若要验证管道配置，请单击工具栏中的“验证”。若要关闭“管道验证报告”，请单击 >>。
单击“全部发布”按钮将管道发布到数据工厂。

运行和监视管道

在此部分中，将触发管道运行，然后对其进行监视。

若要触发某个管道运行，请在工具栏中单击“触发器”，然后单击“立即触发”。
在“管道运行”窗口中选择“完成”。
在左侧切换到“监视”选项卡。随即显示管道运行及其状态以及其他信息（比如运行开始时间）。若要刷新视图，请单击“刷新”。
在“操作”列中单击“查看活动运行”链接。此时只显示一个活动运行，因为管道只有一个活动（存储过程活动）。
可在 SQL 数据库中针对 SSISDB 数据库运行以下查询，验证是否执行了该包。
```
select * from catalog.executions
```

注意

也可以为管道创建一个计划触发器，以便按计划（每小时、每天等）运行管道。有关示例，请参阅创建数据工厂 - 数据工厂 UI。

Azure PowerShell

注意

建议使用 Azure Az PowerShell 模块与 Azure 交互。请参阅安装 Azure PowerShell 以开始使用。若要了解如何迁移到 Az PowerShell 模块，请参阅将 Azure PowerShell 从 AzureRM 迁移到 Az。

在此部分中，将使用 Azure PowerShell 创建数据工厂管道，管道中包含可调用 SSIS 包的存储过程活动。

按如何安装和配置 Azure PowerShell 中的说明安装最新的 Azure PowerShell 模块。

创建数据工厂

可使用具有 Azure-SSIS IR 的相同数据工厂，也可以创建单独的数据工厂。下列过程提供创建数据工厂的步骤。可在数据工厂中使用存储过程活动创建管道。存储过程活动在 SSISDB 数据库中执行存储过程，运行 SSIS 包。

为资源组名称定义一个变量，稍后会在 PowerShell 命令中使用该变量。将以下命令文本复制到 PowerShell，在双引号中指定 Azure 资源组的名称，然后运行命令。例如："adfrg"。
```
$resourceGroupName = "ADFTutorialResourceGroup";
```
如果该资源组已存在，请勿覆盖它。为 $ResourceGroupName 变量分配另一个值，然后再次运行命令
若要创建 Azure 资源组，请运行以下命令：
```
$ResGrp = New-AzResourceGroup $resourceGroupName -location 'chinaeast2'
```
如果该资源组已存在，请勿覆盖它。为 $ResourceGroupName 变量分配另一个值，然后再次运行命令。
定义一个用于数据工厂名称的变量。

重要

更新数据工厂名称，使之全局唯一。
```
$DataFactoryName = "ADFTutorialFactory";
```
若要创建数据工厂，请运行下面的 Set-AzDataFactoryV2 cmdlet，使用 $ResGrp 变量中的 Location 和 ResourceGroupName 属性：
```
$DataFactory = Set-AzDataFactoryV2 -ResourceGroupName $ResGrp.ResourceGroupName -Location $ResGrp.Location -Name $dataFactoryName 
```

请注意以下几点：

Azure 数据工厂的名称必须全局唯一。如果收到以下错误，请更改名称并重试。

The specified Data Factory name 'ADFv2QuickStartDataFactory' is already in use. Data Factory names must be globally unique.

若要创建数据工厂实例，用于登录到 Azure 的用户帐户必须属于参与者或所有者角色，或者是 Azure 订阅的管理员。
若要查看目前提供数据工厂的 Azure 区域的列表，请在以下页面上选择感兴趣的区域，然后展开“分析”以找到“数据工厂”：可用产品(按区域)。数据工厂使用的数据存储（Azure 存储、Azure SQL 数据库，等等）和计算资源（HDInsight 等）可以位于其他区域中。

创建 Azure SQL 数据库链接服务

创建一个链接服务，将托管 SSIS 目录的数据库链接到数据工厂。数据工厂使用此链接服务中的信息连接到 SSISDB 数据库，并执行存储过程来运行 SSIS 包。

在 C:\ADF\RunSSISPackage 文件夹中创建一个名为 AzureSqlDatabaseLinkedService.json 的 JSON 文件，并在其中包含以下内容：

重要

保存文件之前，请将 <servername>、<username> 和 <password> 替换为 Azure SQL 数据库的值。

{
    "name": "AzureSqlDatabaseLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Server=tcp:<servername>.database.chinacloudapi.cn,1433;Database=SSISDB;User ID=<username>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
        }
    }
}

在 Azure PowerShell 中，切换到 C:\ADF\RunSSISPackage 文件夹。

运行 Set-AzDataFactoryV2LinkedService cmdlet 来创建链接服务：AzureSqlDatabaseLinkedService。

Set-AzDataFactoryV2LinkedService -DataFactoryName $DataFactory.DataFactoryName -ResourceGroupName $ResGrp.ResourceGroupName -Name "AzureSqlDatabaseLinkedService" -File ".\AzureSqlDatabaseLinkedService.json"

使用存储过程活动创建管道

在此步骤中，使用存储过程活动创建管道。该活动调用 sp_executesql 存储过程来运行 SSIS 包。

在 C:\ADF\RunSSISPackage 文件夹中创建一个名为 RunSSISPackagePipeline.json 的 JSON 文件，并在其中包含以下内容：

重要

保存文件之前，请将 <FOLDER NAME>、<PROJECT NAME> 和 <PACKAGE NAME> 替换为 SSIS 目录中文件夹、项目和包的名称。

{
    "name": "RunSSISPackagePipeline",
    "properties": {
        "activities": [
            {
                "name": "My SProc Activity",
                "description":"Runs an SSIS package",
                "type": "SqlServerStoredProcedure",
                "linkedServiceName": {
                    "referenceName": "AzureSqlDatabaseLinkedService",
                    "type": "LinkedServiceReference"
                },
                "typeProperties": {
                    "storedProcedureName": "sp_executesql",
                    "storedProcedureParameters": {
                        "stmt": {
                            "value": "DECLARE @return_value INT, @exe_id BIGINT, @err_msg NVARCHAR(150)    EXEC @return_value=[SSISDB].[catalog].[create_execution] @folder_name=N'<FOLDER NAME>', @project_name=N'<PROJECT NAME>', @package_name=N'<PACKAGE NAME>', @use32bitruntime=0, @runinscaleout=1, @useanyworker=1, @execution_id=@exe_id OUTPUT    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @exe_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1    EXEC [SSISDB].[catalog].[start_execution] @execution_id=@exe_id, @retry_count=0    IF(SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id=@exe_id)<>7 BEGIN SET @err_msg=N'Your package execution did not succeed for execution ID: ' + CAST(@exe_id AS NVARCHAR(20)) RAISERROR(@err_msg,15,1) END"
                        }
                    }
                }
            }
        ]
    }
}

若要创建管道 RunSSISPackagePipeline，请运行 Set-AzDataFactoryV2Pipeline cmdlet。

$DFPipeLine = Set-AzDataFactoryV2Pipeline -DataFactoryName $DataFactory.DataFactoryName -ResourceGroupName $ResGrp.ResourceGroupName -Name "RunSSISPackagePipeline" -DefinitionFile ".\RunSSISPackagePipeline.json"

下面是示例输出：

PipelineName      : Adfv2QuickStartPipeline
ResourceGroupName : <resourceGroupName>
DataFactoryName   : <dataFactoryName>
Activities        : {CopyFromBlobToBlob}
Parameters        : {[inputPath, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification], [outputPath, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification]}

创建管道运行

使用 Invoke-AzDataFactoryV2Pipeline cmdlet 运行该管道。此 cmdlet 返回管道运行 ID，用于将来的监视。

$RunId = Invoke-AzDataFactoryV2Pipeline -DataFactoryName $DataFactory.DataFactoryName -ResourceGroupName $ResGrp.ResourceGroupName -PipelineName $DFPipeLine.Name

监视管道运行

运行以下 PowerShell 脚本，持续检查管道运行状态，直到完成数据复制为止。在 PowerShell 窗口中复制/粘贴以下脚本，然后按 ENTER。

while ($True) {
    $Run = Get-AzDataFactoryV2PipelineRun -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -PipelineRunId $RunId

    if ($Run) {
        if ($run.Status -ne 'InProgress') {
            Write-Output ("Pipeline run finished. The status is: " +  $Run.Status)
            $Run
            break
        }
        Write-Output  "Pipeline is running...status: InProgress"
    }

    Start-Sleep -Seconds 10
}

创建触发器

在上一步中，可按需调用管道。还可创建一个计划触发器，按计划（按小时、每天等）运行管道。

在 C:\ADF\RunSSISPackage 文件夹中创建一个名为 MyTrigger.json 的 JSON 文件，并在其中包含以下内容：

{
    "properties": {
        "name": "MyTrigger",
        "type": "ScheduleTrigger",
        "typeProperties": {
            "recurrence": {
                "frequency": "Hour",
                "interval": 1,
                "startTime": "2017-12-07T00:00:00-08:00",
                "endTime": "2017-12-08T00:00:00-08:00"
            }
        },
        "pipelines": [{
                "pipelineReference": {
                    "type": "PipelineReference",
                    "referenceName": "RunSSISPackagePipeline"
                },
                "parameters": {}
            }
        ]
    }
}

在 Azure PowerShell 中，切换到 C:\ADF\RunSSISPackage 文件夹。

运行 Set-AzDataFactoryV2Trigger cmdlet，以创建触发器。

Set-AzDataFactoryV2Trigger -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -Name "MyTrigger" -DefinitionFile ".\MyTrigger.json"

默认情况下，触发器处于停止状态。运行 Start-AzDataFactoryV2Trigger cmdlet 以启动该触发器。

Start-AzDataFactoryV2Trigger -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -Name "MyTrigger"

通过运行 Get-AzDataFactoryV2Trigger cmdlet 确认该触发器已启动。

Get-AzDataFactoryV2Trigger -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -Name "MyTrigger"

在下一小时后运行以下命令。例如，如果当前时间为下午 3:25 (UTC)，则在下午 4:00 (UTC) 运行该命令。

Get-AzDataFactoryV2TriggerRun -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -TriggerName "MyTrigger" -TriggerRunStartedAfter "2017-12-06" -TriggerRunStartedBefore "2017-12-09"

可在 SQL 数据库中针对 SSISDB 数据库运行以下查询，验证是否执行了该包。

select * from catalog.executions

还可使用 Azure 门户监视管道。有关分步说明，请参阅监视管道。

通过

使用存储过程活动运行 SSIS 包

先决条件

Azure SQL 数据库

数据工厂

Azure-SSIS 集成运行时

使用存储过程活动创建管道

运行和监视管道

Azure PowerShell

创建数据工厂

创建 Azure SQL 数据库链接服务

使用存储过程活动创建管道

创建管道运行

监视管道运行

创建触发器

相关内容

其他资源