Exécuter un package SSIS avec l’activité Procédure stockée

Article
07/20/2023

S’APPLIQUE À : Azure Data Factory Azure Synapse Analytics

Conseil

Essayez Data Factory dans Microsoft Fabric, une solution d’analyse tout-en-un pour les entreprises. Microsoft Fabric couvre tous les aspects, du déplacement des données à la science des données, en passant par l’analyse en temps réel, l’aide à la décision et la création de rapports. Découvrez comment démarrer un nouvel essai gratuitement !

Cet article explique comment exécuter un package SSIS dans un pipeline Azure Data Factory ou dans Synapse Pipelines à l’aide d’une activité Procédure stockée.

Prérequis

Azure SQL Database

Dans le cadre de la procédure pas à pas de cet article, Azure SQL Database est utilisé pour héberger le catalogue SSIS. Vous pouvez également utiliser Azure SQL Managed Instance.

Data Factory

Vous aurez besoin d’une instance d’Azure Data Factory pour implémenter cette procédure pas à pas. Si vous n’en avez pas déjà une provisionnée, vous pouvez suivre les étapes du Démarrage rapide : Créer une fabrique de données à l’aide du portail Azure et d’Azure Data Factory Studio.

Runtime d’intégration Azure SSIS

Enfin, vous aurez également besoin d’un runtime d’intégration Azure-SSIS. Si vous n’en avez pas déjà un, suivez les instructions pas à pas fournies dans le Tutoriel : Déployer des packages SSIS.

Créer un pipeline avec une activité de procédure stockée

Lors de cette étape, vous utilisez l’interface utilisateur de Data Factory pour créer un pipeline. Si vous n’êtes pas déjà dans Azure Data Factory Studio, ouvrez votre fabrique de données dans le portail Azure et cliquez sur le bouton Ouvrir Azure Data Factory Studio pour l’ouvrir.

Screenshot of the Azure Data Factory home page.

Vous allez ensuite ajouter une activité de procédure stockée à un nouveau pipeline et le configurer pour exécuter le package SSIS à l’aide de la procédure stockée sp_executesql.

Dans la page d'accueil, cliquez sur Orchestrer :
Dans la boîte à outils Activités, recherchez Procédure stockée et glissez-déposez une activité Procédure stockée vers la surface du concepteur de pipeline.
Sélectionnez l’activité Procédure stockée que vous venez d’ajouter à la surface du concepteur, puis l’onglet Paramètres. Cliquez ensuite sur + Nouveau en regard de Service lié. Vous créez une connexion à la base de données dans Azure SQL Database qui héberge le catalogue SSIS (base de données SSIDB).
Dans la fenêtre Nouveau service lié, procédez comme suit :
1. Sélectionnez Azure SQL Database comme Type.
2. Sélectionnez le runtime par défaut AutoResolveIntegrationRuntime pour vous connecter à la base de données Azure SQL qui héberge la base de données SSISDB.
3. Dans le champ Nom du serveur, sélectionnez la base de données Azure SQL qui héberge la base de données SSISDB.
4. Sélectionnez SSISDB comme Nom de la base de données.
5. Dans Nom d’utilisateur, entrez le nom de l’utilisateur qui a accès à la base de données.
6. Dans Mot de passe, entrez le mot de passe de l’utilisateur.
7. Testez la connexion à la base de données en cliquant sur le bouton Tester la connexion.
8. Enregistrez le service lié en cliquant sur le bouton Enregistrer.

Revenez dans la fenêtre de propriétés, puis, sous l’onglet Paramètres, effectuez les étapes suivantes :

Sélectionnez Modifier.
Dans le champ Nom de la procédure stockée, entrez sp_executesql.
Cliquez sur + Nouveau dans la section Paramètres de procédure stockée.
Comme Nom du paramètre, entrez stmt.
Comme Type de paramètre, entrez String.

Comme Valeur du paramètre, entrez la requête SQL suivante :

Dans la requête SQL, spécifiez les valeurs correctes pour les paramètres folder_name, project_name et 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

Pour valider la configuration du pipeline, cliquez sur Valider dans la barre d’outils. Pour fermer le Rapport de validation de pipeline, cliquez sur >>.
Publiez le pipeline dans Data Factory en cliquant sur le boutonPublier tout.

Exécuter et surveiller le pipeline

Dans cette section, vous déclenchez une exécution du pipeline puis vous la surveillez.

