ssis 2017 développeurs sql server sql server integration … · 2018-07-23 · issn : 1960-3444...

ISSN : 1960-3444ISBN : 978-2-409-01489-5

54 €

Pour plus d’informations :

SQL Server Integration Services 2017Ce livre sur SSIS 2017 s’adresse autant aux développeurs qui débutent avec SQL Server Integration Services et qui sont à la recherche d’informations sur l’utilisation de la plateforme, qu’à ceux en quête de bonnes pratiques leur permettant de parfaire leurs connaissances. Néanmoins, le lecteur devra disposer de bonnes bases sur le langage SQL, sur le fonctionnement d’une base de données, sur l’administration Azure ou encore sur la gestion des serveurs Windows.Le livre se concentre sur les éléments de SSIS réellement utiles dans la mise en place de projets ETL afin de rendre le lecteur plus efficace dans ses recherches et ses développements. Il est à la fois un guide théorique sur l’utilisation des composants de SSIS et un recueil de bonnes pratiques de développement, issues de l’expérience significative des auteurs dans ce domaineLes deux premiers chapitres présentent SSIS dans sa globalité au travers des grands principes de l’ETL, ainsi que les nouveautés apportées par la version 2017, notamment l’intrication avec la plateforme Azure. Les auteurs s’attachent ensuite à présenter les composants les plus utilisés dans les projets décisionnels, à la fois dans le Flux de Contrôle et le Flux de Données, et détaillent un exemple d’implémentation d’un lot SSIS. Les patterns les plus récurrents et ceux offrant de bonnes performances et utilisant de bonnes pratiques de conception sont également étudiés. Avec un chapitre sur les concepts de développement avancés, le lecteur trouvera une boîte à outils pour la résilience des développements (points de contrôle, transactions). Les tâches post-développe-ment comme l’administration, le déploiement, la journalisation ou encore la sécurité sont ensuite détaillées. Pour finir, les deux derniers chapitres sont consacrés à l’optimisation des packages via le paramétrage des aspects serveurs et au développement de ses propres composants SSIS.Des éléments complémentaires sont en téléchargement sur le site www.editions-eni.fr.

Patrice HAREL et Charles-Henri SAUGET interviennent sur des projets Data Platform depuis plus de 10 ans. Dans ce cadre, ils sont amenés à mettre en place des projets intégrant SQL Server Integration Services. Charles-Henri Sauget est reconnu Microsoft MVP (Most Valuable Professional) Data Platform. L’expérience de terrain des auteurs ainsi que leurs nom-breuses contributions en plus de leur activité de consulting (formations professionnelles ou en universités, conférences) permettent de proposer aux lecteurs un livre complétement opérationnel sur la mise en œuvre de projets SSIS.

Avant-propos • Introduction à SSIS • Nouveautés SSIS • Les bases de l’ETL • Concepts de développement avancés • Déploiement et administration • Notions avancées et optimisations • Bases de la programmation de composants

Les chapitres du livre

SQL

Serv

er In

tegr

atio

n Se

rvic

es 2

017

Téléchargementwww.editions-eni.fr.fr

sur www.editions-eni.fr : b Le code source des projets.

SQL Server Integration

Services 2017

Romuald COUTAUD Patrice HAREL François JEHL

Charles-Henri SAUGET

Mise en œuvre d’un projet ETL avec SSIS

1Table des matières

Avant-propos

Chapitre 1Introduction à SSIS

1. Présentation de SSIS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111.1 Concepts ETL et ELT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111.2 Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.3 Les éditions SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

2. Installation et outils . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172.1 Installation de SSIS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

2.1.1 Installation étape par étape de SSIS . . . . . . . . . . . . . . . . . 182.1.2 Installation en ligne de commande de SSIS . . . . . . . . . . . 212.1.3 Finalisation de l'installation . . . . . . . . . . . . . . . . . . . . . . . . 24

2.2 Installation des outils de développement . . . . . . . . . . . . . . . . . . 27

Chapitre 2Nouveautés SSIS

1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

