Grouper plusieurs tables avec SSIS Union All

Grouper les données de plusieurs tables SQL Server en une avec SSIS et la transformation Union All.

Pour grouper le contenu de plusieurs tables ou sources de données en général, il est possible d’utiliser une requête SQL Union All, voici comment le faire sans code avec SSIS. Une prérequis pour ce type de requête est de construire toutes les tables sources avec la même structure de données, c’est à dire le type et la longueur des colonnes. Ou du moins à l’entrée du composant Union All. Cette opération est équivalente à une requête SQL Union classique.

1. Créer plusieurs tables à grouper et la table cible

Créer trois tables sources avec la même structure et y insérer des lignes. Chaque table comporte 16 lignes, soit un total de 48 lignes.

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

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

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

Créer une quatrième table pour stocker les résultats.

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

2. Alimenter les tables SQL à regrouper avec SSIS

Télécharger ce fichier et le renommer avec une extension .sql. Ou exécuter directement son contenu depuis une fenêtre SSMS.

insert_customers_data_in_3_tables.txtTélécharger

Si il ne vous ai pas possible de télécharger le fichier, alors voici le contenu:

-- table 1
INSERT [dbo].[Customers_1] VALUES (1, N'Ali', N'Ahmed', N'Cairo', N'Egypt' );
INSERT [dbo].[Customers_1] VALUES (2, N'Johnny', N'John', N'Toronto', N'Canada' );
INSERT [dbo].[Customers_1] VALUES (3, N'John', N'Doe', N'Mexico City', N'Mexico' );
INSERT [dbo].[Customers_1] VALUES (4, N'Shu', N'Abbas', N'Paris', N'France' );
INSERT [dbo].[Customers_1] VALUES (5, N'Jeane', N'Raffin', N'Liushutun', N'China' );
INSERT [dbo].[Customers_1] VALUES (6, N'Legra', N'Leate', N'Błaszki', N'Poland' );
INSERT [dbo].[Customers_1] VALUES (7, N'Sullivan', N'Goadby', N'Xiaoguwei', N'China' );
INSERT [dbo].[Customers_1] VALUES (8, N'Tom', N'Ellams', N'Lop Buri', N'Thailand' );
INSERT [dbo].[Customers_1] VALUES (9, N'Trace', N'Fowell', N'Sriwing', N'Indonesia' );
INSERT [dbo].[Customers_1] VALUES (10, N'Christoffer', N'Rochford', N'Mburukullu', N'Indonesia' );
INSERT [dbo].[Customers_1] VALUES (11, N'Dru', N'Dunridge', N'Mistrató', N'Colombia' );
INSERT [dbo].[Customers_1] VALUES (12, N'Dud', N'Gravenor', N'Sandaogou', N'China' );
INSERT [dbo].[Customers_1] VALUES (13, N'Lori', N'Garshore', N'Jeziora Wielkie', N'Poland' );
INSERT [dbo].[Customers_1] VALUES (14, N'Cordy', N'Kentish', N'Vydreno', N'Russia' );
INSERT [dbo].[Customers_1] VALUES (15, N'Ancell', N'Wileman', N'Claresholm', N'Canada' );
INSERT [dbo].[Customers_1] VALUES (16, N'Elsworth', N'Chantrell', N'Sumberjo', N'Indonesia' );

-- table 2
INSERT [dbo].[Customers_2] VALUES (17, N'Beverly', N'Giraudy', N'Cigembong', N'Indonesia' );
INSERT [dbo].[Customers_2] VALUES (18, N'Tamas', N'Zavattiero', N'Jangkat', N'Indonesia' );
INSERT [dbo].[Customers_2] VALUES (19, N'Benedick', N'Schaffler', N'Itaparica', N'Brazil' );
INSERT [dbo].[Customers_2] VALUES (20, N'Leonard', N'Brimman', N'Moscow', N'Russia' );
INSERT [dbo].[Customers_2] VALUES (21, N'Morton', N'Capelle', N'Uk', N'Russia' );
INSERT [dbo].[Customers_2] VALUES (22, N'Larissa', N'Rawle', N'Tembayangan Barat', N'Indonesia' );
INSERT [dbo].[Customers_2] VALUES (23, N'Karalee', N'Wall', N'Clifden', N'Ireland' );
INSERT [dbo].[Customers_2] VALUES (24, N'Miller', N'Shakesbye', N'Gongnong', N'China' );
INSERT [dbo].[Customers_2] VALUES (25, N'Deidre', N'Cacacie', N'Itapeva', N'Brazil' );
INSERT [dbo].[Customers_2] VALUES (26, N'Gerri', N'Fawcus', N'Karma', N'Belarus' );
INSERT [dbo].[Customers_2] VALUES (27, N'Rudie', N'Helix', N'Gamut', N'Philippines' );
INSERT [dbo].[Customers_2] VALUES (28, N'Maddi', N'Rounce', N'Gavrilov-Yam', N'Russia' );
INSERT [dbo].[Customers_2] VALUES (29, N'Cornall', N'Beazer', N'Lérida', N'Colombia' );
INSERT [dbo].[Customers_2] VALUES (30, N'Ardelia', N'Pearcehouse', N'Fontenay-sous-Bois', N'France' );
INSERT [dbo].[Customers_2] VALUES (31, N'Adrian', N'Legh', N'Porto Velho', N'Brazil' );
INSERT [dbo].[Customers_2] VALUES (32, N'Buiron', N'Eads', N'Göteborg', N'Sweden' );

