Merging Data from multiple sources using SSIS

In this post i have described about have to get data from different sources and merge it.

We will use four different sources viz.

1. Access DB
2. Text File
3. Excel Workbook
4. SQL Server 2012

Step 1: Prepare Source and Destination as shown below

• Access DB

       

•  Text File (using Notepad)

 

• Excel Workbook

• OLEDB Source
• CREATE TABLE [dbo].[SQLData](
      [ID] [int] NULL,
      [Name] [nvarchar](50) NULL,
      [Age] [int] NULL
  ) ON [PRIMARY]
  
  GO
  INSERT [dbo].[SQLData] ([ID], [Name], [Age]) VALUES (5, N'Praveena', 32)
  GO
  INSERT [dbo].[SQLData] ([ID], [Name], [Age]) VALUES (6, N'Ediga', 23)
  GO

• OLEDB Destination
• Create destination table into sql server database using below script.
• CREATE TABLE [dbo].[OLE DB Destination](
      [ID] [int] NULL,
      [Name] [nvarchar](255) NULL,
      [Age] [int] NULL
  ) ON [PRIMARY]

Step 2: New create new SSIS Project using "SQL Server Data Tool" and add "Data Flow Task" and then  add connection managers for each of the source and destination.

• Connection Manager - Access Source

• Connection Manager - Text Source

• Connection Manager - Excel Source

• Connection Manager - OLEDB Source/OLEDB Destination

Step 3:

• Now add 2 "OLEDB Source" transformas and map it to "Access Connection manager" and "SQL Connection Manager" respectively. 

• Also add "Flat file Source" and "Excel Source" and map them to respective connection managers.

Step 4:

• Drag and Drop "Union All" transform and connect output from all sources to it. This will help merge data from all source.
• Always prefer  "Union All" over "Merge Join" as it is more efficient.
• Add "Data Conversion" before "Union All" whereever data type missmatch happens as shown in screen below.

Step 4: Configure "Union All" Input and Output columns as shown in screen below.

Step 5: Finally, Add "OLEDB Destination" transform and map it to "SQL Connection Mnager" and connect output of "Union All" to "OLEDB Destination".