2. Installation et administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292.1 Installation sur Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292.2 Installation distribuée (Scale Out) . . . . . . . . . . . . . . . . . . . . . . . 33

2.2.1 Architecture générale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332.2.2 Installation d'un nœud Master . . . . . . . . . . . . . . . . . . . . . 352.2.3 Installation d'un Worker . . . . . . . . . . . . . . . . . . . . . . . . . . 382.2.4 Configuration et surveillance du cluster . . . . . . . . . . . . . . 41

Les éléments à télécharger sont disponibles à l'adresse suivante :http://www.editions-eni.fr

Saisissez la référence de l'ouvrage EP17SSIS dans la zone de recherche et validez. Cliquez sur le titre du livre puis sur le bouton de téléchargement.

lcroise

Tampon

2Mise en œuvre d'un projet ETL avec SSIS

SSIS 2017

2.3 Support Always-On de la base SSISDB. . . . . . . . . . . . . . . . . . . . 452.4 Nouveau rôle SSIS_logreader . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472.5 Personnalisation du niveau de journalisation . . . . . . . . . . . . . . . 472.6 Support d'Always Encrypted . . . . . . . . . . . . . . . . . . . . . . . . . . . . 502.7 Déploiement incrémental des packages . . . . . . . . . . . . . . . . . . . 522.8 Choix de la version de déploiement . . . . . . . . . . . . . . . . . . . . . . 54

3. Composants et tâches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553.1 Les tâches Cloud . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55

3.1.1 Tâche Azure Blob Download Task . . . . . . . . . . . . . . . . . . 573.1.2 Tâche Azure Blob Upload Task . . . . . . . . . . . . . . . . . . . . . 613.1.3 Tâche Azure HDInsight Create Cluster . . . . . . . . . . . . . . 623.1.4 Tâche de suppression d'un cluster Azure HDInsight. . . . 673.1.5 Tâche Hive Azure HDInsight . . . . . . . . . . . . . . . . . . . . . . 683.1.6 Tâche Azure HDInsight Pig. . . . . . . . . . . . . . . . . . . . . . . . 723.1.7 Tâche de chargement

Azure SQL Data Warehouse (DW) . . . . . . . . . . . . . . . . . . 743.1.8 Tâche de système de fichiers Azure Data Lake Store. . . . 80

3.2 Nouveaux énumérateurs Foreach . . . . . . . . . . . . . . . . . . . . . . . . 823.2.1 Énumérateur HDFS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 823.2.2 Énumérateur ADLS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 833.2.3 Énumérateur Azure Blob . . . . . . . . . . . . . . . . . . . . . . . . . . 84

3.3 Composants Cloud de flux de données. . . . . . . . . . . . . . . . . . . . 863.3.1 Source Azure Blob . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 863.3.2 Destination Azure Blob . . . . . . . . . . . . . . . . . . . . . . . . . . . 903.3.3 Source Azure Data Lake Store . . . . . . . . . . . . . . . . . . . . . . 913.3.4 Destination Azure Data Lake Store . . . . . . . . . . . . . . . . . 92

3.4 Les composants Big Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 933.4.1 Tâche du système de fichiers Hadoop. . . . . . . . . . . . . . . . 953.4.2 Tâche Hadoop Hive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 973.4.3 Tâche Hadoop Pig. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 983.4.4 Composant source de fichier HDFS . . . . . . . . . . . . . . . . . 993.4.5 Composant destination de fichier HDFS . . . . . . . . . . . . 100


3.5 Les autres composants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1013.5.1 Composant de publication de flux de données. . . . . . . . 1013.5.2 Distributeur de données équilibrées . . . . . . . . . . . . . . . . 1043.5.3 Les parties de package . . . . . . . . . . . . . . . . . . . . . . . . . . . 1053.5.4 Déploiement SSIS dans Azure . . . . . . . . . . . . . . . . . . . . . 108

Chapitre 3Les bases de l’ETL

1. Présentation et installation de la solution de développement de ce livre . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

2. Les bases avant une première implémentation. . . . . . . . . . . . . . . . . 1142.1 Généralités . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1142.2 Le flux de contrôle : généralités . . . . . . . . . . . . . . . . . . . . . . . . . 114

