Ausführen eines SSIS-Pakets mit der Aktivität „Gespeicherte Prozedur“

Artikel
07/20/2023

GILT FÜR: Azure Data Factory Azure Synapse Analytics

Tipp

Testen Sie Data Factory in Microsoft Fabric, eine All-in-One-Analyselösung für Unternehmen. Microsoft Fabric deckt alle Aufgaben ab, von der Datenverschiebung bis hin zu Data Science, Echtzeitanalysen, Business Intelligence und Berichterstellung. Erfahren Sie, wie Sie kostenlos eine neue Testversion starten!

In diesem Artikel wird das Ausführen eines SSIS-Pakets in einer Azure Data Factory-Pipeline oder in Synapse-Pipelines mithilfe der Aktivität einer gespeicherten Prozedur beschrieben.

Voraussetzungen

Azure SQL-Datenbank

Die exemplarische Vorgehensweise in diesem Artikel verwendet Azure SQL-Datenbank zum Hosten des SSIS-Katalogs. Alternativ können Sie eine Azure SQL Managed Instance verwenden.

Data Factory

Sie benötigen eine Instanz von Azure Data Factory, um diese exemplarische Vorgehensweise zu implementieren. Wenn Sie noch keine bereitgestellt haben, können Sie die Schritte in Schnellstart: Erstellen einer Data Factory mithilfe des Microsoft Azure-Portals und Azure Data Factory Studio ausführen.

Azure SSIS-Integrationslaufzeit

Schließlich benötigen Sie auch eine Azure-SSIS-Integrationslaufzeit, falls Sie keine haben, indem Sie die Schritt-für-Schritt-Anleitung im Tutorial: Bereitstellen von SSIS-Paketen befolgen.

Erstellen einer Pipeline mit einer Aktivität einer gespeicherten Prozedur

In diesem Schritt erstellen Sie über die Data Factory-Benutzeroberfläche eine Pipeline. Wenn Sie noch nicht zu Azure Data Factory Studio navigiert sind, öffnen Sie Ihre Data Factory im Azure-Portal und klicken Sie auf die Schaltfläche Open Azure Data Factory Studio, um sie zu öffnen.

Screenshot of the Azure Data Factory home page.

Als Nächstes fügen Sie einer neuen Pipeline eine gespeicherte Prozeduraktivität hinzu und konfigurieren sie so, dass sie das SSIS-Paket mithilfe der gespeicherten Prozedur sp_executesql ausführt.

Klicken Sie auf der Startseite auf Orchestrieren:
Suchen Sie in der Toolbox Aktivitäten nach Gespeicherte Prozedur und ziehen Sie eine Aktivität Gespeicherte Prozedur auf die Oberfläche des Pipeline-Designers.
Wählen Sie die gespeicherte Prozeduraktivität aus, die Sie gerade der Designeroberfläche hinzugefügt haben, und dann die Registerkarte Einstellungen, und klicken Sie neben dem verknüpften Dienst auf + Neu. Sie erstellen eine Verbindung mit der Datenbank in Azure SQL-Datenbank, die als Host für den SSIS-Katalog (SSIDB-Datenbank) fungiert.
Führen Sie im Fenster New Linked Service (Neuer verknüpfter Dienst) die folgenden Schritte aus:
1. Wählen Sie Azure SQL-Datenbank als Typ aus.
2. Wählen Sie die Standard AutoResolveIntegrationRuntime aus, um eine Verbindung mit der Azure SQL-Datenbank herzustellen, die die SSISDBDatenbank hostet.
3. Wählen Sie für das Feld Servername die Azure SQL-Datenbank aus, die die SSISDB-Datenbank hostet.
4. Wählen Sie SSISDB als Datenbankname aus.
5. Geben Sie unter Benutzername den Namen des Benutzers ein, der Zugriff auf die Datenbank hat.
6. Geben Sie unter Kennwort das Kennwort des Benutzers ein.
7. Testen Sie die Verbindung mit der Datenbank, indem Sie auf die Schaltfläche Verbindung testen klicken.
8. Speichern Sie den verknüpften Dienst, indem Sie auf die Schaltfläche Speichern klicken.

