Université Mohammed Première ‫جامــــعة محمـــذ األول بــوجذة‬

Faculté Pluridisciplinaire De Nador ‫كلــية متعذدة التخصصــــات الناظـــــــور‬

Département Informatique

Master de recherche
Master Sciences des Données et Systèmes
Intelligents (MSDSI’23)

Entrepôt de données
Travaux Pratiques
Atelier – 1 : SQL Server Integration Services 2019
(SSIS)
Présenté par
Pr. EL ALLALI Naoufal
Année Universitaire
2022/2023
 À propos de cet atelier

SQL Server Integration Services (SSIS) constitue notamment un outil ETL puissant de MICROSOFT destiné
aux projets d'intégration ou de BI. Avec cette formation tout à fait pratique, vous allez apprendre à mettre en
œuvre une solution ETL avec SSIS sous le SQL Server Data Tools (SSDT) de Visual Studio 2019.

Pr. Naoufal EL ALLALI

 Objectifs

 Dans cet atelier, nous mettons l'accent sur :

 SQL Server
 SSMS
 SSDT
 Visual studio
 Mettre en place la base de données AdventureworksDW
 Développement de paquets SSIS pour l'extraction, la transformation et le chargement de données.

 Objectifs pédagogiques

 A la fin de cet atelier SSIS, vous aurez acquis les connaissances et les

compétences nécessaires pour mettre en œuvre les méthodes ETL de base.

 Vous allez acquérir les compétences pour mettre en œuvre des tâches simples ou

complexes..

Pr. Naoufal EL ALLALI

 Prérequis

 Connaissances en base de données relationnelle et T-SQL

 Savoir administrer SQL Server

 Configuration Matérielle requise : - Mémoire Minumum 2024 Mo - Processeur Minimum - Processeur x86 1.0 GHz -

Processeur x64 1.4 GHz - Disque Munimum 30 Gb

 Configuration Logicielle requise : - Système d’Exploitation : Windows 7,8,10 ou de préférence Server , version 64 bits

recommandée - SQL Server 2012/2014/2016/2019 : ne pas utiliser l’édition express, de préférence qu’elle soit une version

Entreprise ou encore Developpeur - SQL Server Data Tools (SSDT) - SQL Server Management Studio (SSM/) - NET

Framework - MS Excel

Pr. Naoufal EL ALLALI

 Atelier - 1

SQL Server Integration Services- SSIS

 Mise en place du serveur SQL

 Que faire de SSIS ?
 C'est quoi l'intégration des données ?
 Pourquoi SSIS ?
 Comment fonctionne SSIS
 Pré-requis pour SQL Server Integration Service
 C’est quoi le paquet SSIS ?
 Tâches SSIS
 Atelier - 1

Mise en place du SQL Server

 Conditions nécessaires pour l’installation de SQL Server Integration Services

 Les prérequis pour l'installation de SQL Server Integration Services sont les suivants :

 Installer le SQL Server

 Installer les outils de SQL Server Data ( SSDT)

7
Pr. Naoufal EL ALLALI
Microsoft SQL Server 2019
c) Transformation ( SSIS)
 Composants de transformations (flux de données)

I. les composants de flux de données (Transformation) :

 les composants de transformations de flux de données permettant de faire :
⁃ des agrégations;
⁃ de trier;
⁃ de fusionner;
⁃ des modifications;
⁃ des jonctions;
⁃ des nettoyages de données et des distributions de données.
 En plus de cela, il existe une transformation importante et puissante dans SSIS appelée transformation Lookup
pour effectuer des opérations de recherche.

10
Pr. Naoufal EL ALLALI
 Composants de transformations (flux de données)

a) les composants de flux de données :

 Le tableau ci-dessous représente une liste des composants de transformations SSIS qui permettront d'effectuer
des opérations de Business Intelligence telles que :
⁃ l'exploration de données ( data mining)
⁃ la mise à jour et le nettoyage des données.

11
Pr. Naoufal EL ALLALI
 Composants de transformations (flux de données)

a) les composants de flux de données :

 Le tableau ci-dessous représente une liste des composants de transformations SSIS qui permettront d'effectuer
des opérations de Business Intelligence telles que :
SIS Transformations Description

Cette transformation permet de coordonner l'insertion et la mise à jour des enregistrements dans les tables
Slowly Changing Dimension
de dimension de l'entrepôt de données.

Fuzzy Grouping Transformation Il sert à remplacer les mots erronés par des mots appropriés, par la technique du regroupement.

Cette transformation SSIS utilise la mise en correspondance floue pour trouver une ou plusieurs
Fuzzy Lookup Transformation correspondances proches présentes dans la table de référence et remplacer les données sources par des
données de référence.

Cette transformation est utilisée pour extraire les termes du texte présent dans les données sources. Cette
transformation offre quatre options différentes. Ce sont : Extraire les noms uniquement, Extraire les phrases
Term Extraction Transformation
du nominal uniquement, Extraire les noms et les phrases du nominaux, et Onglet d'exclusion.

Il utilise la table de référence pour trouver les phrases correspondantes dans les données sources et compte
Term Lookup Transformation
le nombre de fois qu'un terme est répété dans la source.

