Catalogue SSIS : Exécution en parallèle

Problématique :

Nous voulons exécuter en parallèle un ensemble de Package SSIS stockés dans le catalogue SSIS.

Rappel :

Le catalogue SSIS est apparu avec la version SQL SERVER 2012, c'est un entrepôt de stockage des Projets et des Packages SSIS, il permet entre autre de paramétrer les variables d'environnements et gère l'historique d’exécution.

Voici l'article sur MSDN : Catalogue SSIS    

Solution :

Nous avons un ensemble de Package stockée dans le catalogue SSIS, il faut savoir que dans le catalogue nous avons l'arborescence suivantes :

Catalogue > Répertoire > Projet > Packages

Nous avons plusieurs méthodes pour exécuter un Package SSIS à partir du catalogue :

•  Exécuter un Package SSIS manuellement : 

Clic droit sur le package SSIS dans le catalogue et Exécuter

Execution Manuel SSIS

                                    

• Exécuter un Package SSIS par un Job SQL : 

Dans une étape d'un Job SQL, vous pouvez ajouter le package à partir d'un Catalogue SSIS

Execution SSIS Planifier Par un Job SQL

                               

                             

• Exécuter un Package SSIS par T-SQL ou Procédure stockée :

SSISDB offre la possibilité d'exécuter des packages SSIS via des procédures stockées : pour cela il faut utiliser 3 PS :

EXEC [SSISDB].[catalog].[create_execution] : Permet de créer l'exécution

EXEC [SSISDB].[catalog].[set_execution_parameter_value] : Permet de paramétrer l'exécution

EXEC [SSISDB].[catalog].[start_execution] : Permet d'exécuter

Le code donne ça :

DECLARE @execution_id bigint

DECLARE @PackageName nvarchar(260)

DECLARE @foldername nvarchar(128)

DECLARE @projectname nvarchar(128)

SET @PackageName = 'PackageManagement_Parallele.dtsx'

SET @foldername = 'DWH'

SET @projectname = 'CatalogueSSIS'

EXEC [SSISDB].[catalog].[create_execution] @package_name=@PackageName, @execution_id=@execution_id OUTPUT, @folder_name=@foldername, @project_name=@projectname, @use32bitruntime=False, @reference_id=Null

-- Set execution parameter for logging level

DECLARE @var0 smallint = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

-- Execute the package

EXEC [SSISDB].[catalog].[start_execution] @execution_id

IMPORTANT :

Le problème avec cette méthode c'est que la procédure lance l'exécution sans nous renvoyer le résultat de l'exécution et la transaction est exécutée avec succès indépendamment de l'état d'exécution du Package.

Mais cette méthode est très pratique pour lancer plusieurs Packages en parallèle, nous verrons dans la suite l'utilité.

Si vous voulez attendre la fin d'exécution du Package est récupéré le statut de l'exécution il faut ajouter un paramètre et ça donne ça :

DECLARE @execution_id bigint

DECLARE @PackageName nvarchar(260)

DECLARE @foldername nvarchar(128)

DECLARE @projectname nvarchar(128)

SET @PackageName = 'PackageManagement_Parallele.dtsx'

SET @foldername = 'DWH'

SET @projectname = 'CatalogueSSIS'

EXEC [SSISDB].[catalog].[create_execution] @package_name=@PackageName, @execution_id=@execution_id OUTPUT, @folder_name=@foldername, @project_name=@projectname, @use32bitruntime=False, @reference_id=Null

-- Set execution parameter for logging level

DECLARE @var0 smallint = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

-- Set execution parameter for synchronized

DECLARE @synchronous SMALLINT = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id

 ,@object_type = 50

 ,@parameter_name = N'SYNCHRONIZED'

 ,@parameter_value = @synchronous

-- Execute the package

EXEC [SSISDB].[catalog].[start_execution] @execution_id

-- Show status

SELECT 

      execution_id

,project_name   

,folder_name

,package_name

     ,[status description] 

     = CASE [status] 

               WHEN 1 THEN 'created' 

               WHEN 2 THEN 'Running' 

               WHEN 3 THEN 'canceled' 

               WHEN 4 THEN 'failed' 

               WHEN 5 THEN 'pending' 

               WHEN 6 THEN 'ended unexpectedly' 

               WHEN 7 THEN 'succeeded' 

               WHEN 8 THEN 'stopping' 

               WHEN 9 THEN 'completed' 

     END 

FROM   [catalog] .[executions] 

WHERE execution_id = @execution_id

IMPORTANT :

Malheureusement la définition des statuts de retour ne sont pas répertorié nul part dans la base, vous pouvez les retrouvez dans les Books Online comme celui là : Ici

Sinon y'a un article intéressant qui parle de ce sujet : Ici

Bien évidement vous pouvez créer une procédure stockée et exécute l'ensemble de traitement et passer les paramètres en variables.

• Exécuter des Packages SSIS en parallèle :

Revenant à notre problématique du départ, généralement on a besoin d'exécuter l'ensemble de packages d'un projet, et pour optimiser les temps de traitement nous voulons les exécuter en parallèle tout en vérifiant que tout les packages sont exécuter avec succès.

Pour cela je vais utiliser un Package SSIS qui gère l'exécution :

Première étape je vais récupérer le dernier ID d'exécution pour prendre en compte que les exécutions suivantes.je stocke cet ID dans une variable

SELECT MAX(execution_id)

FROM SSISDB.CATALOG.executions

WHERE folder_name = ?

Ensuite je récupère la liste des packages dans le projet en question :

SELECT T.NomPackage

FROM(

SELECT pk.package_id as [IdPackage]

,pk.name as [NomPackage]

FROM [SSISDB].[catalog].[packages] pk

INNER JOIN [SSISDB].[catalog].[projects] pj

ON pk.[project_id] = pj.[project_id]

INNER JOIN [SSISDB].[catalog].[folders] fd

ON pj.[folder_id] = fd.[folder_id]

AND fd.[name] = ?

AND pj.name = ?

) T

Je lance en parallèle tout les packages avec la commande SQL expliquée au début

Ci joint l'exemple de package SSIS : CatalogueSSISPackage

Pensez à initialiser les variables "V_folder_name" et "V_project_name" et configurer la connexion pour pointer sur votre serveur.

Enjoy