2.2.1 Définition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1142.2.2 Le concepteur de flux de contrôle . . . . . . . . . . . . . . . . . . 115

2.3 Le flux de données : généralités . . . . . . . . . . . . . . . . . . . . . . . . . 1172.3.1 Objectifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1172.3.2 Le concepteur de flux de données . . . . . . . . . . . . . . . . . . 118

2.4 Contraintes de précédence versus Pipeline . . . . . . . . . . . . . . . . 1202.4.1 Contraintes de précédence . . . . . . . . . . . . . . . . . . . . . . . . 1202.4.2 Pipeline : les bases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125

2.5 Gestionnaires de connexions . . . . . . . . . . . . . . . . . . . . . . . . . . . 1272.5.1 Définition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1272.5.2 Configuration d'un gestionnaire

de connexions pour base de données. . . . . . . . . . . . . . . . 1272.5.3 Configuration d'un gestionnaire

de connexions vers des fichiers plats . . . . . . . . . . . . . . . . 1322.6 Les variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138


SSIS 2017

3. Première implémentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1403.1 Conteneurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140

3.1.1 Principes de fonctionnement . . . . . . . . . . . . . . . . . . . . . . 1403.1.2 Conteneur de boucles ForEach . . . . . . . . . . . . . . . . . . . . 1423.1.3 Conteneur de boucles For . . . . . . . . . . . . . . . . . . . . . . . . 1453.1.4 Portée et cloisonnement. . . . . . . . . . . . . . . . . . . . . . . . . . 146

3.2 Les expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1473.2.1 Générateur d'expression . . . . . . . . . . . . . . . . . . . . . . . . . . 1483.2.2 Les bases de la syntaxe . . . . . . . . . . . . . . . . . . . . . . . . . . . 1493.2.3 Expressions et gestionnaire de connexions . . . . . . . . . . . 150

3.3 Extraire les données : composants source . . . . . . . . . . . . . . . . . 1533.3.1 Composant Source du fichier plat (Flat File Source) . . . 1543.3.2 Composant Source OLE DB. . . . . . . . . . . . . . . . . . . . . . . 1563.3.3 Éditeur avancé des composants source . . . . . . . . . . . . . . 158

3.4 Transformer les données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1613.4.1 Colonne dérivée (Derived column) . . . . . . . . . . . . . . . . . 1613.4.2 Composant Recherche (Lookup) . . . . . . . . . . . . . . . . . . . 1623.4.3 Composant Transformation du cache . . . . . . . . . . . . . . 169

3.5 Charger les données : composants destination. . . . . . . . . . . . . 1703.6 Entrée et sortie des composants . . . . . . . . . . . . . . . . . . . . . . . . 174

3.6.1 Définition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1743.6.2 Fractionnement conditionnel . . . . . . . . . . . . . . . . . . . . . 1743.6.3 Éditeur du chemin d'accès au flux de données . . . . . . . . 177

3.7 Les types de données sur SSIS . . . . . . . . . . . . . . . . . . . . . . . . . . 180

4. Patterns de développement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1824.1 Éviter le composant Tri . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1824.2 Éviter le composant OLE DB Command . . . . . . . . . . . . . . . . . 187

4.2.1 Configuration du composant OLE DB Command. . . . . 1874.2.2 Configuration de la Tâche d'exécution

de requêtes SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1904.3 Jointure SQL versus composant Jointure de fusion . . . . . . . . . 194

4.3.1 Composant Jointure de fusion (Merge Join) . . . . . . . . . 1944.3.2 Composant Fusionner . . . . . . . . . . . . . . . . . . . . . . . . . . . 198


4.4 Stocker temporairement des données . . . . . . . . . . . . . . . . . . . . 1994.4.1 En base de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1994.4.2 Destination de fichier brut. . . . . . . . . . . . . . . . . . . . . . . . 199