Pour déclencher une exécution de pipeline, cliquez sur Déclencher dans la barre d’outils, puis sur Déclencher maintenant.
Dans la fenêtre Exécution du pipeline, sélectionnez Terminer.
Basculez vers l’onglet Surveiller sur la gauche. Vous voyez l’exécution de pipeline et son état, ainsi que d’autres informations (telles que l’heure de début d’exécution). Pour actualiser la vue, cliquez sur Actualiser.
Cliquez sur le lien Afficher les exécutions d’activités dans la colonne Actions. Une seule exécution d’activité est affichée, étant donnée que le pipeline n’a qu’une seule activité (activité de procédure stockée).
Vous pouvez exécuter la requête suivante sur la base de données SSISDB dans SQL Database pour vérifier que le package s’est exécuté.
```
select * from catalog.executions
```

Remarque

Vous pouvez également créer un déclencheur planifié pour votre pipeline afin que le pipeline s’exécute selon une planification (horaire, quotidienne, et ainsi de suite). Pour obtenir un exemple, consultez Créer une fabrique de données - Interface utilisateur de Data Factory.

Azure PowerShell

Notes

Nous vous recommandons d’utiliser le module Azure Az PowerShell pour interagir avec Azure. Pour commencer, consultez Installer Azure PowerShell. Pour savoir comment migrer vers le module Az PowerShell, consultez Migrer Azure PowerShell depuis AzureRM vers Az.

Dans cette section, vous utilisez Azure PowerShell pour créer un pipeline Data Factory avec une activité de procédure stockée qui appelle un package SSIS.

Installez les modules Azure PowerShell les plus récents en suivant les instructions décrites dans Comment installer et configurer Azure PowerShell.

Créer une fabrique de données

Vous pouvez utiliser la fabrique de données qui a le runtime d’intégration Azure-SSIS ou créer une fabrique de données distincte. La procédure suivante décrit les étapes permettant de créer une fabrique de données. Vous créez un pipeline avec une activité de procédure stockée dans cette fabrique de données. L’activité de procédure stockée exécute une procédure stockée dans la base de données SSISDB pour exécuter votre package SSIS.

Définissez une variable pour le nom du groupe de ressources que vous utiliserez ultérieurement dans les commandes PowerShell. Copiez le texte de commande suivant dans PowerShell, spécifiez un nom pour le groupe de ressources Azure entre des guillemets doubles, puis exécutez la commande. Par exemple : "adfrg".
```
$resourceGroupName = "ADFTutorialResourceGroup";
```
Si le groupe de ressources existe déjà, vous pouvez ne pas le remplacer. Affectez une valeur différente à la variable $ResourceGroupName et exécutez à nouveau la commande
Pour créer le groupe de ressources Azure, exécutez la commande suivante :
```
$ResGrp = New-AzResourceGroup $resourceGroupName -location 'eastus'
```
Si le groupe de ressources existe déjà, vous pouvez ne pas le remplacer. Affectez une valeur différente à la variable $ResourceGroupName et exécutez à nouveau la commande.
Définissez une variable pour le nom de la fabrique de données.

Important

Mettez à jour le nom de la fabrique de données afin qu’il soit globalement unique.
```
$DataFactoryName = "ADFTutorialFactory";
```
Pour créer la fabrique de données, exécutez l’applet de commande suivante Set-AzDataFactoryV2, à l’aide des propriétés Location et ResourceGroupName à partir de la variable $ResGrp :
```
$DataFactory = Set-AzDataFactoryV2 -ResourceGroupName $ResGrp.ResourceGroupName -Location $ResGrp.Location -Name $dataFactoryName 
```

Notez les points suivants :

Le nom de la fabrique de données Azure doit être un nom global unique. Si vous recevez l’erreur suivante, changez le nom, puis réessayez.
```
The specified Data Factory name 'ADFv2QuickStartDataFactory' is already in use. Data Factory names must be globally unique.
```
Pour créer des instances de fabrique de données, le compte d’utilisateur que vous utilisez pour vous connecter à Azure doit être un membre des rôles contributeur ou propriétaire, ou un administrateur de l’abonnement Azure.
Pour obtenir la liste des régions Azure dans lesquelles Data Factory est actuellement disponible, sélectionnez les régions qui vous intéressent dans la page suivante, puis développez Analytique pour localiser Data Factory : Disponibilité des produits par région. Les magasins de données (Stockage Azure, Azure SQL Database, etc.) et les services de calcul (HDInsight, etc.) utilisés par la fabrique de données peuvent être proposés dans d’autres régions.

Créer un service lié Azure SQL Database

Créez un service lié pour lier votre base de données qui héberge le catalogue SSIS à votre fabrique de données. Data Factory utilise les informations de ce service lié pour se connecter à la base de données SSISDB, et exécute une procédure stockée pour exécuter un package SSIS.