Data Mining Query Transformation Cette transformation SSIS nous aide à exécuter des requêtes d'exploration de données.

Il est utilisé pour corriger les données sources en appliquant les règles que nous avons créées pour la source
DQS Cleansing Transformation de données.

12
Pr. Naoufal EL ALLALI
 Composants de transformations (flux de données)

b) Transformations des enregistrements dans SSIS

 Le tableau ci-dessous représente une liste de transformations SSIS sert à mettre à jour les valeurs des colonnes
existantes et à créer de nouvelles colonnes.
SSIS Transformations Description

Character Map Transformation Il sert à transformer les caractères d'entrée en majuscules, minuscules, etc.

Il duplique simplement les colonnes sources. C'est comme copier les données de la colonne et les coller
Copy Column Transformation
dans une nouvelle colonne.

Cette transformation est utilisée pour convertir les données d'un type de données à un autre type de
Data Conversion Transformation
données. Elle est similaire à CAST ou CONVERT.

Derived Column Transformation Il nous aide à traiter les expressions SSIS

Il est très pratique pour exporter des images, des fichiers binaires, des médias ou tout autre document
Export Column Transformation
volumineux de SQL Server vers un système de fichiers.

Cette transformation est utilisée pour importer des données du fichier texte vers le flux de données. Elle
Import Column Transformation
permet d'effectuer quelques manipulations, et de transmettre les données à la destination.

Il permet d'exécuter des fonctions qui ne sont pas disponibles dans la boîte à outils SSIS. Vous pouvez
Script Component
l'utiliser comme source de données, ou transformation, ou destination.

Il est utilisé pour exécuter les instructions SQL telles que les instructions INSERT, UPDATE et DELETE
OLE DB Command Transformation
dans le flux de données.

13
Pr. Naoufal EL ALLALI
 Composants de transformations (flux de données)

c) Transformations Split et Join

 La liste de transformations ci-dessous sert à distribuer les données sources à différentes sorties. Et aussi pour
combiner plusieurs sorties en une seule sortie.
SSIS Transformations Description

C'est comme l'instruction IF ou CASE, qui vérifie la condition donnée, et en fonction du résultat, la sortie sera
Conditional Split Transformation
envoyée vers le chemin de destination approprié.

Cette transformation SSIS envoie les données d'entrée vers plusieurs chemins de destination sans aucune
Multicast Transformation
modification.

Il est utilisé pour combiner des données provenant de plusieurs sources (telles que des fichiers Excel, des
Union All Transformation
fichiers plats ou plusieurs tables SQL) et produire un seul résultat.

Cette transformation SSIS est utilisée pour fusionner deux entrées, telles que des tables ou des fichiers, et
Merge Transformation
fournir une sortie.

La transformation Merge Join est utile pour effectuer des jointures SQL telles que Inner Join, Left Outer Join,
Merge Join Transformation
Full Outer Join et Right Outer Join (obtenues en échangeant les tables).

Cette transformation est utilisée pour comparer les données sources avec les données existantes dans la table
Lookup Transformation
de référence (ou Lookup Table) pour trouver les correspondances.

Cette transformation SSIS écrit les données d'une source de données connectée dans un fichier Cache
Cache Transformation (extension .caw) en utilisant le gestionnaire de connexion Cache. Il est utile d'effectuer une recherche sur les
données présentes dans le fichier cache.

14
Pr. Naoufal EL ALLALI
 Composants de transformations (flux de données)

d) Transformations de Rowset dans SSIS

 Le tableau ci-dessous représente une liste ci-dessous de transformations SSIS permet de créer un nouveau rowset
(ensemble d’enregistrements). Ces enregistrements peuvent inclure des données agrégées, des données triées, ou
des données pivotées et non pivotées.

SSIS Transformations Description

Aggregate Transformation Cette transformation SSIS effectue les opérations d'agrégation telles que SUM, MIN, AVG, etc.

Il sert à trier la source de données dans un ordre croissant ou décroissant. Il est similaire à l'instruction
Sort Transformation
ORDER BY de la commande T-SQL.

Cette transformation SSIS transmet le pourcentage donné de lignes de la source de données aux lignes de
Percentage Sampling Transformation
sortie sélectionnées. Les lignes restantes seront transférées vers les lignes de sortie non sélectionnées.

Row Sampling Transformation Il fournit une option pour spécifier le nombre de lignes que vous voulez récupérer de la source de données.

Il est utilisé pour convertir des données de lignes individuelles en colonnes séparées, ce qui est similaire à
Pivot Transformation SQL PIVOT. Pour les utilisateurs de SQL Server 2008 R2, je vous suggère de vous référer à Pivot
Transformation en 2008 R2 également.

Il est utilisé pour convertir les données normalisées DE en données normalisées. Il est similaire à SQL
Unpivot Transformation UNPIVOT

15
Pr. Naoufal EL ALLALI
 Composants de transformations (flux de données)

e) Transformations Audit
 Le tableau présenté ci-dessous , c’est un ensemble d’outils de transformations permettent d'ajouter les
informations relatives à l'audit et le nombre de lignes.

SSIS Transformations Description

Il est utilisé pour trouver les informations d'audit telles que le nom de la machine, le nom du paquet, l'identifiant,
Audit Transformation
la date, etc.

