Jointure SSIS avec Merge Join

Tutoriel pour faire une jointure SSIS avec deux tables SQL Server avec le composant Merge Join.

Le composant SSIS Merge Join permet d’effectuer une jointure pour fusionner deux ou plusieurs sources de données à partir d’une clef commune. Autrement dit, le composant SSIS Merge Join permet de fusionner différentes sources hétérogènes ou non en une seule, par exemple des fichiers plats, des documents XML, des fichiers Excel ou encore des tables SQL Server.

Ce tutoriel SSIS explique comment utiliser le composant SSIS qui permet de fusionner 2 tables sources SQL Server dans une seule table cible. Avec SSIS, que ce soit pour les versions on-premises ou sur Le Cloud Microsoft Azure, le composant est utile pour gérer des tables distinctes et sans utiliser de code T-SQL ou .NET. Merge Join en Anglais, il est appelé Jointure de fusion en Français.

1. Préparer les tables sources et la table cible avec SSMS

Depuis SSMS, exécuter le code SQL ci-dessous pour créer deux tables sources à fusionner dans une table cible. Les tables sources sont la table des table des ventes : dbo.Sales et la table des clients: dbo.Customers.

1.1 Créer la table source des ventes

La première table source est la table des ventes, elle sera enrichie dans un flux de données SSIS avec les données provenant de la table des clients, le prénom et le nom de famille.

-- Créer la table des ventes
CREATE TABLE [dbo].[Sales](
  [CustomerID]  [int] NOT NULL,
  [MonthID]     [nvarchar](20) NOT NULL,
  [YearID]      [nvarchar](20) NOT NULL,
  [Qty]         [numeric](10,2) NOT NULL,
  [Price]       [numeric](10,2) NOT NULL,
  [Sales]       [numeric](10,2) NOT NULL,
  [ProductID]   [int] NOT NULL,
  [ProductName] [nvarchar](100) NOT NULL,
  CONSTRAINT [SalesPK] 
    PRIMARY KEY CLUSTERED (
      [CustomerID], [MonthID], [YearID], [ProductID] ASC
    )
);