-- table 3
INSERT [dbo].[Customers_3] VALUES (33, N'Danette', N'Howick', N'Mombasa', N'Kenya' );
INSERT [dbo].[Customers_3] VALUES (34, N'Geno', N'Hannaway', N'Bantuanon', N'Philippines' );
INSERT [dbo].[Customers_3] VALUES (35, N'Kaleb', N'Hoovart', N'Largo', N'United States' );
INSERT [dbo].[Customers_3] VALUES (36, N'Yanaton', N'Coneybeare', N'Yanqi', N'China' );
INSERT [dbo].[Customers_3] VALUES (37, N'Jaimie', N'Zincke', N'Greeley', N'United States' );
INSERT [dbo].[Customers_3] VALUES (38, N'Bonnibelle', N'Dorman', N'Canhestros', N'Portugal' );
INSERT [dbo].[Customers_3] VALUES (39, N'Baxter', N'Howland', N'Finzes', N'Portugal' );
INSERT [dbo].[Customers_3] VALUES (40, N'Elmira', N'Dary', N'Lubao', N'China' );
INSERT [dbo].[Customers_3] VALUES (41, N'Beitris', N'Teresi', N'Impendle', N'South Africa' );
INSERT [dbo].[Customers_3] VALUES (42, N'Gustavo', N'Poston', N'Tuquan', N'China' );
INSERT [dbo].[Customers_3] VALUES (43, N'Renae', N'Macenzy', N'Al Ghayl', N'Yemen' );
INSERT [dbo].[Customers_3] VALUES (44, N'Solly', N'Davydenko', N'Durham', N'United States' );
INSERT [dbo].[Customers_3] VALUES (45, N'Herve', N'Baxandall', N'Ponggeok', N'Indonesia' );
INSERT [dbo].[Customers_3] VALUES (46, N'Page', N'Meek', N'Gufeng', N'China' );
INSERT [dbo].[Customers_3] VALUES (47, N'Cosme', N'Cranage', N'Willowmore', N'South Africa' );
INSERT [dbo].[Customers_3] VALUES (48, N'Lind', N'Bransden', N'Laval', N'France' );

Vérifier le nombre de lignes dans chaque table.

select count(*) from [Customers_1];
select count(*) from [Customers_2];
select count(*) from [Customers_3];

Vérifier le nombre de lignes de chaque tables avec une requête SQL depuis SSMS

3. Créer un flux de données SSIS avec la transformation Union All

Utiliser autant de source que de tables à regrouper dans le package SSIS. Pour faire fonctionner le composant, il faut au moins 2 tables, et il faut que la structure des colonnes à regrouper soit identiques.

Ajouter trois composants OLE DB Source et les renommer de manière explicite
Ajouter la transformation Union All

Ajouter 3 Sources OLE DB et la transformation Union All

Configurer chaque Source pour pointer sur la table des clients correspondantes.

Paramétrer la première table source pour l'Union All SSIS — Paramétrer la première table source pour l’Union All SSIS

Ce cas est volontairement simple car toutes les tables ont la même structure. Le mapping des colonnes est donc automatique car SSIS se base sur le nom des colonnes au niveau des liaison entre les composants. Nommer les sources et les cibles autant que faire ce que peut facilite donc grandement le développement des flux de données Integration Services.

Paramétrer la deuxième table source pour l'Union All SSIS — Paramétrer la deuxième table source pour l’Union All SSIS

Paramétrer la troisième table source à regrouper avec SSIS et Union All

4. Vérifier le composant SSIS Union All pour grouper les données

Après avoir configurer toutes les tables sources, relier alors les composants OLE DB Source à la transformation Union All. Relier les tables dans l’ordre pour faciliter le contrôle et la correction du mapping dans le composant.

Relier les composants SSIS sources à la transformation pour regrouper les données

Ouvrir le composant de transformation et vérifier les colonnes. Ici toutes les colonnes ont des noms et des types identiques. Il est possible de :

Renommer les colonnes de sortie, pour cela cliquer et éditer le nom.
Changer les colonnes sources en cliquant sur une ligne et changeant la source depuis la source.

Mapping des colonnes SSIS à regrouper avec la transformation Union All

5. Paramétrer la table de destination

Ajouter enfin un composant OLE DB Destination et pointer sur la table des clients avec les données regroupées. C’est à dire la table Customers_All, créé à l’étape 1 du tutoriel.

Configurer la table cible avec le regroupement des données

6. Exécuter le package pour regrouper les données des trois tables

Appuyer sur F5 pour exécuter le package et le flux de données. Le but est de regrouper les données depuis les tables sources vers la table cible unique.

Exécuter avec succès le package SSIS et regrouper les données des trois tables en une

7. Vérifier les résultat avec une requête SQL depuis SSMS

Vérifier les données insérées dans la table cible avec une requête SQL à exécuter depuis SSMS.

SELECT COUNT(*) AS [Lines] FROM [Customers_All];

La table cible comporte bien les 48 lignes des 3 tables sources.

IL est aussi possible de contrôler les lignes d’une table SQL Server directement depuis les composants OLE DB SSIS.

Lire les données de la table cible SQL Server depuis le Viewer SSIS

Conclusion sur l’utilisation de SSIS Union All pour regrouper plusieurs tables

Ce tutoriel explique les concepts généraux et illustre l’utilisation du composant de regroupement de données SSIS avec un cas basique. Dans les projets, il est important de préparer les tables et leurs structures en amont pour assurer un mapping fluide et des types de données cohérents tout au long du processus de développement du projet ETL.