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

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

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

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

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

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.

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.

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.

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.

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.

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.

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
• Transformer les colonnes en lignes avec Unpivot
• Utiliser le composant d’agrégation pour compter les lignes distinctes
• Grouper les données de plusieurs tables avec SSIS Union All