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

Create the database, tables and records

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.

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.

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)

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”
  
  

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.

You can see the database connection in connection manager.

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.

Create the new Excel sheet and save it in desktop.

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

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

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.

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.

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

Please leave your comments, if you require anything.

Thank you.