Créez un fichier JSON nommé AzureSqlDatabaseLinkedService.json dans le dossier C:\ADF\RunSSISPackage avec le contenu suivant :

Important

Remplacez <servername>, <username> et <password> par les valeurs de votre base de données Azure SQL avant d’enregistrer le fichier.

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

Dans Azure PowerShell, basculez vers le dossier C:\ADF\RunSSISPackage.

Exécutez la cmdlet Set-AzDataFactoryV2LinkedService pour créer le service lié : AzureSqlDatabaseLinkedService.

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

Créer un pipeline avec une activité de procédure stockée

Lors de cette étape, vous allez créer un pipeline avec une activité de procédure stockée. L’activité appelle la procédure stockée sp_executesql pour exécuter votre package SSIS.

Créez un fichier JSON nommé RunSSISPackagePipeline.json dans le dossier C:\ADF\RunSSISPackage avec le contenu suivant :

Important

Remplacez <FOLDER NAME>, <PROJECT NAME> et <PACKAGE NAME> par des noms de dossier, projet et package dans le catalogue SSIS avant d’enregistrer le fichier.

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

Pour créer le pipeline : RunSSISPackagePipeline, exécutez la cmdlet Set-AzDataFactoryV2Pipeline.

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

Voici l'exemple de sortie :

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

Créer une exécution du pipeline

Utilisez la cmdlet Invoke-AzDataFactoryV2Pipeline pour exécuter le pipeline. L’applet de commande renvoie l’ID d’exécution du pipeline pour permettre une surveillance ultérieure.

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

Surveiller l’exécution du pipeline.

Exécutez le script PowerShell suivant afin de vérifier continuellement l’état de l’exécution du pipeline jusqu’à la fin de la copie des données. Copiez/collez le script suivant dans la fenêtre PowerShell et appuyez sur ENTRÉE.

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
}

Créer un déclencheur

À l’étape précédente, vous avez appelé le pipeline à la demande. Vous pouvez également créer un déclencheur de planification pour exécuter le pipeline d’après une planification (horaire, quotidienne, etc.).

Créez un fichier JSON nommé MyTrigger.json dans le dossier C:\ADF\RunSSISPackage avec le contenu suivant :

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

Dans Azure PowerShell, basculez vers le dossier C:\ADF\RunSSISPackage.

Exécutez la cmdlet Set-AzDataFactoryV2Trigger pour créer le déclencheur.

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

Par défaut, le déclencheur est arrêté. Démarrez le déclencheur en exécutant la cmdlet Start-AzDataFactoryV2Trigger.

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

Vérifiez que le déclencheur est démarré en exécutant la cmdlet Get-AzDataFactoryV2Trigger.

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

Exécutez la commande ci-dessous après l’heure suivante. Par exemple, si l’heure actuelle est 15h25 UTC, exécutez la commande à 16h00 UTC.
```
Get-AzDataFactoryV2TriggerRun -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -TriggerName "MyTrigger" -TriggerRunStartedAfter "2017-12-06" -TriggerRunStartedBefore "2017-12-09"
```
Vous pouvez exécuter la requête suivante sur la base de données SSISDB dans SQL Database pour vérifier que le package s’est exécuté.
```
select * from catalog.executions
```

Vous pouvez également surveiller le pipeline à l’aide du portail Azure. Pour obtenir des instructions pas à pas, consultez Surveiller le pipeline.

Share via

Exécuter un package SSIS avec l’activité Procédure stockée

Prérequis

Azure SQL Database

Data Factory

Runtime d’intégration Azure SSIS

Créer un pipeline avec une activité de procédure stockée

Exécuter et surveiller le pipeline

Azure PowerShell

Créer une fabrique de données

Créer un service lié Azure SQL Database

Créer un pipeline avec une activité de procédure stockée

Créer une exécution du pipeline

Surveiller l’exécution du pipeline.

Créer un déclencheur

Ressources supplémentaires

Share via

Exécuter un package SSIS avec l’activité Procédure stockée

Prérequis

Azure SQL Database

Data Factory

Runtime d’intégration Azure SSIS

Créer un pipeline avec une activité de procédure stockée

Exécuter et surveiller le pipeline

Azure PowerShell

Créer une fabrique de données

Créer un service lié Azure SQL Database

Créer un pipeline avec une activité de procédure stockée

Créer une exécution du pipeline

Surveiller l’exécution du pipeline.

Créer un déclencheur

Contenu connexe

Ressources supplémentaires