Union All Transformation in SSIS is used to combine data from multiple sources (excel, flat files) and produce one output to store in destination table.
Procedure:-
- Creating package under Project “doc1”.
- Under Solution Explorer we need to rename the package as required. i.e. Union all.dtsx.
- Drag and Drop the Data Flow Task from the toolbox to the control flow region. Add a name to it of your choice.
- Double-click on the Data Flow Task. Drag and drop the 3 FlateFile sources from the toolbox menu. Rename the flatefile sources like stu1,stu2,stu3 as shown in below figure.
- Double-click on the stu1 a menu will pop up. Enter the Go to new and change the connection manager name in source1.Browse the file which contains the data in your file manager.
- Next go the columns and check whether the displayed columns matched the tables in the selected source file. Then click OK.
- Do the same for another flatfiles.
- Next Drag and drop the Union all Transformation from the toolbox menu.
- Join all the Flatfile sources to the Union All Transformations .
- Double click on the SSIS Union All Transformation will open the Union All Transformation editor window to configure it.
- Click on ok.
- Drag and drop the OLEDB Destinations from Other Destinations in SSIS Toolbox and drag and drop on the workspace.
- Double-click on the OLEDB destination a menu will pop up. Create at run time.
- Click on mappings and check the all mappings are mapped or not. Click on OK.
- We can now run the package with the run option present in the main menu. When we press the Start button then the package will start running, and you can view the status of your SSIS package.
- Verify the output in the SQL Server. Go to SQL Server .
