Parallélisme SSIS par package

22 septembre 2016

Commentaires fermés

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

1) Configuration du catalogue SSIS

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

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

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ôt 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 partiel

Vue vw_SETUP_PACKAGES_ORDER : Pools d’exécution des packages à exécuter. Il est primordial 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.

USE [master]
GO
/****** Object:  Database [Param_SSIS]    Script Date: 18/09/2016 21:56:34 ******/
CREATE DATABASE [Param_SSIS]
GO
USE [Param_SSIS]
GO
/****** Object:  Table [dbo].[SETUP_PACKAGES]    Script Date: 18/09/2016 21:56:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SETUP_PACKAGES](
	[Package_Name] [varchar](100) NOT NULL,
	[Package_Project] [varchar](50) NOT NULL,
	[Package_Type] [varchar](10) NOT NULL,
	[Package_Order] [int] NOT NULL,
	[Package_Enable] [bit] NOT NULL,
 CONSTRAINT [PK_SETUP_PACKAGES] PRIMARY KEY CLUSTERED 
(
	[Package_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SETUP_PACKAGES_TYPE]    Script Date: 18/09/2016 21:56:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SETUP_PACKAGES_TYPE](
	[Package_Type] [varchar](10) NOT NULL,
	[Package_Type_Order] [int] NOT NULL,
	[Package_Type_Enable] [bit] NOT NULL,
 CONSTRAINT [PK_SETUP_PACKAGES_TYPE] PRIMARY KEY CLUSTERED 
(
	[Package_Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SETUP_PACKAGES]  WITH CHECK ADD  CONSTRAINT [FK_SETUP_PACKAGES_SETUP_PACKAGES_TYPE] FOREIGN KEY([Package_Type])
REFERENCES [dbo].[SETUP_PACKAGES_TYPE] ([Package_Type])
GO
ALTER TABLE [dbo].[SETUP_PACKAGES] CHECK CONSTRAINT [FK_SETUP_PACKAGES_SETUP_PACKAGES_TYPE]
GO

SET ANSI_PADDING OFF
GO
/****** Object:  View [dbo].[vw_SETUP_PACKAGES_ORDER]    Script Date: 18/09/2016 21:56:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_SETUP_PACKAGES_ORDER]

AS

SELECT	T.Package_Type, T.Package_Type_Order, T.Package_Project, T.Package_Order, Package_List =

		      STUFF((		
						SELECT	',' + Package_Name
						FROM	[dbo].[SETUP_PACKAGES] P
						WHERE	T.Package_Type = P.Package_Type
						AND		T.Package_Order = P.Package_Order
						AND		P.Package_Enable = 1
						ORDER BY Package_Order
						FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
					,1,1,'')

FROM	(
			SELECT	T.Package_Type, T.Package_Type_Order, P.Package_Project, P.Package_Order
			FROM	[dbo].[SETUP_PACKAGES_TYPE] T
			INNER	JOIN [dbo].[SETUP_PACKAGES] P ON T.Package_Type = P.Package_Type
			WHERE	T.Package_Type_Enable = 1
			AND	P.Package_Enable = 1
			GROUP BY T.Package_Type, T.Package_Type_Order, P.Package_Project, P.Package_Order 
		) T


GO

-- Création des entrpôts de données avec ordre d'alimentation
INSERT [dbo].[SETUP_PACKAGES_TYPE] ([Package_Type], [Package_Type_Order], [Package_Type_Enable]) VALUES (N'STG', 1, 1)
INSERT [dbo].[SETUP_PACKAGES_TYPE] ([Package_Type], [Package_Type_Order], [Package_Type_Enable]) VALUES (N'ODS', 2, 1)
INSERT [dbo].[SETUP_PACKAGES_TYPE] ([Package_Type], [Package_Type_Order], [Package_Type_Enable]) VALUES (N'DWH', 3, 1)

-- Création des packages associés au projet SSISParallelism et à l'entrepôt de données STG avec ordre d'alimentation (pool d'exécution 1)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_STG_Table1.dtsx', N'SSISParallelism', N'STG', 1, 1)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_STG_Table2.dtsx', N'SSISParallelism', N'STG', 1, 1)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_STG_Table3.dtsx', N'SSISParallelism', N'STG', 1, 0)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_STG_Table4.dtsx', N'SSISParallelism', N'STG', 1, 1)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_STG_Table5.dtsx', N'SSISParallelism', N'STG', 1, 1)

-- Création des packages associés au projet SSISParallelism et à l'entrepôt de données ODS avec ordre d'alimentation (pool d'exécution 2)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_ODS_Table1.dtsx', N'SSISParallelism', N'ODS', 1, 1)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_ODS_Table2.dtsx', N'SSISParallelism', N'ODS', 1, 1)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_ODS_Table3.dtsx', N'SSISParallelism', N'ODS', 1, 1)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_ODS_Table4.dtsx', N'SSISParallelism', N'ODS', 1, 1)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_ODS_Table5.dtsx', N'SSISParallelism', N'ODS', 1, 1)

-- Création des packages associés au projet SSISParallelism et à l'entrepôt de données DHW avec ordre d'alimentation (pool d'exécution 3 : alimentation des tables de Dimension)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_DWH_Table1.dtsx', N'SSISParallelism', N'DWH', 1, 1)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_DWH_Table2.dtsx', N'SSISParallelism', N'DWH', 1, 1)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_DWH_Table3.dtsx', N'SSISParallelism', N'DWH', 1, 1)

-- Création des packages associés au projet SSISParallelism et à l'entrepôt de données DHW avec ordre d'alimentation (pool d'exécution 4 : alimentation des tables de faits)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_DWH_Table4.dtsx', N'SSISParallelism', N'DWH', 2, 1)
INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_DWH_Table5.dtsx', N'SSISParallelism', N'DWH', 2, 1)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

USE [master]

/****** Object: Database [Param_SSIS] Script Date: 18/09/2016 21:56:34 ******/