Cette transformation compte les lignes qui passent par un flux de données, et stocke le compte final dans une
Row Count Transformation
variable

16
Pr. Naoufal EL ALLALI
Exemple - 1: Composants de flux de données (Slowly
Changing Dimension – Type 0)
 Composants de transformations (Slowly Changing Dimension – Type 0)

 La transformation Fuzzy Grouping de SSIS sert à substituer les mots erronés par les mots corrects.
 Avant de lancer la configuration de la transformation SSIS Fuzzy Grouping, nous allons voir quelques
propriétés importantes de celle-ci :
⁃ il utilise la technique du regroupement pour substituer le mot erroné dans les données sources par ce qui
correspond au mot correct.
⁃ Cette transformation nous permet d'utiliser uniquement les colonnes de type de données DT_WSTR et
DT_STR pour la correspondance floue, et la correspondance exacte peut s'appliquer à n'importe quel type
de données, sauf DT_TEXT, DT_NTEXT et DT_IMAGE.
⁃ Il crée des tables et des index temporaires dans la base de données SQL Server au moment de l'exécution.
⁃ Cette transformation utilise seulement le gestionnaire de connexion OLE DB pour établir une connexion
pour stocker les tables et index temporaires.
⁃ Pour configurer la transformation, vous devez sélectionner le type de correspondance (Fuzzy ou Exact)
pour les colonnes d'entrée.

18
Pr. Naoufal EL ALLALI
 Exemple - 2: Composants de flux de données
(Transformation de groupements Fuzzy dans SSIS)
 Transformation de groupements Fuzzy dans SSIS

 La transformation Fuzzy Grouping de SSIS sert à substituer les mots erronés par les mots corrects.
 Avant de lancer la configuration de la transformation SSIS Fuzzy Grouping, nous allons voir quelques
propriétés importantes de celle-ci :
⁃ il utilise la technique du regroupement pour substituer le mot erroné dans les données sources par ce qui
correspond au mot correct.
⁃ Cette transformation nous permet d'utiliser uniquement les colonnes de type de données DT_WSTR et
DT_STR pour la correspondance floue, et la correspondance exacte peut s'appliquer à n'importe quel type
de données, sauf DT_TEXT, DT_NTEXT et DT_IMAGE.
⁃ Il crée des tables et des index temporaires dans la base de données SQL Server au moment de l'exécution.
⁃ Cette transformation utilise seulement le gestionnaire de connexion OLE DB pour établir une connexion
pour stocker les tables et index temporaires.
⁃ Pour configurer la transformation, vous devez sélectionner le type de correspondance (Fuzzy ou Exact)
pour les colonnes d'entrée.

20
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

 Dans cet exemple, vous découvrirez comment configurer la transformation par regroupement flou en vue de
corriger les mots mal saisis (fautes de frappe).
 Les données que nous allons prendre en compte pour cette transformation :

Déplacer et glisser la source OLE DB et la transformation Fuzzy Grouping ( Regroupement probable) de la boîte
à outils vers la région de flux de données.

21
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

Cliquez deux fois sur la source OLE DB dans la région du flux de données pour ouvrir les paramètres du gestionnaire
de connexion et fournir une option pour sélectionner une table ou un espace pour écrire notre déclaration.

Vous pouvez cliquer sur l'onglet colonnes pour vérifier les colonnes. Dans cet onglet, nous pouvons décocher les
colonnes non désirées.

22
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

Pour configurer la transformation SSIS Fuzzy Grouping, il suffit de cliquer avec le bouton droit de la souris sur cette
transformation pour ouvrir la fenêtre de l'éditeur.

Il faut donc configurer le paramètre Gestionnaire de connexions OLE DB dans l'onglet Gestionnaire de connexions.
Fuzzy Grouping Transformation va créer une table et des index temporaires dans cette source pour effectuer
l'opération.
23
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

Dans l'onglet Colonnes, il convient de configurer le type de correspondance. Il faut sélectionner les colonnes que
vous voulez regrouper comme Type de correspondance Fuzzy et les autres colonnes comme Correspondance Exact.

Pour cet exemple, le nom du pays est une chaîne de caractères, et nous voulons trouver les mauvaises valeurs dans
cette colonne. Donc, changez le type de correspondance en Fuzzy, et Cid est la valeur Int, donc le type de
correspondance est Exact.
24
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

Il existe deux paramètres importants relatifs à la configuration d'une transformation de regroupement fuzzy. Il s'agit
des chiffres et des flags de comparaison.

25
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

Vous pouvez constater les options possibles dans la capture d'écran ci-dessous (Comparison Flags)

 Ignore case : Le Fuzzy Grouping ne tient pas compte de la casse si nous cochons cette option. XYZ et xyz
seront identiques.
 Ignore kana type : Cette option de SSIS Fuzzy Grouping ignore la différence entre les caractères japonais
hiragana et katakana;
 Ignore non spacing characters : Si nous cochons cette option, le regroupement flou ignorera la différence entre
les diacritiques et les caractères.
 Ignore character width: si vous cochez cette option, le regroupement flou ignore la différence entre les
