使用預存程序活動執行 SSIS 套件

發行項
04/27/2024

適用於：Azure Data Factory Azure Synapse Analytics

提示

試用 Microsoft Fabric 中的 Data Factory，這是適用於企業的全方位分析解決方案。 Microsoft Fabric 涵蓋從資料移動到資料科學、即時分析、商業智慧和報告的所有項目。了解如何免費開始新的試用！

本文描述如何使用預存程序活動，在 Azure Data Factory 管線 Synapse Pipelines 執行 SSIS 套件。

必要條件

Azure SQL Database

這篇文章中將逐步解說使用 Azure SQL Database 來裝載 SSIS 目錄。您也可以使用 Azure SQL 受控執行個體。

Data Factory

您將需要 Azure Data Factory 的執行個體，才能實作此逐步說明。如果您尚未進行佈建，可以遵循快速入門：使用 Azure 入口網站和 Azure Data Factory Studio 建立資料處理站中的步驟。

Azure-SSIS 整合執行階段

最後，如果您未依照教學課程：部署 SSIS 套件中的逐步指示進行，還會需要 Azure SSIS 整合執行階段。

使用預存程序活動建立管線

在此步驟中，您可以使用資料處理站 UI 建立管線。如果您尚未流覽到 Azure Data Factory Studio，請在 Azure 入口網站中開啟資料處理站，然後按一下 [開啟 Azure Data Factory Studio] 按鈕加以開啟。

Screenshot of the Azure Data Factory home page.

接下來，您要將預存程序活動新增到管線，並將新管線設定為使用 sp_executesql 預存程序執行 SSIS 套件。

在首頁中，按一下 [協調]：
在 [活動] 工具箱中，搜尋 [預存程序]，並將 [預存程序] 活動拖放到管線設計工具表面。
選取剛才新增到設計工具介面的 [預存程序] 活動，然後到 [設定] 索引標籤中按一下 [連結服務] 旁的 [+ 新增]。您可對於裝載 SSIS 目錄 (SSIDB 資料庫) 的 Azure SQL Database 建立連線。
在 [新增連結服務] 視窗中，執行下列步驟：
1. 選取 [類型] 的 [Azure SQL Database]。
2. 選取預設 AutoResolveIntegrationRuntime，以連線到裝載 SSISDB 資料庫的 Azure SQL Database。
3. 針對 [伺服器名稱] 欄位，選取裝載 SSISDB 資料庫的 Azure SQL Database。
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 Database linked service

若要驗證管線設定，按一下工具列上的 [驗證]。若要關閉 [管線驗證報告]，請按一下 >>。
按一下 [全部發行] 按鈕，將管線發行至資料處理站。

執行並監視管線

在本節中，您會觸發管線執行，然後監視執行的情況。

若要觸發管線執行，按一下工具列上的 [觸發程序]，然後按一下 [立即觸發]。
在 [管線執行] 視窗中，選取 [完成]。
切換至左側的 [監視] 索引標籤。您會看到管線執行、其狀態，以及其他資訊 (例如執行開始時間)。若要重新整理檢視，按一下 [重新整理]。
按一下 [動作]資料行中的 [檢視活動執行] 連結。管線只有一個活動 (預存程序活動) 時，您只會看到一個活動執行。
您可以依據 SQL Database 中的 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 'eastus'
```
如果資源群組已經存在，您可能不想覆寫它。將不同的值指派給 $ResourceGroupName 變數，然後執行一次命令。
定義 Data Factory 名稱的變數。

重要

將資料處理站名稱更新為全域唯一的。
```
$DataFactoryName = "ADFTutorialFactory";
```
若要建立 Data Factory，請從 $ResGrp 變數使用 Location 和 ResourceGroupName 屬性來執行下列 Set-AzDataFactoryV2 Cmdlet：
```
$DataFactory = Set-AzDataFactoryV2 -ResourceGroupName $ResGrp.ResourceGroupName -Location $ResGrp.Location -Name $dataFactoryName 
```

請注意下列幾點：

Azure Data Factory 的名稱在全域必須是唯一的。如果發生下列錯誤，請變更名稱，並再試一次。

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

若要建立 Data Factory 執行個體，您用來登入 Azure 的使用者帳戶必須為參與者或擁有者角色，或是 Azure 訂用帳戶的管理員。
如需目前可使用 Data Factory 的 Azure 區域清單，請在下列頁面上選取您感興趣的區域，然後展開 [分析] 以找出 [Data Factory]：依區域提供的產品。資料處理站所使用的資料存放區 (Azure 儲存體、Azure SQL Database 等) 和計算 (HDInsight 等) 可位於其他區域。

建立 Azure SQL Database 連結服務

建立連結服務，將裝載 SSIS 目錄的資料庫連結到資料處理站。資料處理站使用此連結服務中的資訊連線到 SSISDB 資料庫，並執行預存程序來執行 SSIS 套件。

使用下列內容，在 C:\ADF\RunSSISPackage 資料夾中建立名為 AzureSqlDatabaseLinkedService.json 的 JSON 檔案：

重要

儲存檔案之前，以您的 Azure SQL Database 的值取代 <servername>、<username> 和 <password>。

{
    "name": "AzureSqlDatabaseLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Server=tcp:<servername>.database.windows.net,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 檔案：

重要

以 SSIS 目錄中的資料夾名稱、專案名稱和封裝名稱取代 <FOLDER NAME>、<PROJECT NAME>、<PACKAGE NAME>，再儲存檔案。

{
    "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 會傳回管線執行識別碼，方便後續監視。

$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"

在下一個小時後，執行下列命令。例如，如果目前的時間是 UTC 下午 3:25，請在 UTC 下午 4:00 執行命令。

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

您可以依據 SQL Database 中的 SSISDB 資料庫執行下列查詢，來確認套件已執行。

select * from catalog.executions

您也可以使用 Azure 入口網站監視管線。如需逐步指示，請參閱監視管線：

使用預存程序活動執行 SSIS 套件

必要條件

Azure SQL Database

Data Factory

Azure-SSIS 整合執行階段

使用預存程序活動建立管線

執行並監視管線

Azure PowerShell

建立資料處理站

建立 Azure SQL Database 連結服務

使用預存程序活動建立管線

建立管線執行

監視管道執行

建立觸發程序

相關內容

其他資源