CREATE DATABASE [Param_SSIS]

USE [Param_SSIS]

/****** Object: Table [dbo].[SETUP_PACKAGES] Script Date: 18/09/2016 21:56:34 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[SETUP_PACKAGES](

[Package_Name] [varchar](100) NOT NULL,

[Package_Project] [varchar](50) NOT NULL,

[Package_Type] [varchar](10) NOT NULL,

[Package_Order] [int] NOT NULL,

[Package_Enable] [bit] NOT NULL,

CONSTRAINT [PK_SETUP_PACKAGES] PRIMARY KEY CLUSTERED

(

[Package_Name] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDING OFF

/****** Object: Table [dbo].[SETUP_PACKAGES_TYPE] Script Date: 18/09/2016 21:56:34 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[SETUP_PACKAGES_TYPE](

[Package_Type] [varchar](10) NOT NULL,

[Package_Type_Order] [int] NOT NULL,

[Package_Type_Enable] [bit] NOT NULL,

CONSTRAINT [PK_SETUP_PACKAGES_TYPE] PRIMARY KEY CLUSTERED

(

[Package_Type] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[SETUP_PACKAGES] WITH CHECK ADD CONSTRAINT [FK_SETUP_PACKAGES_SETUP_PACKAGES_TYPE] FOREIGN KEY([Package_Type])

REFERENCES [dbo].[SETUP_PACKAGES_TYPE] ([Package_Type])

ALTER TABLE [dbo].[SETUP_PACKAGES] CHECK CONSTRAINT [FK_SETUP_PACKAGES_SETUP_PACKAGES_TYPE]

SET ANSI_PADDING OFF

/****** Object: View [dbo].[vw_SETUP_PACKAGES_ORDER] Script Date: 18/09/2016 21:56:34 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE VIEW [dbo].[vw_SETUP_PACKAGES_ORDER]

SELECT T.Package_Type, T.Package_Type_Order, T.Package_Project, T.Package_Order, Package_List =

STUFF((

SELECT ',' + Package_Name

FROM [dbo].[SETUP_PACKAGES] P

WHERE T.Package_Type = P.Package_Type

AND T.Package_Order = P.Package_Order

AND P.Package_Enable = 1

ORDER BY Package_Order

FOR XML PATH(''), TYPE).value('.', 'varchar(max)')

,1,1,'')

FROM (

SELECT T.Package_Type, T.Package_Type_Order, P.Package_Project, P.Package_Order

FROM [dbo].[SETUP_PACKAGES_TYPE] T

INNER JOIN [dbo].[SETUP_PACKAGES] P ON T.Package_Type = P.Package_Type

WHERE T.Package_Type_Enable = 1

AND P.Package_Enable = 1

GROUP BY T.Package_Type, T.Package_Type_Order, P.Package_Project, P.Package_Order

) T

-- Création des entrpôts de données avec ordre d'alimentation

INSERT [dbo].[SETUP_PACKAGES_TYPE] ([Package_Type], [Package_Type_Order], [Package_Type_Enable]) VALUES (N'STG', 1, 1)

INSERT [dbo].[SETUP_PACKAGES_TYPE] ([Package_Type], [Package_Type_Order], [Package_Type_Enable]) VALUES (N'ODS', 2, 1)

INSERT [dbo].[SETUP_PACKAGES_TYPE] ([Package_Type], [Package_Type_Order], [Package_Type_Enable]) VALUES (N'DWH', 3, 1)

-- Création des packages associés au projet SSISParallelism et à l'entrepôt de données STG avec ordre d'alimentation (pool d'exécution 1)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_STG_Table1.dtsx', N'SSISParallelism', N'STG', 1, 1)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_STG_Table2.dtsx', N'SSISParallelism', N'STG', 1, 1)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_STG_Table3.dtsx', N'SSISParallelism', N'STG', 1, 0)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_STG_Table4.dtsx', N'SSISParallelism', N'STG', 1, 1)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_STG_Table5.dtsx', N'SSISParallelism', N'STG', 1, 1)

-- Création des packages associés au projet SSISParallelism et à l'entrepôt de données ODS avec ordre d'alimentation (pool d'exécution 2)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_ODS_Table1.dtsx', N'SSISParallelism', N'ODS', 1, 1)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_ODS_Table2.dtsx', N'SSISParallelism', N'ODS', 1, 1)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_ODS_Table3.dtsx', N'SSISParallelism', N'ODS', 1, 1)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_ODS_Table4.dtsx', N'SSISParallelism', N'ODS', 1, 1)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_ODS_Table5.dtsx', N'SSISParallelism', N'ODS', 1, 1)

-- Création des packages associés au projet SSISParallelism et à l'entrepôt de données DHW avec ordre d'alimentation (pool d'exécution 3 : alimentation des tables de Dimension)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_DWH_Table1.dtsx', N'SSISParallelism', N'DWH', 1, 1)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_DWH_Table2.dtsx', N'SSISParallelism', N'DWH', 1, 1)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_DWH_Table3.dtsx', N'SSISParallelism', N'DWH', 1, 1)

-- Création des packages associés au projet SSISParallelism et à l'entrepôt de données DHW avec ordre d'alimentation (pool d'exécution 4 : alimentation des tables de faits)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_DWH_Table4.dtsx', N'SSISParallelism', N'DWH', 2, 1)

INSERT [dbo].[SETUP_PACKAGES] ([Package_Name], [Package_Project], [Package_Type], [Package_Order], [Package_Enable]) VALUES (N'Alim_DWH_Table5.dtsx', N'SSISParallelism', N'DWH', 2, 1)

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

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.

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 :

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 :

SSISFolder, SSISProject, SSISEnvironment : Ces variables stockent les informations de contexte d’une exécution. Si le package Master est exécuté depuis le catalogue SSIS, la valeur 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

La requête qui interroge le catalogue :

SELECT folder_name = ISNULL(c.folder_name, d.folder_name),
  project_name = ISNULL(c.project_name, d.project_name),
  environment_name = ISNULL(c.environment_name, d.environment_name)
FROM (SELECT folder_name = @SSISFolder, project_name = @SSISProject, environment_name = @SSISEnvironment) AS d
OUTER APPLY (
SELECT  TOP 1 folder_name, project_name, environment_name
FROM   catalog.executions 
WHERE    1 = 1 
AND      execution_id = @ServerExecutionID
AND  status = 2) AS c

SELECT folder_name = ISNULL(c.folder_name, d.folder_name),

project_name = ISNULL(c.project_name, d.project_name),

environment_name = ISNULL(c.environment_name, d.environment_name)

FROM (SELECT folder_name = @SSISFolder, project_name = @SSISProject, environment_name = @SSISEnvironment) AS d

OUTER APPLY (

SELECT TOP 1 folder_name, project_name, environment_name

FROM catalog.executions

WHERE 1 = 1

AND execution_id = @ServerExecutionID

AND status = 2) AS c

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

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)

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.

La requête qui interroge le repository :

SELECT [Package_List]
FROM	[dbo].[vw_SETUP_PACKAGES_ORDER] 
WHERE [Package_Project] = ?
ORDER BY  [Package_Type_Order], [Package_Order]

SELECT [Package_List]

FROM [dbo].[vw_SETUP_PACKAGES_ORDER]

WHERE [Package_Project] = ?

ORDER BY [Package_Type_Order], [Package_Order]

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

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

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

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

3.5 Création d’un conteneur For Each

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.

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.

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#

string ssisConnectionString = Dts.Connections["SSISCSCatalog"].ConnectionString;

1	string ssisConnectionString = Dts.Connections["SSISCSCatalog"].ConnectionString;

Nos paramètres liés au parallélisme

int workerThreads = (int)Dts.Variables["WorkerThreads"].Value;
int completionPortThreads = (int)Dts.Variables["CompletionPortThreads"].Value;

//First parameter is the number of thread in the pool
//Second paramter is the number of async I/O thread in the pool
//Both value cannot be less than your logical processor count
ThreadPool.SetMinThreads(workerThreads, completionPortThreads);
ThreadPool.SetMaxThreads(workerThreads, completionPortThreads);

int workerThreads = (int)Dts.Variables["WorkerThreads"].Value;

int completionPortThreads = (int)Dts.Variables["CompletionPortThreads"].Value;

//First parameter is the number of thread in the pool

//Second paramter is the number of async I/O thread in the pool

//Both value cannot be less than your logical processor count

ThreadPool.SetMinThreads(workerThreads, completionPortThreads);

ThreadPool.SetMaxThreads(workerThreads, completionPortThreads);

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 :

#region Help:  Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services control flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */
#endregion