caractères à un octet et ceux à deux octets.
 Ignore symbols: La méthode du groupement flou ignore la différence entre les lettres et les symboles (espaces
blancs, ponctuations, symboles monétaires et mathématiques). Par exemple, *xy est traité de la même manière
que xy.
 Sort punctuation as symbols: Dans le cas du regroupement flou, tous les symboles de ponctuation (à
l'exception des apostrophes et des traits d'union) seront écrits avant les lettres. Par exemple, .xyz sera trié avant
le xyz.

26
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

Vous pouvez constater les options possibles dans la capture d'écran ci-dessous (Comparison Flags)

 Ignore case : Le Fuzzy Grouping ne tient pas compte de la casse si nous cochons cette option. XYZ et xyz
seront identiques.
 Ignore kana type : Cette option de SSIS Fuzzy Grouping ignore la différence entre les caractères japonais
hiragana et katakana;
 Ignore non spacing characters : Si nous cochons cette option, le regroupement flou ignorera la différence entre
les diacritiques et les caractères.
 Ignore character width: si vous cochez cette option, le regroupement flou ignore la différence entre les
caractères à un octet et ceux à deux octets.
 Ignore symbols: La méthode du groupement flou ignore la différence entre les lettres et les symboles (espaces
blancs, ponctuations, symboles monétaires et mathématiques). Par exemple, *xy est traité de la même manière
que xy.
 Sort punctuation as symbols: Dans le cas du regroupement flou, tous les symboles de ponctuation (à
l'exception des apostrophes et des traits d'union) seront écrits avant les lettres. Par exemple, .xyz sera trié avant
le xyz.

27
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

Dans cette option, on doit indiquer l'importance des chiffres de début et de fin lors de la comparaison des données
de la colonne.

Valeur Description

Les chiffres situés aux positions de départ et d'arrivée sont ignorés lors du
Neither
regroupement.
Les chiffres situés à la position de départ seront ignorés lors du
Leading
regroupement.
Les chiffres situés dans les dernières positions sont ignorés lors du
Trailing
regroupement.
Les chiffres situés aux positions de départ et d'arrivée ne seront pas ignorés
LeadingAndTrailing
lors du regroupement.

Par exemple, si les chiffres de début sont significatifs, "93 New lands Street" ne sera pas groupé avec "99 New lands
Street".

28
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

Dans l'onglet Avancé, vous devez définir le seuil de similarité. Le seuil de similarité est compris entre 0 et 1, où 1 correspond à une
correspondance exacte. L'éditeur de transformation SSIS Fuzzy Grouping fournit un curseur pour ajuster la similarité entre 0 et 1. Si le
seuil de similarité est plus proche de 1, la colonne source devrait correspondre plus précisément aux données de référence.

Dans cet exemple, nous effectuons un regroupement flou sur les noms de pays et trouvons la correspondance floue. Si nous donnons
un seuil de similarité de 0,80, les valeurs de la colonne de chaîne doivent correspondre à plus de 80 %. Ce n'est qu'alors qu'il sera
considéré comme un enregistrement valide.

29
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

 La transformation SSIS Fuzzy Grouping génère des colonnes supplémentaires en plus des colonnes
existantes, et ce sont les suivantes :

 Input key column name: Définir le nom unique de la

colonne de clé d'entrée. key_inis le nom par défaut.
 Output key column name: Fournissez le nom unique
pour la colonne de clé de sortie. key_Outis est le nom par
défaut.
 Similarity score column name: Fournir le nom unique
pour la colonne de score de similarité. Le score est une
valeur comprise entre 0 et 1, qui indique la similarité entre
la ligne d'entrée et la ligne canonique.

Pour le moment, nous allons laisser les valeurs par défaut et cliquer sur ok pour terminer la configuration de la
transformation de regroupement flou.
30
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

Il faut maintenant fournir les informations sur le serveur, la base de données et la table de la destination.

Nous devons sélectionner la base de données de SQL Server comme base de données de destination et la table [Fuzzy Grouping
Transformation Output] comme table de destination.

31
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS
Cliquez sur l'onglet Mappings pour vérifier si les colonnes sources sont exactement mises en correspondance avec les colonnes de
destination.

Nous devons sélectionner la base de données de SQL Server comme base de données de destination et la table [Destination OLE DB]
comme table de destination.

32
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS
Vérifiez si les colonnes sources sont exactement mises en correspondance avec les colonnes de destination en cliquant sur l'onglet
Mappings.

Vous pouvez cliquer sur ok pour terminer la conception du package et exécutons le package.

33
Pr. Naoufal EL ALLALI
 Transformation de groupements Fuzzy dans SSIS

Vous pouvez cliquer sur ok pour terminer la conception du package et exécutons le package.

34
Pr. Naoufal EL ALLALI
Exemple - 3 : Composants de flux de données (SSIS
Fuzzy Lookup Transformation)
 Transformation Fuzzy Lookup ( Rercherche flou)

 La transformation Fuzzy Lookup de SSIS est destinée à remplacer les mots mal saisis par les mots corrects.
Contrairement à Lookup, la transformation Fuzzy Lookup de SSIS utilise la correspondance floue pour trouver une
ou plusieurs correspondances proches dans la table de référence et remplacer les données sources par des données de
référence.
 La transformation Fuzzy Lookup de SSIS permet alors de rechercher le mot correct le plus proche et de remplacer la
