Parallélisme SSIS par package


Comment prioriser et paralléliser l’exécution de packages d’un projet SSIS stocké dans le catalogue ? Dans cet article, nous allons détailler une méthode de parallélisme SSIS qui soit paramétrable pour contrôler les éléments suivant :

  • Ordonnancement d’alimentation par entrepôt de données
  • Ordonnancement des packages au sein d’un même entrepôt de données
  • Activation/Désactivation d’entrepôt(s) de données et de package(s)
  • Nombre de packages SSIS exécutables simultanément en mode synchrone

Cet article a fait l’objet d’une session à l’édition francophone du 24 heures PASS, elle est disponible en ligne.

Parallélisme SSIS – Accélérer vos traitements

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - parallelism

1) Configuration du catalogue SSIS

Notre catalogue SSIS contient le dossier myFolder avec l’environnement DEV.

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - catalogue_ssis

Des variables liées à l’environnement DEV ont également été créées :

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - catalogue_ssis_variables

  • varCompletionPortThread : Nombre de threads en mode asynchrone autorisés dans un pool d’exécution (ex : opération I/O ans attente de process complet)
  • varWorkerThread : Nombre de threads en mode synchrone autorisés dans un pool d’exécution
  • varSSISCSCatalog : Chaine de connexion vers le catalogue SSIS
  • varSSISCSParam : Chaine de connexion vers la base de paramétrage pour le parallélisme de notre projet

2) Création d’une base de paramétrage

Dans un premier temps, nous allons créer une base de données dédiée au paramétrage, Param_SSIS, avec les objets suivants :

Table SETUP_PACKAGE_TYPE : liste des entrepôts de données à alimenter

  • Package_Type
    • STG : Données brutes et temporaires
    • ODS : Données historisées
    • DWH : Données transformées
  • Package_Type_Order : Ordre d’alimentation pour chaque entrepôts de données
    • L’alimentation des entrepôts de données se fait en cascade, STG alimente ODS et ODS alimente DWH avec potentiellement des transformations entre chaque zone de stockage
  • Package_Type_Enable : Entrepôt désactivé ou pas
    • Il n’est pas toujours nécessaire de recharger tous les entrepôts de données

Table SETUP_PACKAGE : liste des packages à exécuter

  • Package_Type : Entrepôt de données concerné
  • Package_Projet : Projet SSIS associé au package
  • Package_Order : Ordre d’exécution du package
    • Les packages qui seront exécutés simultanément (en fonction du nombre de threads maximum autorisé dans le pool) auront le même Package_Order
  • Package_Enable : Exécution désactivée ou pas
    • Ces packages ne sont pas exécutés pour effectuer un chargement partielle

Vue vw_SETUP_PACKAGES_ORDER : Pools d’exécution des packages à exécuter. Il est primordiale de définir la clause ORDER BY  [Package_Type_Order], [Package_Order] lors de l’appel à cette vue pour obtenir la liste des packages à exécuter comme nous le verrons plus tard.

Le résultat de la vue ordonnée : renvoie tous les pools d’exécution par entrepôt de données & packages

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - param_ssis_view

3) Création d’un projet SSIS nommé « SSISParallelism »

Les paramètres de projet à créer ont sensiblement les mêmes noms que les variables de l’environnement de DEV de notre catalogue SSIS (et les mêmes valeurs) pour des raisons pratiques afin de faciliter leur mapping par la suite.

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - ssis_project_param

3.1 Master package

Pour piloter l’exécution de plusieurs packages depuis SSIS, nous allons créer un master package dans le même projet que les packages enfant. A l’arrivée, il ressemblera à ça :

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - master_package