#region Namespaces
using Microsoft.SqlServer.Management.IntegrationServices;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Threading;
#endregion

namespace ST_6ed5a804b01d4911b13cd6814da233ba
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region Help:  Using Integration Services variables and parameters in a script
        /* To use a variable in this script, first ensure that the variable has been added to 
         * either the list contained in the ReadOnlyVariables property or the list contained in 
         * the ReadWriteVariables property of this script task, according to whether or not your
         * code needs to write to the variable.  To add the variable, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and 
         * ReadWriteVariables properties in the Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         * 
         * Example of reading from a variable:
         *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
         * 
         * Example of writing to a variable:
         *  Dts.Variables["User::myStringVariable"].Value = "new value";
         * 
         * Example of reading from a package parameter:
         *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;
         *  
         * Example of reading from a project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;
         * 
         * Example of reading from a sensitive project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
         * */

        #endregion

        #region Help:  Firing Integration Services events from a script
        /* This script task can fire events for logging purposes.
         * 
         * Example of firing an error event:
         *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
         * 
         * Example of firing an information event:
         *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
         * 
         * Example of firing a warning event:
         *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
         * */
        #endregion

        #region Help:  Using Integration Services connection managers in a script
        /* Some types of connection managers can be used in this script task.  See the topic 
         * "Working with Connection Managers Programatically" for details.
         * 
         * Example of using an ADO.Net connection manager:
         *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
         *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
         *
         * Example of using a File connection manager
         *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
         *  string filePath = (string)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
         * */
        #endregion

        private void CheckCatalog(string dataSource, string ssisFolder, string ssisProject, string ssisEnvironment, string[] ssisPackages)
        {

            SqlConnection ssisConnection = new SqlConnection(dataSource);
            IntegrationServices integrationServices = new IntegrationServices(ssisConnection);

            var connBuilder = new SqlConnectionStringBuilder(dataSource);

            var ssisCatalog = connBuilder.InitialCatalog;

            if (integrationServices.Catalogs.Contains(ssisCatalog))
            {
                var catalog = integrationServices.Catalogs[ssisCatalog];

                if (!catalog.Folders.Contains(ssisFolder))
                {
                    throw new Exception(String.Format("SSIS folder doesn't exists : {0}", ssisFolder));
                }
                else
                {
                    var folder = catalog.Folders[ssisFolder];

                    if (!folder.Projects.Contains(ssisProject))
                    {
                        throw new Exception(String.Format("SSIS project doesn't exists : {0}", ssisProject));
                    }
                    else
                    {
                        var project = folder.Projects[ssisProject];

                        if (!project.References.Contains(ssisEnvironment, "."))
                        {
                            throw new Exception(String.Format("SSIS environment reference doesn't exists or is not mapped to project : {0}", ssisEnvironment));
                        }
                        else
                        {

                            EnvironmentReference environmentRef = project.References[ssisEnvironment, "."];

                            string invalidPackages = string.Empty;

                            foreach (string ssisPackage in ssisPackages)
                            {

                                if (!project.Packages.Contains(ssisPackage))
                                {
                                    invalidPackages += ((String.IsNullOrEmpty(invalidPackages)) ? "SSIS package(s) not found : " : "") + ssisPackage + ",";
                                }
                            }

                            if (invalidPackages.Length > 0) throw new Exception(invalidPackages.Substring(0, invalidPackages.Length - 1));

                        }

                    }
                }
            }

        }

        private void ExecPackage(string dataSource, string ssisFolder, string ssisProject, string ssisEnvironment, string ssisPackage)
        {

            SqlConnection ssisConnection = new SqlConnection(dataSource);
            IntegrationServices integrationServices = new IntegrationServices(ssisConnection);

            var connBuilder = new SqlConnectionStringBuilder(dataSource);

            var ssisCatalog = connBuilder.InitialCatalog;

            Microsoft.SqlServer.Management.IntegrationServices.PackageInfo myPackage = integrationServices.Catalogs[ssisCatalog].Folders[ssisFolder].Projects[ssisProject].Packages[ssisPackage];

            EnvironmentReference environmentRef = integrationServices.Catalogs[ssisCatalog].Folders[ssisFolder].Projects[ssisProject].References[ssisEnvironment, "."];

            long executionIdentifier = myPackage.Execute(false, environmentRef);

            ExecutionOperation executionOperation = integrationServices.Catalogs[ssisCatalog].Executions[executionIdentifier];

            // Loop while the execution is not completed
            while (!(executionOperation.Completed))
            {
                // Refresh execution info
                executionOperation.Refresh();

                // Wait a few seconds before refreshing
                Thread.Sleep(3000);

                // Get status exceution of current packge
                Operation.ServerOperationStatus status = executionOperation.Status;
                
                if (status == Operation.ServerOperationStatus.Failed)
                {
                    throw new Exception(String.Format("The package execution failed : {0}", ssisPackage));
                }

            }

            ssisConnection.Close();

        }


        /// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
        public void Main()
        {
            // TODO: Add your code here

            try
            {

                int workerThreads = (int)Dts.Variables["WorkerThreads"].Value;
                int completionPortThreads = (int)Dts.Variables["CompletionPortThreads"].Value;

                //First parameter is the number of thread in the pool
                //Second paramter is the number of async I/O thread in the pool
                //Both value cannot be less than your logical processor count
                ThreadPool.SetMinThreads(workerThreads, completionPortThreads);
                ThreadPool.SetMaxThreads(workerThreads, completionPortThreads);

                string ssisConnectionString = Dts.Connections["SSISCSCatalog"].ConnectionString;
                string ssisFolder = Dts.Variables["User::SSISFolder"].Value.ToString();
                string ssisProject = Dts.Variables["User::SSISProject"].Value.ToString();
                string ssisEnvironment = Dts.Variables["User::SSISEnvironment"].Value.ToString();
                string[] ssisPackages = Dts.Variables["User::PackagesList"].Value.ToString().Split(',');

                CheckCatalog(ssisConnectionString, ssisFolder, ssisProject, ssisEnvironment, ssisPackages);

                List<System.Threading.Tasks.Task> tasks = new List<System.Threading.Tasks.Task>();

                bool fireAgain = true;

                foreach (string ssisPackage in ssisPackages)
                {

                    Dts.Events.FireInformation(0, "Script Task", String.Format("{0} added in threads pool", ssisPackage), String.Empty, 0, ref fireAgain);

                    System.Threading.Tasks.Task task = System.Threading.Tasks.Task.Factory.StartNew(() =>
                     {
                         ExecPackage(ssisConnectionString, ssisFolder, ssisProject, ssisEnvironment, ssisPackage);
                     });

                    tasks.Add(task);

                }

                try
                {
                    System.Threading.Tasks.Task.WaitAll(tasks.ToArray());
                }
                catch (AggregateException ae)
                {
                    foreach (var e in ae.InnerExceptions)
                    {
                        if (e is Exception)
                        {

                            Dts.Events.FireError(0, "Script Task", e.Message, String.Empty, 0);
                            Dts.TaskResult = (int)ScriptResults.Failure;

                        }
                    }
                }

                Dts.TaskResult = (int)ScriptResults.Success;

            }
            catch (Exception e)
            {
                Dts.Events.FireError(0, "Script Task", e.Message, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;

            }

        }


        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

#region Help: Introduction to the script task

/* The Script Task allows you to perform virtually any operation that can be accomplished in

* a .Net application within the context of an Integration Services control flow.

* Expand the other regions which have "Help" prefixes for examples of specific ways to use

* Integration Services features within this script task. */

#endregion

#region Namespaces

using Microsoft.SqlServer.Management.IntegrationServices;

using System;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Threading;

#endregion

namespace ST_6ed5a804b01d4911b13cd6814da233ba

{

/// <summary>

/// ScriptMain is the entry point class of the script. Do not change the name, attributes,

/// or parent of this class.

/// </summary>

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

{

#region Help: Using Integration Services variables and parameters in a script

/* To use a variable in this script, first ensure that the variable has been added to

* either the list contained in the ReadOnlyVariables property or the list contained in

* the ReadWriteVariables property of this script task, according to whether or not your

* code needs to write to the variable. To add the variable, save this script, close this instance of

* Visual Studio, and update the ReadOnlyVariables and

* ReadWriteVariables properties in the Script Transformation Editor window.

* To use a parameter in this script, follow the same steps. Parameters are always read-only.

* Example of reading from a variable:

* DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;

* Example of writing to a variable:

* Dts.Variables["User::myStringVariable"].Value = "new value";

* Example of reading from a package parameter:

* int batchId = (int) Dts.Variables["$Package::batchId"].Value;

* Example of reading from a project parameter:

* int batchId = (int) Dts.Variables["$Project::batchId"].Value;

* Example of reading from a sensitive project parameter:

* int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();

* */

#endregion

#region Help: Firing Integration Services events from a script

/* This script task can fire events for logging purposes.

* Example of firing an error event:

* Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);

* Example of firing an information event:

* Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)

* Example of firing a warning event:

* Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);

* */

#endregion

#region Help: Using Integration Services connection managers in a script

/* Some types of connection managers can be used in this script task. See the topic

* "Working with Connection Managers Programatically" for details.

* Example of using an ADO.Net connection manager:

* object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);

* SqlConnection myADONETConnection = (SqlConnection)rawConnection;

* //Use the connection in some code here, then release the connection

* Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);

* Example of using a File connection manager

* object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);

* string filePath = (string)rawConnection;

* //Use the connection in some code here, then release the connection

* Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);

* */

#endregion

private void CheckCatalog(string dataSource, string ssisFolder, string ssisProject, string ssisEnvironment, string[] ssisPackages)

{

SqlConnection ssisConnection = new SqlConnection(dataSource);

IntegrationServices integrationServices = new IntegrationServices(ssisConnection);

var connBuilder = new SqlConnectionStringBuilder(dataSource);

var ssisCatalog = connBuilder.InitialCatalog;

if (integrationServices.Catalogs.Contains(ssisCatalog))

{

var catalog = integrationServices.Catalogs[ssisCatalog];

if (!catalog.Folders.Contains(ssisFolder))

{

throw new Exception(String.Format("SSIS folder doesn't exists : {0}", ssisFolder));

}

else

{

var folder = catalog.Folders[ssisFolder];

if (!folder.Projects.Contains(ssisProject))

{

throw new Exception(String.Format("SSIS project doesn't exists : {0}", ssisProject));

}

else

{

var project = folder.Projects[ssisProject];

if (!project.References.Contains(ssisEnvironment, "."))

{

throw new Exception(String.Format("SSIS environment reference doesn't exists or is not mapped to project : {0}", ssisEnvironment));

}

else

{

EnvironmentReference environmentRef = project.References[ssisEnvironment, "."];

string invalidPackages = string.Empty;

foreach (string ssisPackage in ssisPackages)

{

if (!project.Packages.Contains(ssisPackage))

{

invalidPackages += ((String.IsNullOrEmpty(invalidPackages)) ? "SSIS package(s) not found : " : "") + ssisPackage + ",";

}

if (invalidPackages.Length > 0) throw new Exception(invalidPackages.Substring(0, invalidPackages.Length - 1));

}

private void ExecPackage(string dataSource, string ssisFolder, string ssisProject, string ssisEnvironment, string ssisPackage)

{

SqlConnection ssisConnection = new SqlConnection(dataSource);

IntegrationServices integrationServices = new IntegrationServices(ssisConnection);

var connBuilder = new SqlConnectionStringBuilder(dataSource);

var ssisCatalog = connBuilder.InitialCatalog;

Microsoft.SqlServer.Management.IntegrationServices.PackageInfo myPackage = integrationServices.Catalogs[ssisCatalog].Folders[ssisFolder].Projects[ssisProject].Packages[ssisPackage];

EnvironmentReference environmentRef = integrationServices.Catalogs[ssisCatalog].Folders[ssisFolder].Projects[ssisProject].References[ssisEnvironment, "."];

long executionIdentifier = myPackage.Execute(false, environmentRef);

ExecutionOperation executionOperation = integrationServices.Catalogs[ssisCatalog].Executions[executionIdentifier];

// Loop while the execution is not completed

while (!(executionOperation.Completed))

{

// Refresh execution info

executionOperation.Refresh();

// Wait a few seconds before refreshing

Thread.Sleep(3000);

// Get status exceution of current packge

Operation.ServerOperationStatus status = executionOperation.Status;

if (status == Operation.ServerOperationStatus.Failed)

{

throw new Exception(String.Format("The package execution failed : {0}", ssisPackage));

}

ssisConnection.Close();

}

/// <summary>

/// This method is called when this script task executes in the control flow.

/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

/// To open Help, press F1.

/// </summary>

public void Main()

{

// TODO: Add your code here

try

{

int workerThreads = (int)Dts.Variables["WorkerThreads"].Value;

int completionPortThreads = (int)Dts.Variables["CompletionPortThreads"].Value;

//First parameter is the number of thread in the pool

//Second paramter is the number of async I/O thread in the pool

//Both value cannot be less than your logical processor count

ThreadPool.SetMinThreads(workerThreads, completionPortThreads);

ThreadPool.SetMaxThreads(workerThreads, completionPortThreads);

string ssisConnectionString = Dts.Connections["SSISCSCatalog"].ConnectionString;

string ssisFolder = Dts.Variables["User::SSISFolder"].Value.ToString();

string ssisProject = Dts.Variables["User::SSISProject"].Value.ToString();

string ssisEnvironment = Dts.Variables["User::SSISEnvironment"].Value.ToString();

string[] ssisPackages = Dts.Variables["User::PackagesList"].Value.ToString().Split(',');

CheckCatalog(ssisConnectionString, ssisFolder, ssisProject, ssisEnvironment, ssisPackages);

List<System.Threading.Tasks.Task> tasks = new List<System.Threading.Tasks.Task>();

bool fireAgain = true;

foreach (string ssisPackage in ssisPackages)

{

Dts.Events.FireInformation(0, "Script Task", String.Format("{0} added in threads pool", ssisPackage), String.Empty, 0, ref fireAgain);

System.Threading.Tasks.Task task = System.Threading.Tasks.Task.Factory.StartNew(() =>

{

ExecPackage(ssisConnectionString, ssisFolder, ssisProject, ssisEnvironment, ssisPackage);

});

tasks.Add(task);

}

try

{

System.Threading.Tasks.Task.WaitAll(tasks.ToArray());

}

catch (AggregateException ae)

{

foreach (var e in ae.InnerExceptions)

{

if (e is Exception)

{

Dts.Events.FireError(0, "Script Task", e.Message, String.Empty, 0);

Dts.TaskResult = (int)ScriptResults.Failure;

}

Dts.TaskResult = (int)ScriptResults.Success;

}

catch (Exception e)

{

Dts.Events.FireError(0, "Script Task", e.Message, String.Empty, 0);

Dts.TaskResult = (int)ScriptResults.Failure;

}

#region ScriptResults declaration

/// <summary>

/// This enum provides a convenient shorthand within the scope of this class for setting the

/// result of the script.

///

/// This code was generated automatically.

/// </summary>

enum ScriptResults

{

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

};

#endregion

}

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.

3.7 Déploiement du projet SSIS dans le catalogue

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.

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

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

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

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 !

View post on imgur.com

Auteur

Sarah Béquet

Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.

Derniers articles

2019.12.02BI & Big Data Etude des contextes en DAX (4/4) : Transition de contexte
2019.11.25BI & Big Data Etude des contextes en DAX (3/4) : Contexte de requête
2019.11.17BI & Big Data Etude des contextes en DAX (2/4) : Contexte de filtre
2019.11.11BI & Big Data Etude des contextes en DAX (1/4) : Contexte de ligne

Articles liés

Quelques trucs à savoir sur Invoke-Sqlcmd

Exécuter un script sur toutes les bases de données d'une instance

SQL Data Warehouse : Kezako ?

Scripter la création d'une base données

Blog