valeur erronée par le mot correct. Les points suivants sont à retenir avant de travailler avec la transformation Fuzzy
Lookup de SSIS.
• Il utilise seulement le gestionnaire de connexion OLE DB pour établir une connexion avec la table de référence.
• Cette transformation utilise la table de référence pour comparer le mot erroné dans les données sources avec le
mot correct dans la table de référence.
• Reporting Service nous permet d'utiliser uniquement les colonnes de type de données DT_WSTR et DT_STR
pour le Fuzzy lookup Matching.
• Cette transformation crée des tables et des index temporaires dans la base de données SQL Server au moment de
l'exécution.
36
Pr. Naoufal EL ALLALI
 Transformation Fuzzy Lookup ( Rercherche flou)

Dans cet exemple, vous allez voir comment cette transformation Fuzzy lookup fonctionne sur les données sources. La capture d'écran
ci-dessous représente nos données sources.

37
Pr. Naoufal EL ALLALI
 Transformation Fuzzy Lookup ( Rercherche flou)

Vous pouvez déplacer la source OLE DB de la boîte à outils vers la région de flux de données. Il suffit de double-cliquer sur la source
OLE DB dans la région de flux de données pour ouvrir les paramètres du gestionnaire de connexion et sélectionner une table ou une
instruction SQL.

Dans le capture ci-dessus, nallons sélectionner la base de données ‘TestDW’ et la table présentée précédemment comme base de
données et table source OLE DB.

38
Pr. Naoufal EL ALLALI
 Transformation Fuzzy Lookup ( Rercherche flou)

Vous pouvez déplacer la source OLE DB de la boîte à outils vers la région de flux de données. Il suffit de double-cliquer sur la source
OLE DB dans la région de flux de données pour ouvrir les paramètres du gestionnaire de connexion et sélectionner une table ou une
instruction SQL.

Dans le capture ci-dessus, nallons sélectionner la base de données ‘TestDW’ et la table présentée précédemment comme base de
données et table source OLE DB.

39
Pr. Naoufal EL ALLALI
 Transformation Fuzzy Lookup ( Rercherche flou)

 Lorsque vous sélectionnez la transformation Fuzzy Lookup, un double clic sur celle-ci ouvre l'éditeur.
Dans l'onglet table de référence, nous devons configurer le gestionnaire de connexion et également
sélectionner la table. Pour cela, cet onglet dispose de plusieurs options.

40
Pr. Naoufal EL ALLALI
 Transformation Fuzzy Lookup ( Rercherche flou)

 Lorsque vous sélectionnez la transformation Fuzzy Lookup, un double clic sur celle-ci ouvre l'éditeur.
Dans l'onglet table de référence, nous devons configurer le gestionnaire de connexion et également
sélectionner la table. Pour cela, cet onglet dispose de plusieurs options.
• OLE DB Connection Manager : La transformation par recherche floue ne prend en charge que le
gestionnaire de connexion OLE DB pour établir une connexion avec la table de référence. Il faut donc
créer une connexion à l'aide du gestionnaire de connexion OLE DB.
• Generate new index: La transformation SSIS Fuzzy Lookup va générer un nouvel index sur la table
référencée à utiliser.
• Reference table name: Dans la liste déroulante, vous pouvez sélectionner la table de référence de la
connexion OLE DB.
• Store new index: Si vous choisissez d'enregistrer le nouvel index sur la table, sélectionnez cette
option.
• New index name: Si vous choisissez l'option Store New index, écrivez le nom de l'index ici.
• Maintain stored index: Si vous voulez que le serveur SQL maintienne l'index nouvellement créé en
utilisant l'option Store New index, cochez cette option.
• Use existing index: Si vous souhaitez utiliser l'index existant (déjà créé) sur la table, sélectionnez
cette option.
• Name of an existing index: Vous pouvez sélectionner l'index existant dans la liste déroulante.

41
Pr. Naoufal EL ALLALI
 Transformation Fuzzy Lookup ( Rercherche flou)

Dans la capture d'écran ci-dessous, vous constatez que nous avons configuré le gestionnaire de connexion OLE DB à la base de
données TestDW, puis sélectionné la Table ‘Reference_Pays’ comme table de référence.

42
Pr. Naoufal EL ALLALI
 Transformation Fuzzy Lookup ( Rercherche flou)

Vous pouvez cocher les colonnes que vous souhaitez faire passer et joindre la colonne source (Nom du pays) au nom de la colonne de
la table de référence (à nouveau, Nom du pays dans cet exemple).

Si vous voulez changer la colonne de sortie, alors changez le nom de l'alias comme nous l'avons fait dans la capture d'écran ci-dessous.

43
Pr. Naoufal EL ALLALI
 Transformation Fuzzy Lookup ( Rercherche flou)

Nous allons maintenant devoir fournir les informations sur le serveur, la base de données et la table de la destination.

Il suffit de double-cliquer sur la destination OLE DB et de fournir les informations requises.

44
Pr. Naoufal EL ALLALI
 Transformation Fuzzy Lookup ( Rercherche flou)

Vous pouvez cliquer sur ok pour terminer la conception du package et exécutons le package.