4.5 Pattern Ignorer l'échec pour le composant Recherche . . . . . . . 2014.5.1 Composant Unir tout. . . . . . . . . . . . . . . . . . . . . . . . . . . . 2024.5.2 Ignorer l'échec et colonne dérivée . . . . . . . . . . . . . . . . . . 204

4.6 Lot SSIS Parent-Enfant. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2044.6.1 Référence du projet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2054.6.2 Référence externe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207

Chapitre 4Concepts de développement avancés

1. Gestion de la qualité et du changement dans les données . . . . . . . . 2111.1 Data Quality Services (DQS). . . . . . . . . . . . . . . . . . . . . . . . . . . 211

1.1.1 Considérations sur DQS . . . . . . . . . . . . . . . . . . . . . . . . . 2111.1.2 Nettoyage DQS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213

1.2 CDC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2151.2.1 Qu'est-ce que le CDC ? . . . . . . . . . . . . . . . . . . . . . . . . . . 2151.2.2 Mise en place du CDC . . . . . . . . . . . . . . . . . . . . . . . . . . . 216

2. Interactions avec SQL Server et SSAS. . . . . . . . . . . . . . . . . . . . . . . . 2212.1 Insertion en bloc . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2212.2 Tâches de maintenance et de transfert

d'objets de la base de données . . . . . . . . . . . . . . . . . . . . . . . . . . 2242.3 Tâches relatives à SQL Server Analysis Services (SSAS) . . . . . 225

2.3.1 Tâche de traitement Analysis Services . . . . . . . . . . . . . . 2252.3.2 Tâche d’exécution de DDL Analysis Services . . . . . . . . . 2272.3.3 Tâche de requête de Data Mining . . . . . . . . . . . . . . . . . . 228

3. Checkpoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2303.1 Principe général. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230

3.1.1 Cas d'usage classique . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2303.1.2 Mise en place. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2323.1.3 Lien entre échec et création de checkpoint . . . . . . . . . . . 232


SSIS 2017

3.1.4 Fonctionnement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2343.2 Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236

3.2.1 Conteneurs mal gérés . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2363.2.2 Mauvaise gestion du parallélisme . . . . . . . . . . . . . . . . . . 2373.2.3 Gestionnaires d’évènements . . . . . . . . . . . . . . . . . . . . . . 239

3.3 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239

4. Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2394.1 Principe des transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239

4.1.1 A.C.I.D. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2394.1.2 Transactions distribuées . . . . . . . . . . . . . . . . . . . . . . . . . 2404.1.3 Cas du DTC en réseau . . . . . . . . . . . . . . . . . . . . . . . . . . . 242

4.2 Mise en place d’une transaction MSDTC dans SSIS . . . . . . . . 2434.2.1 TransactionOption pour activer les transactions. . . . . . 2434.2.2 IsolationLevel pour gérer l’interaction

avec les autres transactions . . . . . . . . . . . . . . . . . . . . . . . 2434.2.3 Comportement à l’exécution. . . . . . . . . . . . . . . . . . . . . . 2454.2.4 Analyse des problèmes . . . . . . . . . . . . . . . . . . . . . . . . . . . 2474.2.5 Conseils de design de Control Flow

dans un cas transactionnel. . . . . . . . . . . . . . . . . . . . . . . . 2484.3 Utilisation de transactions natives . . . . . . . . . . . . . . . . . . . . . . 249

4.3.1 BEGIN/ROLLBACK/COMMIT . . . . . . . . . . . . . . . . . . . 2494.3.2 Implémentation avec des Execute SQL Task

et RetainSameConnection . . . . . . . . . . . . . . . . . . . . . . . . 2494.3.3 Bonnes pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252

4.4 Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252

Chapitre 5Déploiement et administration

1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253

2. Le catalogue SSISDB. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2532.1 Configuration du catalogue . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2552.2 Rapports de supervision . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256


3. Les objets du catalogue SSIS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2583.1 Dossier . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2583.2 Environnement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2603.3 Projet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262

