Mesliensfavoris.fr

Problématique
Comment faire pour compter toutes les lignes à différents endroits dans le flux de données simplement et exploiter ces statistiques dans des flux de contrôle ? En d'autres termes, comment superviser le lancement des packages sous Microsoft SQL Server Integration Services de façon quantitative ?


Nous pouvons utiliser le composant SSIS (présent dans la version 2005 et 2008) pour compter toutes les lignes de chaque destination OLE DB par exemple.

Cet exemple utilise est fait avec la version 2008 :

-- Créons une table EMPLOYEE simple pour commencer
-- La table contient le nom et le poste du salarié
USE DATABASE_SAMPLE;


IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'EMPLOYEES' AND type = 'U')
DROP TABLE EMPLOYEES
GO


IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'SALES_EMPLOYEES' AND type = 'U')
DROP TABLE SALES_EMPLOYEES
GO

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'MARKETING_EMPLOYEES' AND type = 'U')
DROP TABLE MARKETING_EMPLOYEES
GO

CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID INTEGER PRIMARY KEY,
JOB_TITLE NVARCHAR(20)
)


CREATE TABLE SALES_EMPLOYEES
(
EMPLOYEE_ID INTEGER PRIMARY KEY
)


CREATE TABLE MARKETING_EMPLOYEES
(
EMPLOYEE_ID INTEGER PRIMARY KEY
)

Insérons quelques lignes dans la table des employés :

INSERT INTO EMPLOYEES VALUES (1, 'Marketing Manager');
INSERT INTO EMPLOYEES VALUES (2, 'Marketing Assistant');
INSERT INTO EMPLOYEES VALUES (3, 'Marketing Specialist');
INSERT INTO EMPLOYEES VALUES (4, 'Marketing Assistant');
INSERT INTO EMPLOYEES VALUES (5, 'Marketing Assistant');
INSERT INTO EMPLOYEES VALUES (6, 'Marketing Specialist');
INSERT INTO EMPLOYEES VALUES (7, 'Marketing Specialist');
INSERT INTO EMPLOYEES VALUES (8, 'Marketing Specialist');
INSERT INTO EMPLOYEES VALUES (9, 'Marketing Specialist');
INSERT INTO EMPLOYEES VALUES (10, 'Marketing Manager');
INSERT INTO EMPLOYEES VALUES (11, 'Marketing Assistant');
INSERT INTO EMPLOYEES VALUES (12, 'Marketing Specialist');
INSERT INTO EMPLOYEES VALUES (13, 'Marketing Assistant');
INSERT INTO EMPLOYEES VALUES (14, 'Marketing Assistant');
INSERT INTO EMPLOYEES VALUES (15, 'Marketing Specialist');
INSERT INTO EMPLOYEES VALUES (16, 'Marketing Specialist');
INSERT INTO EMPLOYEES VALUES (17, 'Marketing Specialist');
INSERT INTO EMPLOYEES VALUES (18, 'Marketing Specialist');
INSERT INTO EMPLOYEES VALUES (19, 'Sales Manager');
INSERT INTO EMPLOYEES VALUES (20, 'Sales Representative');
INSERT INTO EMPLOYEES VALUES (21, 'Sales Representative');
INSERT INTO EMPLOYEES VALUES (22, 'Sales Manager');
INSERT INTO EMPLOYEES VALUES (23, 'Sales Manager');
INSERT INTO EMPLOYEES VALUES (24, 'Sales Representative');
INSERT INTO EMPLOYEES VALUES (25, 'Sales Representative');
INSERT INTO EMPLOYEES VALUES (26, 'Sales Manager');
INSERT INTO EMPLOYEES VALUES (27, 'Sales Director');
INSERT INTO EMPLOYEES VALUES (28, 'Sales Representative');
INSERT INTO EMPLOYEES VALUES (29, 'Sales Representative');
INSERT INTO EMPLOYEES VALUES (30, 'Sales Representative');
INSERT INTO EMPLOYEES VALUES (31, 'Sales Representative');
INSERT INTO EMPLOYEES VALUES (32, 'Sales Assistant');
Admettons que nous voulions compter le nombre de lignes insérées dans chaque table (SALES_EMPLOYEES et MARKETING_EMPLOYEES). Pour se connecter à notre base et à nos tables crées précédemment il faut créer une connexion dont la connexion ressemble à cette chaîne : "Data Source=SERVEUR;Initial Catalog=DATABASE_SAMPLE;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" Utiliser l'authentification Windows implique que l'utilisateur qui lance le package à les droits de connexions à la base de données.



Dans un nouveau package SSIS créé dans Visual Studio nous créons un simple flux : "Tâche de flux de données".





Créons maintenant une table de statistiques sur les chargements effectués dans notre flux de données : IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'STATISTIQUES' AND type = 'U')
DROP TABLE STATISTIQUES
GO

CREATE TABLE STATISTIQUES
(
NOM_TABLE NVARCHAR(50),
LIGNES_INSEREES INTEGER,
DATE_INSERTION DATETIME
)


Pour remplir cette table il faut utiliser le composant "Nombre de lignes" ou "Row COunt" en Anglais disponible dans SSIS.
Ce composant est à insérer avant l'élément à Auditer, comme suit :

Ce composant Row Count nécessite la création de variables locales, en pratique une par "chemin à suivre" dans le flux SSIS.



Une fois ces étapes réalisées, il faut utiliser ces variables instanciées par les deux composants "Nombre de lignes" créés dans le flux de données. Créons un composant SQL pour insérer les lignes comme suit :


Pour récupérer la valeur des variables de façon dynamique il faut insérer la requête dans la partie Expression du composant et non dans la partie de saisie directe. Il faut sélectionner le champs SQLStatementSource dans l'Onglet Expressions. Et insérer une requête construite avec les variables en deux étapes :


Une fois l'exécution terminée les lignes sont insérées de la même façon dans la base de données SQL Server que pour la toute première version du flux de donnés (Data-Flow).


Faisons une requête pour examiner le résultat :



Conclusion

Sous SQL Server et dans tous les autres ETL du marché, (Informatica, Talend, Genio) la partie monitoring et supervision des traitements et des données insérées est primordiale, sous SQL Server toutes ces informations doivent être suivies et stockées de façon manuelle, c'est à dire que l'implémentation d'une solution est nécessaire et qu'aucun outil natif ne permet la supervision.

En outre l'utilisation du composant nombre de lignes est un outil très pratique et facile à mettre en œuvre pou l'alimentation de tables de suivi personnalisées.