-- Insérer les données de ventes
INSERT [dbo].[Sales] VALUES (1, N'1', N'2022', 10.00, 879.20, 8792.00, 31, N'Babka');
INSERT [dbo].[Sales] VALUES (1, N'3', N'2021', 5.00, 312.00, 1560.00, 40, N'Unreal');
INSERT [dbo].[Sales] VALUES (1, N'6', N'2021', 9.00, 627.00, 5643.00, 27, N'Qrunch');
INSERT [dbo].[Sales] VALUES (1, N'7', N'2022', 6.00, 621.70, 3730.20, 10, N'Ambrosi');
INSERT [dbo].[Sales] VALUES (1, N'7', N'2022', 4.00, 653.10, 2612.40, 47, N'Quickick');
INSERT [dbo].[Sales] VALUES (1, N'9', N'2021', 2.00, 734.90, 1469.80, 47, N'Quickick');
INSERT [dbo].[Sales] VALUES (2, N'2', N'2021', 9.00, 895.60, 8060.40, 47, N'Quickick');
INSERT [dbo].[Sales] VALUES (2, N'3', N'2022', 11.00, 51.80, 569.80, 49, N'Menudito');
INSERT [dbo].[Sales] VALUES (2, N'7', N'2021', 11.00, 112.20, 1234.20, 19, N'Chiavie');
INSERT [dbo].[Sales] VALUES (2, N'9', N'2022', 7.00, 581.80, 4072.60, 33, N'Jans');
INSERT [dbo].[Sales] VALUES (3, N'2', N'2022', 4.00, 576.60, 2306.40, 5, N'Amoy');
INSERT [dbo].[Sales] VALUES (3, N'6', N'2021', 6.00, 551.10, 3306.60, 8, N'Gilda');
INSERT [dbo].[Sales] VALUES (4, N'0', N'2021', 7.00, 15.40, 107.80, 26, N'Lasco');
INSERT [dbo].[Sales] VALUES (4, N'1', N'2022', 2.00, 706.60, 1413.20, 27, N'Qrunch');
INSERT [dbo].[Sales] VALUES (4, N'1', N'2022', 9.00, 491.70, 4425.30, 30, N'Paldo');
INSERT [dbo].[Sales] VALUES (4, N'3', N'2022', 4.00, 810.20, 3240.80, 22, N'Bourbon');
INSERT [dbo].[Sales] VALUES (4, N'4', N'2021', 9.00, 648.90, 5840.10, 42, N'Barbacoa');
INSERT [dbo].[Sales] VALUES (4, N'5', N'2021', 7.00, 284.90, 1994.30, 5, N'Amoy');
INSERT [dbo].[Sales] VALUES (5, N'11', N'2021', 3.00, 914.90, 2744.70, 45, N'Predue');
INSERT [dbo].[Sales] VALUES (5, N'11', N'2022', 9.00, 903.80, 8134.20, 44, N'Kemps');
INSERT [dbo].[Sales] VALUES (5, N'4', N'2022', 10.00, 133.90, 1339.00, 6, N'Haystack');
INSERT [dbo].[Sales] VALUES (5, N'6', N'2021', 10.00, 940.20, 9402.00, 34, N'Amazin');
INSERT [dbo].[Sales] VALUES (5, N'7', N'2021', 7.00, 352.90, 2470.30, 32, N'Maverik');
INSERT [dbo].[Sales] VALUES (5, N'7', N'2022', 3.00, 116.40, 349.20, 7, N'Kalamata');
INSERT [dbo].[Sales] VALUES (6, N'1', N'2021', 4.00, 283.30, 1133.20, 23, N'Oatsnack');
INSERT [dbo].[Sales] VALUES (6, N'4', N'2021', 4.00, 604.90, 2419.60, 10, N'Ambrosi');
INSERT [dbo].[Sales] VALUES (6, N'4', N'2022', 5.00, 184.30, 921.50, 6, N'Haystack');
INSERT [dbo].[Sales] VALUES (6, N'5', N'2022', 9.00, 464.40, 4179.60, 18, N'Lurpak');
INSERT [dbo].[Sales] VALUES (6, N'8', N'2021', 6.00, 836.10, 5016.60, 40, N'Unreal');
INSERT [dbo].[Sales] VALUES (6, N'9', N'2022', 4.00, 254.80, 1019.20, 1, N'Boncora');
INSERT [dbo].[Sales] VALUES (7, N'0', N'2021', 7.00, 100.90, 706.30, 45, N'Predue');
INSERT [dbo].[Sales] VALUES (7, N'10', N'2021', 4.00, 161.50, 646.00, 10, N'Ambrosi');
INSERT [dbo].[Sales] VALUES (7, N'10', N'2022', 7.00, 484.50, 3391.50, 50, N'Armanino');
INSERT [dbo].[Sales] VALUES (7, N'3', N'2022', 7.00, 748.80, 5241.60, 38, N'Exo');
INSERT [dbo].[Sales] VALUES (7, N'6', N'2021', 1.00, 623.50, 623.50, 45, N'Predue');
INSERT [dbo].[Sales] VALUES (7, N'7', N'2021', 6.00, 185.00, 1110.00, 28, N'Basilico');
INSERT [dbo].[Sales] VALUES (7, N'8', N'2022', 7.00, 625.50, 4378.50, 4, N'Sartori');
INSERT [dbo].[Sales] VALUES (7, N'8', N'2022', 3.00, 125.40, 376.20, 38, N'Exo');
INSERT [dbo].[Sales] VALUES (8, N'1', N'2021', 8.00, 949.80, 7598.40, 16, N'Homekist');
INSERT [dbo].[Sales] VALUES (8, N'10', N'2021', 9.00, 944.30, 8498.70, 41, N'Baked');
INSERT [dbo].[Sales] VALUES (8, N'10', N'2022', 7.00, 578.10, 4046.70, 2, N'Master');
INSERT [dbo].[Sales] VALUES (8, N'12', N'2022', 3.00, 309.20, 927.60, 42, N'Barbacoa');
INSERT [dbo].[Sales] VALUES (8, N'2', N'2022', 4.00, 417.80, 1671.20, 25, N'Scrapple');
INSERT [dbo].[Sales] VALUES (8, N'4', N'2021', 7.00, 509.20, 3564.40, 4, N'Sartori');