3.3.1 Les fichiers de déploiement ISPAC . . . . . . . . . . . . . . . . . 2623.3.2 Déployer à l'aide de l'assistant . . . . . . . . . . . . . . . . . . . . . 2643.3.3 Déployer en T-SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2683.3.4 Déployer en PowerShell . . . . . . . . . . . . . . . . . . . . . . . . . . 2703.3.5 Configuration de projet . . . . . . . . . . . . . . . . . . . . . . . . . . 271

3.4 Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2753.4.1 Exécution de package . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2753.4.2 Planification de package . . . . . . . . . . . . . . . . . . . . . . . . . . 279

4. La sécurité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2834.1 Sécurité niveau SSISDB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2844.2 Sécurité niveau dossier . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2854.3 Sécurité niveau Projet. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2864.4 Sécurité niveau Environnement . . . . . . . . . . . . . . . . . . . . . . . . 286

Chapitre 6Notions avancées et optimisations

1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289

2. Les fondements du flux de données . . . . . . . . . . . . . . . . . . . . . . . . . 2892.1 La mémoire tampon (buffer) . . . . . . . . . . . . . . . . . . . . . . . . . . . 2902.2 Execution Tree et threading. . . . . . . . . . . . . . . . . . . . . . . . . . . . 2922.3 Les types de composants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2962.4 Incidences sur les performances. . . . . . . . . . . . . . . . . . . . . . . . . 300

3. Mesure des performances et diagnostic . . . . . . . . . . . . . . . . . . . . . . 3023.1 Utilisation des journaux SSISDB. . . . . . . . . . . . . . . . . . . . . . . . 3023.2 Utilisation de rapport SSIS . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3073.3 Journalisation personnalisée . . . . . . . . . . . . . . . . . . . . . . . . . . . 3083.4 Utilisation du moniteur de performance . . . . . . . . . . . . . . . . . 310


SSIS 2017

4. Des packages performants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3124.1 Utilisation des moteurs relationnels . . . . . . . . . . . . . . . . . . . . . 3124.2 Particularités sur les Lookup . . . . . . . . . . . . . . . . . . . . . . . . . . . 3154.3 Particularités de Fusion et Jointure de fusion . . . . . . . . . . . . . . 318

Chapitre 7Bases de la programmation de composants

1. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3231.1 Pourquoi développer un nouveau composant ? . . . . . . . . . . . . 3231.2 Possibilités offertes par les composants personnalisés . . . . . . . 324

2. Pour bien démarrer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3252.1 Quelques notions avant de commencer . . . . . . . . . . . . . . . . . . 3252.2 Éléments à installer sur le poste de développement. . . . . . . . . 3282.3 Configurer un projet de bibliothèque . . . . . . . . . . . . . . . . . . . . 329

3. Exemple d’une tâche de flux de contrôle . . . . . . . . . . . . . . . . . . . . . 3333.1 Tâche personnalisée d’envoi d'e-mail . . . . . . . . . . . . . . . . . . . . 3333.2 Rappels sur les composants disponibles . . . . . . . . . . . . . . . . . . 3343.3 Héritage et métadonnées . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3363.4 Vérification de déploiement. . . . . . . . . . . . . . . . . . . . . . . . . . . . 3373.5 Ajouter des propriétés de configuration . . . . . . . . . . . . . . . . . . 3383.6 Implémenter la validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3393.7 La méthode Initialize . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3403.8 Réaliser un traitement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3413.9 Gestionnaire de connexions personnalisées . . . . . . . . . . . . . . . 3423.10 Interface utilisateur du gestionnaire de connexions . . . . . . . . 345

4. Log Provider, ouverture, logging et fermeture du log. . . . . . . . . . . . 3484.1 Création d’un Log Provider . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3484.2 Interface utilisateur du Log Provider . . . . . . . . . . . . . . . . . . . . . 351

5. Énumérer sur toute collection avec un ForEachEnumerator . . . . . . 3525.1 Création d’un ForEachEnumerator . . . . . . . . . . . . . . . . . . . . . . 3525.2 Interface utilisateur du ForeachEnumerator. . . . . . . . . . . . . . . 353