45
Pr. Naoufal EL ALLALI
 Transformation Fuzzy Lookup ( Rercherche flou)

Lancez le Management Studio et sélectionnez la table requise pour vérifier les résultats de la transformation.

46
Pr. Naoufal EL ALLALI
Exemple - 4 : Composants de flux de données (Derived
Column Transformation)
 Transformation de colonnes dérivées (Derived Column Transformation)

 La transformation de colonnes dérivées dans SSIS est un outil essentiel pour traiter les expressions dans
SQL Server Integration Services. Le langage d'expression possède des fonctions intégrées puissantes pour la
manipulation des chaînes de caractères, les conversions de types de données, les fonctions mathématiques,
les expressions conditionnelles et le traitement des valeurs nulles.
 Avec la transformation de colonne dérivée SSIS, nous pouvons créer l'expression souhaitée en utilisant les
fonctions intégrées, les colonnes, les opérateurs et les variables. La transformation de colonne dérivée SSIS
offre également la possibilité de choisir si vous souhaitez créer de nouvelles colonnes ou remplacer les
colonnes existantes par des valeurs calculées à partir d'expressions.

48
Pr. Naoufal EL ALLALI
 Transformation de colonnes dérivées (Derived Column Transformation)

Vous pouvez déplacer la source OLE DB, la destination [Link] et la transformation en colonne dérivée de la boîte à outils SSIS
vers la région du flux de données.

49
Pr. Naoufal EL ALLALI
 Transformation de colonnes dérivées (Derived Column Transformation)

Cliquez sur la source OLE DB dans la région du flux de données pour accéder aux paramètres du gestionnaire de connexion et créer un
espace pour écrire notre déclaration.

La commande SQL utilisée dans la capture d'écran ci-dessus en vue d’extraire les données.

50
Pr. Naoufal EL ALLALI
Transformation de colonnes dérivées (Derived Column Transformation)

Double-cliquez sur la transformation de la colonne dérivée

51
Pr. Naoufal EL ALLALI
 Transformation de colonnes dérivées (Derived Column Transformation)

 Derived Column Name: Fournit un nom unique. Identique à celui de la colonne Alias en T-SQL.
 Derived Column: Cette colonne dérivée offre deux options. Vous pouvez l'ajouter comme une nouvelle colonne
ou remplacer une colonne existante par celle-ci.
 Expression : A cet endroit, nous allons écrire une expression personnalisée en combinant la fonction intégrée, les
variables et les colonnes.
 Precision: Lorsque nous ajoutons un nouveau champ, Derived Column Transformation dans SSIS définit
automatiquement la précision des données numériques en fonction du type de données. La valeur de cette
précision est en lecture seule.
 Scale: Lorsque nous ajoutons un nouveau champ, la transformation en colonne dérivée définit automatiquement
l'échelle des données numériques en fonction du type de données. La valeur de ce paramètre est en lecture seule.
 Code Page: Lorsque nous ajoutons une nouvelle colonne, elle définit automatiquement la page de codes pour le
type de données DT_STR.
 Configure error output: Spécifit comment traiter les erreurs.

52
Pr. Naoufal EL ALLALI
 Transformation de colonnes dérivées (Derived Column Transformation)

Vous pouvez cliquer sur ok pour terminer la configuration de la transformation de la colonne dérivée dans SSIS.

Faites glisser et déposez la flèche bleue sur la destination [Link].

53
Pr. Naoufal EL ALLALI
 Transformation de colonnes dérivées (Derived Column Transformation)

Maintenant, nous devons fournir les détails du serveur, de la base de données et de la table de la destination. Double-cliquez donc sur
la destination [Link] et fournissez les informations requises.

Cliquez sur ok pour terminer la conception du package SSIS Derived Column Transformation. Exécutons le package.

54
Pr. Naoufal EL ALLALI
 Transformation de colonnes dérivées (Derived Column Transformation)

Cliquez sur ok pour terminer la conception du package SSIS Derived Column Transformation. Exécutons le package.

Ouvrons le Management Studio et vérifions les résultats de la transformation de la colonne dérivée.

55
Pr. Naoufal EL ALLALI
Exemple – 5 : Composants de flux de données (OLE DB
Command Transformation in SSIS)
 Transformation des commandes OLE DB (OLE DB Command Transformation in SSIS)

 La transformation de commande OLE DB dans SSIS sert à exécuter les instructions SQL telles que les
instructions :
 INSERT,
 UPDATE et DELETE dans le flux de données.
 Dans cet exemple, vous allez voir comment insérer des données dans la table de destination à l'aide de la
transformation de commande OLE DB. ( pour les autres instructions, nous allons voir les différents étapes au
cours de la présentation cet exemple)
 Avant de commencer à concevoir le package SSIS, voyons la table de destination dans la base de données.

57
Pr. Naoufal EL ALLALI
 Transformation des commandes OLE DB (OLE DB Command Transformation in SSIS)

 La transformation de commande OLE DB dans SSIS sert à exécuter les instructions SQL telles que les
instructions :
 INSERT,
 UPDATE et DELETE dans le flux de données.
 Dans cet exemple, vous allez voir comment insérer des données dans la table de destination à l'aide de la
transformation de commande OLE DB. ( pour les autres instructions, nous allons voir les différents étapes au
cours de la présentation cet exemple)
 Avant de commencer à concevoir le package SSIS, voyons la table de destination dans la base de données.

58
Pr. Naoufal EL ALLALI
Transformation des commandes OLE DB (OLE DB Command Transformation in SSIS)

Nous allons établir les mêmes étapes que celles que nous avons vues dans les différents exemples précédents.

59
Pr. Naoufal EL ALLALI
 Transformation des commandes OLE DB (OLE DB Command Transformation in SSIS)

Maintenant nous allons commencer à configurer ‘’la transformation de la commande OLE DB’’ alors il devra cliquer double-cliquer
sur le composant correspondant.

60
Pr. Naoufal EL ALLALI
Transformation des commandes OLE DB (OLE DB Command Transformation in SSIS)

Dans l'onglet Gestionnaires de connexion, nous sélectionnons la connexion OLE DB déjà créée dans la liste.

61
Pr. Naoufal EL ALLALI
 Transformation des commandes OLE DB (OLE DB Command Transformation in SSIS)

Dans les propriétés du composant, nous devons écrire la déclaration SQL que nous devons exécuter. Veuillez sélectionner la propriété
SqlCommand et cliquer sur le bouton Parcourir (...).

62
Pr. Naoufal EL ALLALI
 Transformation des commandes OLE DB (OLE DB Command Transformation in SSIS)

Une fois que vous avez cliqué sur le bouton Parcourir, une fenêtre d'édition de valeur de chaîne s'ouvre pour écrire la déclaration SQL.
Dans cet exemple, nous insérons les valeurs dans la table des produits.

63
Pr. Naoufal EL ALLALI
 Transformation des commandes OLE DB (OLE DB Command Transformation in SSIS)

Dans le Mappage des colonnes, nous devons attribuer le nom de colonne approprié aux paramètres.

Dans la capture d'écran ci-dessus, vous pouvez observer que le paramètre 0 représente le premier point d'interrogation et que le
paramètre 6 représente le sixième point d'interrogation.
64
Pr. Naoufal EL ALLALI
 Transformation des commandes OLE DB (OLE DB Command Transformation in SSIS)

Appuyez sur OK pour terminer la configuration de la transformation de la commande Inserting Data Using OLE DB dans le package
SSIS. Exécutons le package

65
Pr. Naoufal EL ALLALI
Transformation des commandes OLE DB (OLE DB Command Transformation in SSIS)

Ouvrons le Management Studio du serveur SQL et vérifions le résultat.

66
Pr. Naoufal EL ALLALI
Exemple – 6 : Composants de flux de données (Union
All Transformation)
 Transformation – Unir tout (Union All Transformation)

 La transformation Union All de SSIS sert à combiner des données issues de plusieurs sources (fichiers
Excel, fichiers plats, etc.). ou de plusieurs tables SQL et produire un seul résultat qui sera stocké dans la table
de destination.
 Il n'y a pas de contrainte spécifique lors de la fusionner des données et de leur stockage dans la table de
destination.
 Dans cet exemple, vous allez voir comment combiner des données provenant de différents sources et les
stocker dans la table de destination et cela se fait à l’aide Union ALL Transformation.

68
Pr. Naoufal EL ALLALI
 Transformation – Unir tout (Union All Transformation)