Führen Sie im Eigenschaftenfenster auf der Registerkarte Einstellungen die folgenden Schritte aus:

Wählen Sie Bearbeiten aus.
Geben Sie sp_executesql in das Feld Name der gespeicherten Prozedur ein.
Klicken Sie im Abschnitt Parameter der gespeicherten Prozedur auf + Neu.
Geben Sie unter Name des Parameters stmt ein.
Geben Sie unter Typ des Parameters Zeichenfolge ein.

Geben Sie unter Wert des Parameters die folgende SQL-Abfrage ein:

Geben Sie in der SQL-Abfrage die entsprechenden Werte für die Parameter folder_name, project_name und package_name ein.

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

Klicken Sie zum Überprüfen der Pipelinekonfiguration in der Symbolleiste auf Überprüfen. Klicken Sie zum Schließen des Pipelineüberprüfungsberichts (Pipeline Validation Report) auf >>.
Veröffentlichen Sie die Pipeline in der Data Factory, indem Sie auf die Schaltfläche Alle veröffentlichen klicken.

Ausführen und Überwachen der Pipeline

In diesem Abschnitt lösen Sie eine Pipelineausführung aus, und überwachen diese.

Klicken Sie auf der Symbolleiste auf Trigger und anschließend auf Jetzt auslösen, um eine Pipelineausführung auszulösen.
Wählen Sie im Fenster Pipelineausführung die Option Fertig stellen aus.
Wechseln Sie im linken Bereich zur Registerkarte Überwachen. Es werden die Pipelineausführung, der zugehörige Status sowie weitere Informationen (z.B. Startzeit der Ausführung) angezeigt. Klicken Sie zum Aktualisieren der Ansicht auf Aktualisieren.
Klicken Sie in der Spalte Aktionen auf den Link Aktivitätsausführungen anzeigen. Es wird nur eine Aktivitätsausführung angezeigt, da die Pipeline nur eine Aktivität (Aktivität „Gespeicherte Prozedur“) enthält.
Sie können die folgende Abfrage für die SSISDB-Datenbank in SQL-Datenbank ausführen, um zu überprüfen, ob das Paket ausgeführt wurde.
```
select * from catalog.executions
```

Hinweis

Sie können auch einen geplanten Trigger für die Pipeline erstellen, damit die Pipeline basierend auf einem Zeitplan (stündlich, täglich usw.) ausgeführt wird. Ein Beispiel finden Sie unter Erstellen einer Data Factory über die Azure Data Factory-Benutzeroberfläche.

Azure PowerShell

Hinweis

Es wird empfohlen, das Azure Az PowerShell-Modul für die Interaktion mit Azure zu verwenden. Informationen zu den ersten Schritten finden Sie unter Installieren des Azure Az PowerShell-Moduls. Informationen zum Migrieren zum Az PowerShell-Modul finden Sie unter Migrieren von Azure PowerShell von AzureRM zum Az-Modul.

In diesem Abschnitt erstellen Sie mithilfe von Azure PowerShell eine Data Factory-Pipeline mit der Aktivität einer gespeicherten Prozedur, die ein SSIS-Paket aufruft.

Installieren Sie die aktuellen Azure PowerShell-Module, indem Sie die Anweisungen unter Installieren und Konfigurieren von Azure PowerShell befolgen.

Erstellen einer Data Factory

Sie können wahlweise die gleiche Data Factory verwenden, die die Azure-SSIS IR aufweist, oder eine separate Data Factory erstellen. Das folgende Verfahren beschreibt die Schritte zum Erstellen einer Data Factory. In dieser Data Factory erstellen Sie eine Pipeline mit einer Aktivität einer gespeicherten Prozedur. Die Aktivität der gespeicherten Prozedur führt eine gespeicherte Prozedur in der SSISDB-Datenbank aus, um Ihr SSIS-Paket auszuführen.

