Load Database Records To Another Table And Excel Sheet Using SSIS

Introduction

In this session, we will briefly discuss how to load data from one table to another table and Excel sheets at the same time using SSIS package.

Let’s discuss about the step by step process to implement the data load to multiple places. We will discuss the following points,

1. Create the database, tables and records
2. Move data from Original table to Archive table and excel sheet
3. Multicast operation
4. Data conversion

I have created the database name called “Contact”. Here I have two tables Personal and Archive_Personal.

In the database, I will keep the Original records in Personal table and my archive records will be stored in archive_personal table.

Find the below screenshot. You can see that I have the selected records in the “Personal” table.

If you want to load the records from one table to another table, you should have an SSIS package.

Let’s discuss how to create the SSIS package file.

If you want to create a new package, you should follow the given steps in Visual Studio 2010/2013/2015.

• Open the Visual Studio 2015
• Select New - Project
• Click on OK

The new project window will be displayed on the desktop. Find the below screenshot.

Here you should select the Integration Services - Integration Services Project - Click OK.

When you click on “OK”. You will see the below screen on desktop. The package has been created along with the .dtsx file.

Double click on the package file. Then drag and drop the “Data Flow Task” from the SSIS Toolbox.

Then double click on the Data Flow Task. You will move into the process creation area.

Here I have implemented the package to move records from one table another table and excel sheet.

1. Created the OLE DB Source (To fetch the records)
2. Multicast (Split the records to archive table and excel sheet)
3. OLE DB Destination & Excel Destination (To store records)