Nous allons extraire les trois sources de données de type Excel (Produit 1, Produit 2 et Produit 3, comme indiqué dans la capture
d'écran ci-dessous)., et reprendre les mêmes étapes précédentes relatives à l'établissement de la connexion.

Nous allons exporté toutes les données dans une table DimProduct de base de données SQL.

69
Pr. Naoufal EL ALLALI
 Transformation – Unir tout (Union All Transformation)

Relier toutes les sources Excel à l'union de toutes les transformations en faisant glisser les flèches de sortie comme illustré dans le
capture ci-dessous

70
Pr. Naoufal EL ALLALI
 Transformation – Unir tout (Union All Transformation)

Double-cliquez sur la transformation SSIS Union All Transformation pour ouvrir la fenêtre de l'éditeur Union All Transformation et la
configurer.

L'éditeur SSIS Union All Transformation mappe automatiquement les colonnes tant que les noms des colonnes sont les mêmes. S'ils ne
le sont pas, nous devons le faire manuellement. Veuillez faire attention aux conversions de données lorsque vous travaillez avec Excel.
71
Pr. Naoufal EL ALLALI
 Transformation – Unir tout (Union All Transformation)

Vous pouvez maintenant déplacer la destination OLE DB en faisant un double-clic sur cette destination pour la configurer., nous
devons fournir les détails du serveur, de la base de données et de la table de la cible.

On va reprendre les mêmes étapes précédentes relatives à l'établissement de la connexion pour stocker les données vers la table de la
cible.
72
Pr. Naoufal EL ALLALI
 Transformation – Unir tout (Union All Transformation)

Vous pouvez maintenant déplacer la destination OLE DB en faisant un double-clic sur cette destination pour la configurer., nous
devons fournir les détails du serveur, de la base de données et de la table de la cible.

On va reprendre les mêmes étapes précédentes relatives à l'établissement de la connexion pour stocker les données vers la table de la
cible.
73
Pr. Naoufal EL ALLALI
 Transformation – Unir tout (Union All Transformation)

Après avoir terminé l’établissement de connexion vers la destination, nous exécutons le package

Ouvrons Management Studio et vérifions le résultat de la transformation "Union All".

74
Pr. Naoufal EL ALLALI
Exemple – 7 : Composants de flux de données (Lookup
Transformation)
 Transformation de la recherche (Lookup Transformation)

 Le Lookup dans SSIS constitue une des transformations les plus utilisées, qui permet de comparer les
données sources aux données existantes de la table de référence (ou Lookup Table) et de trouver celles qui
correspondent.
 Nous allons maintenant faire un exemple simple pour vous montrer rapidement comment fonctionne
LookUp. Donc, nous allons maintenant faire un exemple simple pour vous montrer rapidement comment
fonctionne LookUp. Nous allons donc prendre une table de clients avec des colonnes : ClientID,
ClientName, ClientAmount, ClientAddress, ClientCountryID, ClientISActive. Nous allons également créer
une table principale pour la liste des pays et la nommer Pays avec les colonnes : PaysId, PaysNom. Nous
allons ajouter quelques noms de pays à cette table principale.
 Au moment où nous chargeons des données à partir d'un fichier source qui contient des "enregistrements de
clients avec le nom du pays" avant qu'elles n'atteignent la table de destination (table des clients), nous
appliquons le composant LookUp pour comparer les enregistrements source avec la table des pays existante
et filtrer ceux qui correspondent et ceux qui ne correspondent pas. Si les valeurs clés correspondent, nous les
remplacerons par le nom du pays et nous les mettrons à jour dans la table de destination.
76
Pr. Naoufal EL ALLALI
 Transformation de la recherche (Lookup Transformation)

Nous allons créer la table des pays dans la base de donnée en vue de comparer avec les enregistrements source

77
Pr. Naoufal EL ALLALI
 Transformation de la recherche (Lookup Transformation)

Dans cette étape, nous allons créer la table CustomerMaster avec les colonnes : ClientID, ClientName, ClientMontant,
CustomerAddress, ClientPaysID, ClientISActive respectivement dans SQL management studio comme montré dans l'image ci-
dessous.

78
Pr. Naoufal EL ALLALI
Transformation de la recherche (Lookup Transformation)

Dans cette étape, nous allons créer un fichier source de type txt.

79
Pr. Naoufal EL ALLALI
 Transformation de la recherche (Lookup Transformation)

Maintenant, vous allez glisser et déplacer la source du fichier Plat de la boîte à outils SSIS dans la région du flux de données (on va
reprendre les mêmes étapes précédentes relatives à l'établissement de la connexion.)

80
Pr. Naoufal EL ALLALI
 Transformation de la recherche (Lookup Transformation)

L'étape la plus importante ici est de glisser et déposer le composant SSIS #LookUP et de l'attacher au composant Flat File Source
comme indiqué dans la capture ci-dessus.

Vous allez faire un double clic sur celle-ci ouvre l'éditeur. Dans l'onglet table de référence, ensuite nous allons configurer
certains paramètres.

81
Pr. Naoufal EL ALLALI
 Transformation de la recherche (Lookup Transformation)

Sélectionnez le menu Connexion -> Choisissez votre nom de connexion SQL -> Sélectionnez CountryTable comme indiqué dans la
capture ci-dessous.

82
Pr. Naoufal EL ALLALI
 Transformation de la recherche (Lookup Transformation)

Dans le menu de gauche, sélectionnez les colonnes. Vous y trouverez les noms des colonnes de la source et les colonnes de la table
Client (à droite). Puisque nous comparons les noms de pays, il suffit de faire glisser et de déposer la flèche sur le nom du pays des
deux côtés et de sélectionner la sortie comme PID comme indiqué dans la capture ci-dessous.

83
Pr. Naoufal EL ALLALI
 Transformation de la recherche (Lookup Transformation)

Une fois que c'est fait, cliquez simplement sur le bouton OK et enregistrez le tout.

84
Pr. Naoufal EL ALLALI
 Transformation de la recherche (Lookup Transformation)

 Sachant que nous avons deux sorties, nous allons utiliser deux destinations.
1 : Pour une sortie correspondante -> Destination [Link].
2 : Pour les sorties non correspondantes -> Destination fichier plat.
 Il suffit de déplacer la destination [Link] par un clique droit et de la configurer. Ici, nous allons choisir
notre table de données comme Clients afin de charger toutes les données dans cette table.

85
Pr. Naoufal EL ALLALI
 Transformation de la recherche (Lookup Transformation)

Donc, Maintenant nous allons sélectionner la sortie correspondant pour charger les données correspondant vers la
destination [Link], ensuite Nous allons choisir un fichier .txt pour enregistrer les erreurs.

et

86
Pr. Naoufal EL ALLALI
 Transformation de la recherche (Lookup Transformation)

Après avoir terminé l’établissement de connexion vers la destination, nous exécutons le package

87
Pr. Naoufal EL ALLALI
 Transformation de la recherche (Lookup Transformation)

Ouvrons Management Studio et le fichier plat et vérifions le résultat de la transformation « LookUp".

et

88
Pr. Naoufal EL ALLALI