Definieren Sie eine Variable für den Ressourcengruppennamen zur späteren Verwendung in PowerShell-Befehlen. Kopieren Sie den folgenden Befehlstext nach PowerShell, geben Sie einen Namen für die Azure-Ressourcengruppe in doppelten Anführungszeichen an, und führen Sie dann den Befehl aus. Beispiel: "adfrg".
```
$resourceGroupName = "ADFTutorialResourceGroup";
```
Beachten Sie, dass die Ressourcengruppe ggf. nicht überschrieben werden soll, falls sie bereits vorhanden ist. Weisen Sie der Variablen $ResourceGroupName einen anderen Wert zu, und führen Sie den Befehl erneut aus.
Führen Sie den folgenden Befehl aus, um die Azure-Ressourcengruppe zu erstellen:
```
$ResGrp = New-AzResourceGroup $resourceGroupName -location 'eastus'
```
Beachten Sie, dass die Ressourcengruppe ggf. nicht überschrieben werden soll, falls sie bereits vorhanden ist. Weisen Sie der Variablen $ResourceGroupName einen anderen Wert zu, und führen Sie den Befehl erneut aus.
Definieren Sie eine Variable für den Namen der Data Factory.

Wichtig

Aktualisieren Sie den Data Factory-Namen, damit er global eindeutig ist.
```
$DataFactoryName = "ADFTutorialFactory";
```
Führen Sie zum Erstellen der Data Factory das folgende Cmdlet vom Typ Set-AzDataFactoryV2 aus. Verwenden Sie dabei den Standort und die Eigenschaft „ResourceGroupName“ aus der Variablen „$ResGrp“:
```
$DataFactory = Set-AzDataFactoryV2 -ResourceGroupName $ResGrp.ResourceGroupName -Location $ResGrp.Location -Name $dataFactoryName 
```

Beachten Sie folgende Punkte:

Der Name der Azure Data Factory muss global eindeutig sein. Wenn die folgende Fehlermeldung angezeigt wird, ändern Sie den Namen, und wiederholen Sie den Vorgang.
```
The specified Data Factory name 'ADFv2QuickStartDataFactory' is already in use. Data Factory names must be globally unique.
```
Damit Sie Data Factory-Instanzen erstellen können, muss das Benutzerkonto, mit dem Sie sich bei Azure anmelden, ein Mitglied der Rolle Mitwirkender oder Besitzer oder ein Administrator des Azure-Abonnements sein.
Eine Liste der Azure-Regionen, in denen Data Factory derzeit verfügbar ist, finden Sie, indem Sie die für Sie interessanten Regionen auf der folgenden Seite auswählen und dann Analysen erweitern, um Data Factory zu finden: Verfügbare Produkte nach Region. Die von der Data Factory verwendeten Datenspeicher (Azure Storage, Azure SQL-Datenbank usw.) und Computedienste (HDInsight usw.) können sich in anderen Regionen befinden.

Erstellen eines verknüpften Azure SQL-Datenbank-Diensts

In diesem Schritt erstellen Sie einen verknüpften Dienst, um Ihre Datenbank, die als Host für den SSIS-Katalog fungiert, mit Ihrer Data Factory zu verknüpfen. Die Data Factory verwendet Informationen in diesem verknüpften Dienst zum Herstellen der Verbindung mit der SSISDB-Datenbank und führt eine gespeicherte Prozedur zum Ausführen eines SSIS-Pakets aus.

Erstellen Sie im Ordner C:\ADF\RUNSSISPackage eine JSON-Datei mit dem Namen AzureSQLDatabaseLinkedService.json und folgendem Inhalt:

Wichtig

Ersetzen Sie <servername>, <username> und <password> durch Werte Ihrer Azure SQL-Datenbank. Speichern Sie anschließend die Datei.

{
    "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"
        }
    }
}

Wechseln Sie in Azure PowerShell zum Ordner C:\ADF\RunSSISPackage.

Führen Sie das Cmdlet Set-AzDataFactoryV2LinkedService aus, um den verknüpften Dienst zu erstellen: AzureSqlDatabaseLinkedService.

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

Erstellen einer Pipeline mit einer Aktivität einer gespeicherten Prozedur

In diesem Schritt erstellen Sie eine Pipeline mit einer Aktivität einer gespeicherten Prozedur. Die Aktivität ruft die gespeicherte Prozedur „sp_executesql“ auf, um Ihr SSIS-Paket auszuführen.