6. Sources, destinations et transformations personnalisées. . . . . . . . . 3556.1 Cas d’usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3566.2 Préparation de la solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3576.3 Développement de composant source. . . . . . . . . . . . . . . . . . . . 358

7. Débogage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3667.1 Débogage des composants personnalisés . . . . . . . . . . . . . . . . . 3667.2 Débogage de l’interface graphique. . . . . . . . . . . . . . . . . . . . . . . 368

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371

Chapitre 3

Les bases de l’ETL

Les bases de l’ETL1. Présentation et installation de la solution de développement de ce livre

Une partie des exemples présentés dans cet ouvrage sont disponibles en télé-chargement. Afin de pouvoir les exploiter, il est indispensable de disposer del’environnement matériel et logiciel ci-dessous. Si ce n'est pas le cas, il est toutà fait possible de parcourir l'ouvrage et alors seule la partie pratique en seralimitée.

– une machine 4 cœurs, 8 Go de RAM, disque SSD

– SQL Server 2017 Developer/Enterprise Edition

– Visual Studio SSDT 2015

Cet ouvrage ne couvre pas les étapes d'installation de SQL Server ni de VisualStudio. Des références externes pourront être utilisées.

lcroise

Tampon

© E

dit

ions

EN

I -

All r

ights

rese

rved


SSIS 2017

2. Les bases avant une première implémentation

2.1 Généralités

Avant de procéder au moindre développement, il est nécessaire de fixerquelques points de définition. Généralement, Integration Services est qualifiéd'outil d’extraction, de transformation et de chargement de données, ce quiest juste, mais cette description simpliste limite la portée réelle de cettesolution.

Un outil d’ETL complet se doit de fournir des fonctionnalités d’orchestration,c’est-à-dire d’ordonnancement des tâches, pour traiter tout le cycle d’intégra-tion des données. Par exemple, avant de charger un fichier, il est fréquent dedevoir le copier depuis un serveur, pour éventuellement, une fois les donnéesrécupérées, notifier tel ou tel opérateur, ou lancer un traitement externe.

Il doit également fournir un système de journalisation des exécutions quipermet de suivre la bonne avancée des traitements et de tracer les éventuelleserreurs, que ce soit pendant la phase de développement ou bien lorsque la so-lution est en production.

2.2 Le flux de contrôle : généralités

2.2.1 Définition

Le flux de contrôle (Control Flow) est ce que l'on peut désigner comme lepoint d'entrée, la couche externe d'un lot SSIS. C'est à partir de ce dernier quetout ce qui doit être exécuté dans un lot le sera. Il encapsule d'autres élémentscomme l'indispensable flux de données (Data Flow), le Gestionnaire d’Évène-ments, les Paramètres. C'est lui qui offre une vision complète sur tout ce queréalise un lot SSIS en termes d'exécution. Il est donc le premier objet auquelles développeurs ont à faire. Il est possible de le comparer à une fonctionMain() des langages de développement comme le C#.

Les principaux objets qui le composent sont appelés des Tâches et ce n'est paspar hasard, car le flux de contrôle peut également être perçu comme un ordon-nanceur de tâches.

115Les bases de l’ETLChapitre 3

C'est tout naturellement que l'on trouve dans le flux de contrôle les structuresde code que l'on peut voir dans la plupart des langages comme les boucles oules expressions conditionnelles. Pour ces dernières, il ne faut pas parler deTâches, mais respectivement de Conteneurs et de Contraintes de précédence.

2.2.2 Le concepteur de flux de contrôle

Dans Visual Studio, la boîte à outils, qui se trouve très probablement sur lagauche de l'écran, permet de visualiser deux des trois types d'objets mention-nés précédemment : les Tâches ainsi que les Conteneurs. Si la boîte à outilsn'est pas visible, elle est accessible via le menu SSIS - Boîte à outils SSIS dela barre de navigation principale de Visual Studio.

© E

dit

ions

EN

I -

All r

ights

rese

rved


SSIS 2017

Le contenu de la boîte à outils change en fonction de la section où l'on setrouve dans le lot SSIS. Il faut ici veiller à être dans la section flux de contrôlepour avoir la même chose que sur l'image ci-dessus. C'est le premier onglet dela fenêtre de conception du lot SSIS.

