{"id":25791,"date":"2023-05-09T06:11:00","date_gmt":"2023-05-09T04:11:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=25791"},"modified":"2023-05-16T20:17:33","modified_gmt":"2023-05-16T18:17:33","slug":"group-multiples-tables-with-ssis","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/ssis\/group-multiples-tables-with-ssis\/","title":{"rendered":"Group multiple tables with SSIS Union All"},"content":{"rendered":"\n

Develop an SSIS package step-by-step to group data from multiple SQL Server tables into one using the Union All transformation.<\/em><\/strong><\/h4>\n\n\n\n

To group the contents of several tables or data sources in general, it is possible to use a SQL Union All query, here is how to do it without code with SSIS. A prerequisite for this type of query is to build all source tables with the same data structure, i.e., column type and length. Or at least at the input of the Union All component. This operation is equivalent to a classic SQL Union All query.<\/p>\n\n\n\n

1. Create several tables to be grouped and the target table<\/h2>\n\n\n\n

First of all, create three source tables with the same structure and insert rows into them. Each table has 16 rows, for a total of 48 rows<\/strong>.<\/p>\n\n\n\n

-- Create the first customers table\nCREATE TABLE [dbo].[Customers_1](\n   [CustomerID] [int] NOT NULL,\n   [FirstName] [nvarchar](20) NULL,\n   [LastName] [nvarchar](20) NULL,\n   [City] [nvarchar](20) NULL,\n   [Country] [nvarchar](50) NULL,\n   CONSTRAINT [CustomersPKCustomerID_1] PRIMARY KEY CLUSTERED ([CustomerID] ASC)\n);\nGO\n\n-- Create the second customers table\nCREATE TABLE [dbo].[Customers_2](\n   [CustomerID] [int] NOT NULL,\n   [FirstName] [nvarchar](20) NULL,\n   [LastName] [nvarchar](20) NULL,\n   [City] [nvarchar](20) NULL,\n   [Country] [nvarchar](50) NULL,\n   CONSTRAINT [CustomersPKCustomerID_2] PRIMARY KEY CLUSTERED ([CustomerID] ASC)\n);\nGO\n\n-- Create the third customers table\nCREATE TABLE [dbo].[Customers_3](\n   [CustomerID] [int] NOT NULL,\n   [FirstName] [nvarchar](20) NULL,\n   [LastName] [nvarchar](20) NULL,\n   [City] [nvarchar](20) NULL,\n   [Country] [nvarchar](50) NULL,\n   CONSTRAINT [CustomersPKCustomerID_3] PRIMARY KEY CLUSTERED ([CustomerID] ASC)\n);\nGO\n<\/pre>\n\n\n\n
Create a fourth table, the target, used to store the results, i.e., all data from the 3 sources tables.<\/p>\n\n\n\n
-- Create the target and global customers table\nCREATE TABLE [dbo].[Customers_All](\n   [CustomerID] [int] NOT NULL,\n   [FirstName] [nvarchar](20) NULL,\n   [LastName] [nvarchar](20) NULL,\n   [City] [nvarchar](20) NULL,\n   [Country] [nvarchar](50) NULL,\n   CONSTRAINT [CustomersPKCustomerID_All] PRIMARY KEY CLUSTERED ([CustomerID] ASC)\n);\nGO<\/pre>\n\n\n\n
2. Feed the SQL tables to be grouped with SSIS<\/h2>\n\n\n\n
Download this file and rename it with an .sql extension. Or run its contents directly from an SSMS window.<\/p>\n\n\n\n
insert_customers_data_in_3_tables.txt<\/strong><\/a>Donwload  the SQL insert statements<\/a><\/div>\n\n\n\n
If you are unable to download the file because of security restrictions, then here are its contents: <\/p>\n\n\n\n
-- table 1\nINSERT [dbo].[Customers_1] VALUES (1, N'Ali', N'Ahmed', N'Cairo', N'Egypt' );\nINSERT [dbo].[Customers_1] VALUES (2, N'Johnny', N'John', N'Toronto', N'Canada' );\nINSERT [dbo].[Customers_1] VALUES (3, N'John', N'Doe', N'Mexico City', N'Mexico' );\nINSERT [dbo].[Customers_1] VALUES (4, N'Shu', N'Abbas', N'Paris', N'France' );\nINSERT [dbo].[Customers_1] VALUES (5, N'Jeane', N'Raffin', N'Liushutun', N'China' );\nINSERT [dbo].[Customers_1] VALUES (6, N'Legra', N'Leate', N'B\u0142aszki', N'Poland' );\nINSERT [dbo].[Customers_1] VALUES (7, N'Sullivan', N'Goadby', N'Xiaoguwei', N'China' );\nINSERT [dbo].[Customers_1] VALUES (8, N'Tom', N'Ellams', N'Lop Buri', N'Thailand' );\nINSERT [dbo].[Customers_1] VALUES (9, N'Trace', N'Fowell', N'Sriwing', N'Indonesia' );\nINSERT [dbo].[Customers_1] VALUES (10, N'Christoffer', N'Rochford', N'Mburukullu', N'Indonesia' );\nINSERT [dbo].[Customers_1] VALUES (11, N'Dru', N'Dunridge', N'Mistrat\u00f3', N'Colombia' );\nINSERT [dbo].[Customers_1] VALUES (12, N'Dud', N'Gravenor', N'Sandaogou', N'China' );\nINSERT [dbo].[Customers_1] VALUES (13, N'Lori', N'Garshore', N'Jeziora Wielkie', N'Poland' );\nINSERT [dbo].[Customers_1] VALUES (14, N'Cordy', N'Kentish', N'Vydreno', N'Russia' );\nINSERT [dbo].[Customers_1] VALUES (15, N'Ancell', N'Wileman', N'Claresholm', N'Canada' );\nINSERT [dbo].[Customers_1] VALUES (16, N'Elsworth', N'Chantrell', N'Sumberjo', N'Indonesia' );\n\n-- table 2\nINSERT [dbo].[Customers_2] VALUES (17, N'Beverly', N'Giraudy', N'Cigembong', N'Indonesia' );\nINSERT [dbo].[Customers_2] VALUES (18, N'Tamas', N'Zavattiero', N'Jangkat', N'Indonesia' );\nINSERT [dbo].[Customers_2] VALUES (19, N'Benedick', N'Schaffler', N'Itaparica', N'Brazil' );\nINSERT [dbo].[Customers_2] VALUES (20, N'Leonard', N'Brimman', N'Moscow', N'Russia' );\nINSERT [dbo].[Customers_2] VALUES (21, N'Morton', N'Capelle', N'Uk', N'Russia' );\nINSERT [dbo].[Customers_2] VALUES (22, N'Larissa', N'Rawle', N'Tembayangan Barat', N'Indonesia' );\nINSERT [dbo].[Customers_2] VALUES (23, N'Karalee', N'Wall', N'Clifden', N'Ireland' );\nINSERT [dbo].[Customers_2] VALUES (24, N'Miller', N'Shakesbye', N'Gongnong', N'China' );\nINSERT [dbo].[Customers_2] VALUES (25, N'Deidre', N'Cacacie', N'Itapeva', N'Brazil' );\nINSERT [dbo].[Customers_2] VALUES (26, N'Gerri', N'Fawcus', N'Karma', N'Belarus' );\nINSERT [dbo].[Customers_2] VALUES (27, N'Rudie', N'Helix', N'Gamut', N'Philippines' );\nINSERT [dbo].[Customers_2] VALUES (28, N'Maddi', N'Rounce', N'Gavrilov-Yam', N'Russia' );\nINSERT [dbo].[Customers_2] VALUES (29, N'Cornall', N'Beazer', N'L\u00e9rida', N'Colombia' );\nINSERT [dbo].[Customers_2] VALUES (30, N'Ardelia', N'Pearcehouse', N'Fontenay-sous-Bois', N'France' );\nINSERT [dbo].[Customers_2] VALUES (31, N'Adrian', N'Legh', N'Porto Velho', N'Brazil' );\nINSERT [dbo].[Customers_2] VALUES (32, N'Buiron', N'Eads', N'G\u00f6teborg', N'Sweden' );\n\n-- table 3\nINSERT [dbo].[Customers_3] VALUES (33, N'Danette', N'Howick', N'Mombasa', N'Kenya' );\nINSERT [dbo].[Customers_3] VALUES (34, N'Geno', N'Hannaway', N'Bantuanon', N'Philippines' );\nINSERT [dbo].[Customers_3] VALUES (35, N'Kaleb', N'Hoovart', N'Largo', N'United States' );\nINSERT [dbo].[Customers_3] VALUES (36, N'Yanaton', N'Coneybeare', N'Yanqi', N'China' );\nINSERT [dbo].[Customers_3] VALUES (37, N'Jaimie', N'Zincke', N'Greeley', N'United States' );\nINSERT [dbo].[Customers_3] VALUES (38, N'Bonnibelle', N'Dorman', N'Canhestros', N'Portugal' );\nINSERT [dbo].[Customers_3] VALUES (39, N'Baxter', N'Howland', N'Finzes', N'Portugal' );\nINSERT [dbo].[Customers_3] VALUES (40, N'Elmira', N'Dary', N'Lubao', N'China' );\nINSERT [dbo].[Customers_3] VALUES (41, N'Beitris', N'Teresi', N'Impendle', N'South Africa' );\nINSERT [dbo].[Customers_3] VALUES (42, N'Gustavo', N'Poston', N'Tuquan', N'China' );\nINSERT [dbo].[Customers_3] VALUES (43, N'Renae', N'Macenzy', N'Al Ghayl', N'Yemen' );\nINSERT [dbo].[Customers_3] VALUES (44, N'Solly', N'Davydenko', N'Durham', N'United States' );\nINSERT [dbo].[Customers_3] VALUES (45, N'Herve', N'Baxandall', N'Ponggeok', N'Indonesia' );\nINSERT [dbo].[Customers_3] VALUES (46, N'Page', N'Meek', N'Gufeng', N'China' );\nINSERT [dbo].[Customers_3] VALUES (47, N'Cosme', N'Cranage', N'Willowmore', N'South Africa' );\nINSERT [dbo].[Customers_3] VALUES (48, N'Lind', N'Bransden', N'Laval', N'France' );<\/pre>\n\n\n\n
Then check the number of lines in each table.<\/p>\n\n\n\n
select count(*) from [Customers_1];\nselect count(*) from [Customers_2];\nselect count(*) from [Customers_3];<\/pre>\n\n\n
\n
\"Check
Check the number of rows in each table with an SQL query from SSMS<\/em><\/figcaption><\/figure><\/div>\n\n\n
3. Create an SSIS data stream with the Union All transformation<\/h2>\n\n\n\n
Use as many sources as there are tables to be grouped in the SSIS package. To make the component work, you need at least 2 tables, and the structure of the columns to be grouped must be identical.<\/p>\n\n\n\n
    \n
  1. Add three OLE DB Source<\/em><\/strong> components and rename them explicitly.<\/li>\n\n\n\n
  2. Add the Union All<\/em><\/strong> transformation.<\/li>\n<\/ol>\n\n\n
    \n
    \"Add
    Add 3 OLE DB sources and the Union All transformation to the SSIS data flow<\/em><\/figcaption><\/figure><\/div>\n\n\n
    Configure each Source to point to the corresponding customer table.<\/p>\n\n\n
    \n
    \"Set
    Set up the first source table for the Union All SSIS<\/em><\/figcaption><\/figure><\/div>\n\n\n
    This example is deliberately simple because all the tables have the same structure. The mapping of columns is therefore automatic because SSIS is based on the names of the columns in the links between components. Naming the sources and targets as much as possible therefore greatly facilitates the development of Integration Services data flows.<\/a><\/p>\n\n\n
    \n
    \"Set
    Set up the second source table for the Union All SSIS<\/em><\/figcaption><\/figure><\/div>\n\n
    \n
    \"Set
    Set up the third source table to be grouped using the SSIS Union All<\/em><\/figcaption><\/figure><\/div>\n\n\n
    4. Check the SSIS Union All component<\/h2>\n\n\n\n
    After configuring all the source tables, then link the OLE DB Source components<\/em><\/strong> to the Union All transformation<\/em><\/strong>. Link the tables in order to facilitate control and correction of the mapping in the component.<\/p>\n\n\n
    \n
    \"Link
    Link the 3 source SSIS components to the transformation to group the data<\/em><\/figcaption><\/figure><\/div>\n\n\n
    Open the transformation component and check the columns. Here all columns have identical names and types. It is possible to perform the following operations.<\/p>\n\n\n\n