Erstellen Sie im Ordner C:\ADF\RunSSISPackage eine JSON-Datei mit dem Namen RunSSISPackagePipeline.json und folgendem Inhalt:

Wichtig

Ersetzen Sie <FOLDER NAME>, <PROJECT NAME>, <PACKAGE NAME> durch die Namen von Ordner, Projekt und Paket im SSIS-Katalog, und speichern Sie dann die Datei.

{
    "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"
                        }
                    }
                }
            }
        ]
    }
}

Führen Sie zum Erstellen der Pipeline RunSSISPackagePipeline das Cmdlet Set-AzDataFactoryV2Pipeline aus.

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

Hier ist die Beispielausgabe:

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

Erstellen einer Pipelineausführung

Verwenden Sie das Cmdlet Invoke-AzDataFactoryV2Pipeline, um die Pipeline auszuführen. Das Cmdlet gibt die ID der Pipelineausführung für die zukünftige Überwachung zurück.

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

Überwachen der Pipelineausführung

Führen Sie das folgende PowerShell-Skript aus, um den Status der Pipelineausführung kontinuierlich zu überwachen, bis das Kopieren der Daten beendet ist. Kopieren Sie das folgende Skript, fügen Sie es in das PowerShell-Fenster ein, und drücken Sie die EINGABETASTE.

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
}

Erstellen eines Triggers

Im vorherigen Schritt wurde die Pipeline auf Anforderung aufgerufen. Alternativ können Sie einen Zeitplantrigger erstellen, um die Pipeline nach Zeitplan (stündlich, täglich usw.) auszuführen.

Erstellen Sie im Ordner C:\ADF\RunSSISPackage eine JSON-Datei mit dem Namen MyTrigger.json und dem folgenden Inhalt:

{
    "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": {}
            }
        ]
    }
}

Wechseln Sie in Azure PowerShell zum Ordner C:\ADF\RunSSISPackage.

Führen Sie das Cmdlet Set-AzDataFactoryV2Trigger aus, um den Trigger zu erstellen.

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

Standardmäßig befindet sich der Trigger im beendeten Zustand. Starten Sie den Trigger durch Ausführen des Cmdlets Start-AzDataFactoryV2Trigger.
```
Start-AzDataFactoryV2Trigger -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -Name "MyTrigger" 
```
Bestätigen Sie, dass der Trigger sich im gestarteten Zustand befindet, indem Sie das Cmdlet Get-AzDataFactoryV2Trigger ausführen.
```
Get-AzDataFactoryV2Trigger -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -Name "MyTrigger"     
```
Führen Sie nach der nächsten vollen Stunde den folgenden Befehl aus. Wenn die aktuelle Uhrzeit z.B. 15:25 UTC ist, führen Sie den Befehl um 16:00 UTC aus.
```
Get-AzDataFactoryV2TriggerRun -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -TriggerName "MyTrigger" -TriggerRunStartedAfter "2017-12-06" -TriggerRunStartedBefore "2017-12-09"
```
Sie können die folgende Abfrage für die SSISDB-Datenbank in SQL-Datenbank ausführen, um zu überprüfen, ob das Paket ausgeführt wurde.
```
select * from catalog.executions
```

Sie können die Pipeline auch mithilfe des Azure-Portals überwachen. Schritt-für-Schritt-Anweisungen finden Sie unter Überwachen der Pipeline.

Ausführen eines SSIS-Pakets mit der Aktivität „Gespeicherte Prozedur“

Voraussetzungen

Azure SQL-Datenbank

Data Factory

Azure SSIS-Integrationslaufzeit

Erstellen einer Pipeline mit einer Aktivität einer gespeicherten Prozedur

Ausführen und Überwachen der Pipeline

Azure PowerShell

Erstellen einer Data Factory

Erstellen eines verknüpften Azure SQL-Datenbank-Diensts

Erstellen einer Pipeline mit einer Aktivität einer gespeicherten Prozedur

Erstellen einer Pipelineausführung

Überwachen der Pipelineausführung

Erstellen eines Triggers

Zugehöriger Inhalt

Zusätzliche Ressourcen