La classification au travers des répertoires Favoris, Commun, Azure, Conte-neurs, Autres Tâches et Parties de package permet une meilleure lisibilité.Le contenu de Favoris et Commun n'est pas immuable et il est tout à faitpossible de l'adapter en fonction des habitudes de développement.

Dans le bas de la boîte à outils, une aide donne une brève description de lafonction de l'objet mis en surbrillance par un simple clic. Elle offre égalementla possibilité de chercher des exemples d'utilisation au travers de MSDN en cli-quant sur Rechercher des exemples.

Les objets de la boîte à outils sont déplacés vers l'espace de conception aumoyen d'un double clic ou bien d'un glisser-déposer. Il est possible de faireglisser plusieurs fois le même objet, dans ce cas Visual Studio va suffixer lenom par défaut d'un incrément. Il est conseillé de ne pas conserver les nomsfournis par défaut, mais de décrire au moyen d'un texte court la fonction dechacune des tâches qui sont utilisées.

117Les bases de l’ETLChapitre 3

Le flux de contrôle ci-dessus propose un aperçu complet de tous les typesd’objets utilisables et dont la fonctionnalité sera détaillée par la suite : Tâche(EST - Truncate stg Wifi, DFT - stg Wifi), Conteneur (FEL - FichierWifi) etContrainte de précédence (les flèches). Il est accessible dans les fichiers de lasolution téléchargeable sous le projet Chapitre 3 et son nom est 001 -HotspotCollecte.dtsx (se référer à la section Présentation et installation de lasolution de développement de ce chapitre).

Pour des raisons de simplicité de lecture ainsi que par convention, il est préfé-rable de débuter chaque développement SSIS par en haut et de le compléterverticalement vers le bas.

2.3 Le flux de données : généralités

2.3.1 Objectifs

Le flux de données permet de faire transiter les données d'un point à un autre,d'une forme vers une autre, tout en réalisant des transformations sur ces der-nières si nécessaire. Dit comme cela, il est facile de se rendre compte que cettedéfinition du flux de données colle parfaitement à celle qui a été donnée del'ETL au début de ce chapitre. Il faut donc considérer le flux de donnéescomme la partie ETL de SSIS.

Le flux de données constitue une tâche particulière du flux de contrôle. Unflux de contrôle aura donc la possibilité d'exécuter plusieurs flux de données.Cette remarque met en exergue l'une des grandes différences qui existe entreflux de données et contrôle de flux de contrôle : le premier fait transiter desdonnées tandis que le second ordonnance et exécute les tâches responsables dela gestion des données. Le flux de contrôle ne gère pas l'extraction, la transfor-mation et le chargement des données directement : ce n'est que l'ordonnan-ceur de l'ETL. Cette distinction aura son importance lorsque la distinctionentre les flèches visibles dans le flux de données et celles du flux de contrôlesera faite.

© E

dit

ions

EN

I -

All r

ights

rese

rved


SSIS 2017

2.3.2 Le concepteur de flux de données

Tout comme pour le flux de contrôle, la réalisation d'un flux de données passepar l'utilisation d'une boîte à outils et d'une fenêtre de conception. Commeévoqué précédemment, le contenu de la boîte à outils sera fonction de l'endroitoù l'on se trouve dans Visual Studio. Après s'être assuré de se trouver dansl'éditeur de flux de données, second onglet de la fenêtre de développement dulot SSIS, voici le contenu de la boîte à outils.

Ici, il n'est plus question de Tâche ou de Conteneur, mais uniquement deComposant. Ces derniers sont de quatre types : Azure, Source, Transforma-tion et Destination. Comme pour le flux de contrôle, les sections Favoris etCommun sont personnalisables en fonction des habitudes de développement.Hormis pour le répertoire Azure, il faut identifier dans Source, Transforma-tion et Destination, les grands principes de l'ETL.

ssis 2017 développeurs sql server sql server integration … · 2018-07-23 · issn : 1960-3444...

Documents