1.2 Créer la table source des clients

La deuxième table source est celle des clients, qui permet de stocker le numéro de client, le prénom, le nom de famille, la ville et le pays. Elle contient 8 lignes.

-- Créer la table des clients
CREATE TABLE [dbo].[Customers](
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](20) NULL,
   [LastName] [nvarchar](20) NULL,
   [City] [nvarchar](20) NULL,
   [Country] [nvarchar](50) NULL,
   CONSTRAINT [CustomersPrimaryKeyCustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO

-- Insérer 8 lignes à exporter au format XML
INSERT INTO [dbo].[Customers] VALUES (1, N'Ali', N'Ahmed', N'Cairo', N'Egypt');
INSERT INTO [dbo].[Customers] VALUES (2, N'Johnny', N'John', N'Toronto', N'Canada');
INSERT INTO [dbo].[Customers] VALUES (3, N'John', N'Doe', N'Mexico City', N'Mexico');
INSERT INTO [dbo].[Customers] VALUES (4, N'Shu', N'Abbas', N'Paris', N'France');
INSERT INTO [dbo].[Customers] VALUES (5, N'Jeane', N'Raffin', N'Liushutun', N'China');
INSERT INTO [dbo].[Customers] VALUES (6, N'Legra', N'Leate', N'Błaszki', N'Poland');
INSERT INTO [dbo].[Customers] VALUES (7, N'Sullivan', N'Goadby', N'Xiaoguwei', N'China');
INSERT INTO [dbo].[Customers] VALUES (8, N'Tom', N'Ellams', N'Lop Buri', N'Thailand');

1.3 Créer la table cible des ventes enrichie

La table cible est une table des ventes enrichie avec le nom et le prénom des clients : dbo.SalesEnriched. La table enrichie a exactement la même structure avec les deux colonnes suivantes en plus:

  1. FirstName : Prénom du client
  2. LastName : Nom de famille du client
CREATE TABLE [dbo].[SalesEnriched](
  [CustomerID]  [int] NOT NULL,
  [MonthID]     [nvarchar](20) NOT NULL,
  [YearID]      [nvarchar](20) NOT NULL,
  [Qty]         [numeric](10,2) NOT NULL,
  [Price]       [numeric](10,2) NOT NULL,
  [Sales]       [numeric](10,2) NOT NULL,
  [ProductID]   [int] NOT NULL,
  [ProductName] [nvarchar](100) NOT NULL,
  [FirstName]   [nvarchar](20) NULL,
  [LastName]    [nvarchar](20) NULL,
  CONSTRAINT [SalesSalesEnrichedPK] 
    PRIMARY KEY CLUSTERED (
      [CustomerID], [MonthID], [YearID], [ProductID] ASC
    )
);

2. Créer un flux de données SSIS avec Merge Join

Au préalable, créer une connexion à la base SQL Server depuis le package SSIS.

2.1 Préparer les composants sources

Dans un flux de données SSIS, ajouter les composants suivants, puis renommer les composants avec le nom des tables sources et cibles.

  • 2 composants OLE DB Source
  • Un composant transformation Merge Join
  • Une destination OLE DB
Ajouter les composants SSIS OLE DB Source, Merge Join et la Destination OLE DB
Ajouter les composants SSIS OLE DB Source, Merge Join et la Destination OLE DB

Choisir la première table source de la jointure SSIS, c’est à dire la table gauche.

Sélectionner la première table source : Sales
Sélectionner la première table source : Sales

De la même manière, sélectionner la deuxième able source, la table des clients.

Choisir la table à droite de la jointure SSIS
Choisir la table à droite de la jointure SSIS

Relier enfin les sources à la transformation Merge Join SSIS, la fenêtre apparait alors. Choisir l’entrée gauche pour la table dbo.Sales.

Choisir l'entrée de jointure SSIS gauche pour la table des ventes
Choisir l’entrée de jointure SSIS gauche pour la table des ventes

2.2 Paramétrer le tri des données sources

Le package affiche une erreur SSIS:

[Merge Join [2]] Error: « Merge Join.Inputs[Merge Join Left Input] » must be sorted. If possible, sort the data at the source and mark the data as sorted by setting the IsSorted and SortKeyPosition properties in the Advanced Editor. Otherwise, add a Sort Transformation to the path before the merge.

Le message est assez explicite, il suffit donc de trier les colonnes de jointure à l’aide des 2 propriétés suivantes, depuis l’éditeur avancé du composant.

  1. IsSorted : pour indiquer que la source est triée.
  2. SortKeyPosition : pour indiquer l’ordre des colonnes utilisées pour le tri.

Répéter ces opérations pour les deux tables sources.

Depuis les composants OLE DB Source:

  1. Faire un clic-droit
  2. Dans l’onglet Input and Output Properties
  3. Sélectionner OLE DB Source Output
  4. Changer la propriété IsSorted à True
Changer la valeur IsSorted dans l'éditeur avancé OLE DB
Changer la valeur IsSorted dans l’éditeur avancé OLE DB

Toujours depuis l’éditeur avancé SSIS, sélectionner cette fois la colonne utilisée comme clef de jointure, ici c’est CustomerID, et modifier la propriété SortKeyPosition à 1. Dans une jointure avec des colonnes multiples, assigner les mêmes valeurs de tri aux colonnes correspondantes dans les 2 tables sources.

Assigner SortKeyPosition à 1 pour la colonne CustomerID
Assigner SortKeyPosition à 1 pour la colonne CustomerID

3. Configurer la jointure avec Merge Join SSIS

Ouvrir le composant de transformation Merge Join et configurer les options comme suit:

  1. Choisir le type de jointure SSIS: Left outer join.
  2. Sélectionner toutes les colonnes sources de la première table.
  3. Sélectionner uniquement le prénom et le nom de famille de la deuxième table.
Configurer la jointure dans le composant SSIS Merge Join
Configurer la jointure dans le composant SSIS Merge Join

4. Préparer la table cible pour stocker le résultat de la jointure

Configurer le dernier composant du flux de données et le faire pointer sur la table des ventes enrichies.

Choisir la table Sales_Enriched pour stocker le résultat de la jointure SSIS
Choisir la table Sales_Enriched pour stocker le résultat de la jointure SSIS

Vérifier le mapping des colonnes depuis l’onglet Mappings. Les colonnes provenant des 2 tables sources sont bien mappés vers la table cible.

Mapping des données issues de la jointure SSIS à la table des ventes des ventes
Mapping des données issues de la jointure SSIS à la table des ventes des ventes

5. Exécuter la jointure SSIS et vérifier les données dans la table

Exécuter enfin le package SSIS, Les 44 lignes de données de ventes sont bien intégrées dans la table enrichie sans erreur. Dans ce tutoriel simple, les lignes non enrichies ne sont pas gérées et donc non redirigées.

44 lignes sont intégrées après la jointure SSIS avec Merge Join
44 lignes sont intégrées et enrichies

Enfin cliquer sur Visualiser les données depuis le composant OLE DB Destination pour lire la table et vérifier que les deux colonnes sont bien enrichies avec les noms et prénoms des clients.

Les colonnes additionnelles sont bien enrichies par la jointure SSIS
Les colonnes additionnelles sont bien enrichies par la jointure SSIS

Conclusion sur le composant SSIS Merge Join

La jointure SSIS permet de grouper les données de deux tables sans écrire une seule ligne de code. Le seul prérequis est de trier au préalable les données, directement depuis l’éditeur avancé ou avec le composant de tri.

Autres tutoriels sur les transformations SSIS

Pivoter les lignes en colonnes avec SSIS Pivot

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *