Load Database Records To Another Table And Excel Sheet Using SSIS

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.

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.

SSIS

 

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
Create the database, tables and records

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

SSIS

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.

SSIS

Move data from Original table to Archive table and Excel sheet

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.

SSIS

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

SSIS

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

SSIS

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

SSIS

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)

SSIS

Create connections

You should connect the database from Visual Studio to communicate for data.

  • Right click on the “Connection Manager” Area (In bottom of the page)
  • Click “New OLE DB Connection

    SSIS

The “Configure OLE DB Connection Manager” window will be appearing on the desktop.

  1. Click on “New” button.
  2. Configure the Server name in dropdown.
  3. Set the authentication mode. Make sure you have selected the database from the list.
  4. Hit “Test Connection”.
  5. If the connection succeeds, you will get the “Test Connection Succeeded” notification on desktop.

Find the below screenshot for reference.

SSIS

You can see the database connection in connection manager.

SSIS

Double click on “OLE DB Source” in Data Flow. Then connect the OLE DB manager.

Then select the table from dropdown box, where you want to get records from database. Refer point #2.

Connect the database with OLE DB Source and OLE DB Destination.

SSIS

Create the new Excel sheet and save it in desktop.

SSIS

Configure the “Excel Connection Manager” with the created one.

SSIS

Right click on “Data Flow Task”. Click on “Execute Task”.

SSIS

You can see that, package has been executed successfully.

Not only that, it contains the number of record loaded to table and excel sheet.

Multicast operation

The multicast operation is used to split records to multiple destination.

SSIS

In the SSIS package, we have used the multicast operation to pass records to table and Excel sheets.

Data conversion

The data conversion is used to convert the data type of the column.

SSIS

SSIS

Eventually, the records has been loaded into archive table and Excel sheet at same time.

SSIS

Please leave your comments, if you require anything.

Thank you.