Deux chaines de connexion sont présentes :

  • 1 connexion de type ADO.NET qui pointe vers le catalogue SSIS. Ce type de connexion fournit une couche d’abstraction permettant d’accéder aux attributs de la connexion depuis une tâche de script (C#).
  • 1 connexion de type OLEDB qui pointe vers notre base Param_SSIS (OLEDB) qui stocke tout le paramétrage du parallélisme lié à notre projet SSIS.

Il y a d’autres différences entre une connexion de type ADO.NET et OLEDB telle que la manière de passer des paramètres d’entrée à une requête que nous verrons par la suite. A noter que ces connexions sont aussi mappées sur les paramètres de projet qui porte le même nom.

Pour la cuisine interne du master package, les variables à créer sont les suivantes :

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - 2016-07-09-09_33_36-SSISParallelism-Running-Microsoft-Visual-Studio-Administrator

  • SSISFolder, SSISProject, SSISEnvironment : Ces variables stockent les informations de contexte d’une exécution. Si le le package Master est exécuté depuis le catalogue SSIS, la valeurs de ces variables seront déduites. Le cas échéant, si le package master est exécuté depuis Visual Studio, les variables conserveront les valeurs affectées par défaut. Les informations de contexte seront par la suite exploitées pour l’exécution des packages enfant.
  • PackagesPools : Contient la liste des pools d’exécution ordonnancés par entrepôt de données et par packages, sous la forme d’une variable de type Object. C’est le résultat de l’appel à la vue vw_SETUP_PACKAGES_ORDER avec la clause ORDER BY qui va bien.
  • PackagesList : Cette variable est alimentée dans une boucle Foreach Loop Container en parcourant la variable parente PackagesPools, résultat de l’itération. 1 pool contient la liste des packages enfant à exécuter ayant un ordonnancement identique pour un même entrepôt de données. 1  occurrence = 1 pool d’exécution

3.2 Tâche SQL : Récupération du contexte d’exécution du master package

On tente de déduire le contexte d’exécution du package master : Visual Studio ou Catalogue SSIS ? Dans le cas où le master package est exécuté depuis le catalogue SSIS, on récupère les informations de contexte qui nous intéressent en passant System::ServerExecutionID en paramètre à une requête qui interroge le catalogue. Le cas échéant, on cré artificiellement un recorset avec les valeurs par défaut des variables ci-dessous passées aussi en paramètre d’entrée de la requête :

  • FolderName
  • ProjectName
  • EnvironementName

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - tache_get_context-1

La requête qui interroge le catalogue :

Paramètres d’entrée de la requête :

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - parameter_in

Récupération de la sortie de la requête en respectant l’ordre des colonnes de la clause SELECT (Result Name = position de la colonne)

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - parameter_out

3.3 Tâche SQL : Récupération des pools d’exécution des packages enfants

On va cette fois interroger notre repository pour obtenir la liste des pools d’exécution des packages enfant à partir de la vue et on consigne le dataset dans une variable de type Object appelée User::PakagesPools.

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - tache_get_package_pool2

La requête qui interroge le repository :

Paramètre d’entrée de notre requête :

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - task2_param_in

Récupération de la sortie de la requête

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - task2_param_out

3.4 Création d’un conteneur de type For Each

On va itère la liste des pools de packages à exécuter ordonnancés par entrepôt et packages (1 pool = n packages à exécuter) en récupérant le résultat de la vue créée plus haut

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - for_each1

Récupération de la sortie ligne à ligne de l’objet PackagesPools

3.5 Création d’un conteneur For Each

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - for_each2

3.6 Création d’une tâche de script C# à l’intérieur du conteneur For Each

Cette tâche C# est chargée de l’exécution des packages enfants via l’itération des pools dans la boucle For Each. Le script prend en entrée les variables et paramètres de projet en lecture seule.

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - script_c

  • User::PakagesList (dataset ligne à ligne = 1 pool à traiter)
  • User::SSISEnvironment
  • User::SSISFolder
  • User::SSISProject
  • $Project::WorkerThreads : 10
  • $Project::CompletionPortThreads : 0

Nous allons également intégrer quelques assemblies supplémentaires dans notre script C# pour gérer l’accès au catalogue et exécuter un pool de packages enfant.

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - c_master

Assembly Chemin
Integration Service C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll
SMO C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
SFC C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\11.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
ConnectionInfo C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\13.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll

Ce script C# contient entre autre :

  • 1 méthode qui vérifie les informations liées au catalogue
  • 1 méthode qui exécute nos packages enfants en vérifiant à intervalle régulier leur statut d’exécution : en cours, succès, échec
  • Notre connexion au catalogue SSIS de type ADO.NET ré exploitée par nos méthodes
    • Attention au nom de la connexion, en cas de changement il faudra le répercuter dans le script C#

  • Nos paramètres liés au parallélisme

  • Notre pool d’exécution à traiter PackageList, qui stocke une liste de packages enfant à exécuter avec un séparateur (,)

Voici le script en version complète :

Notre projet SSIS est enfin finalisé et prêt à être déployé dans le catalogue SSIS. Entre temps, nous y avons intégré nos packages enfant.

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - projet_ssis

3.7 Déploiement du projet SSIS dans le catalogue

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - deploy

4) Exécution depuis le catalogue SSIS

Il reste à compléter le configuration de notre projet SSIS au sein du catalogue en mappant nos paramètres de projet aux variables de l’environnement DEV.

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - exec

Le master package de notre projet est prêt à être exécuté !!!!

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - ex

Notre rapport d’exécution de notre projet SSIS :

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - report_ssis

Si nous avions exécuté notre master package depuis Visual Studio, voici la sortie que nous aurions obtenue :

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - 2016-09-22-11_40_10-SSISParallelism-Microsoft-Visual-Studio-Administrator

L’intégralité du projet est disponible en téléchargement : SSISParallelism.zip

Merci à Sophie Bismuth consultante MS qui a initié le projet et y a largement contribué.

Et roule ma poule, en marche pour le parallélisme SSIS !

Auteur

Expert SQL Server - Parallélisme SSIS par package - BI & Big Data C# SQL Server  - e86fd254d6eae54c82dc5be9b5003bd7?s=100&d=mm&r=g
Sarah Bessard
Experte SQL Server Prod/Etude avec un bonus sur la BI, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur. Besoin d'une expertise SQL Server ? N'hésitez pas à me contacter.

Leave a comment

Your email address will not be published.

error: