SSIS - Import MS Excel Worksheet data into a Database


This article will help you to understand that how to import the Excel worksheet data into database using data conversion function. Data Conversion is used to change the data type of a column. Let say we have Unicode String format in Source database and the same column is defined as Non Unicode in destination. In this case we need to convert the data. This is the case when data conversion is used.

Introduction


There are two ways to import the data into the SQL database from excel source.

1. Using the Import Data wizard from SQL Server Management Studio.
By following the steps through the wizard, user can choose the source file, and the destination table to import the data.
Limitation :
Importing the data using wizard won't allow user to execute more complex tasks with different kind of the data, and multiple files. User can not manipulate the complex data while inserting into database from Excel source

2.Using SSIS packages.
It overcomes the limitation of Wizard method.
The SSIS package is a ETL (Extract-Transform-Load) tool and it can be used not just to import the data into the database, but to transform, manipulate the data and many other tasks.

Lets understand how to achieve this thing using SSIS Package.

Step 1
Open BIDS (Visual studio) and create new project.
Create new project in BIDS choosing Integration Services and give project name as shown in figure.
create package

Step 2
Add Data flow task from SSIS Toolbox in control flow surface as shown in figure
DFT

Step 3
Double click on Data Flow Task which will take you to the data flow design window.
• Add Excel source and configure it as shown in figure.

Excel source

Click New and give proper Excel file name with folder path shown in figure below.

excel file

After giving path, we need to select the sheet name and select column from left panel as shown below

excelsheet

Step 4
Add data conversion from SSIS toolbox and connect the excel source to it.
Select columns which needs to be converted to insert the data into database

conversion

Step 5
Add OLE DB Destination from SSIS Toolbox. OLE DB destination is used as destination to insert data into SQL Server Database.
Perform following steps to configure the OLE DB destination
• Double click on OLE DB Destination and give appropriate connection of database as shown below.

Click "New" to configure the database

config_database

Give Server name ,database name and test the connection as shown in figure.
Press OK.
We can give specific authentication as Windows authentication or SQL Server authentication (UserName and Password)

config_database1

Now Select the table from the database dropdown or you can create table while configuring as shown below.

• Click New.
new table
• Once we click "New" then tool will generate create table query as per the input.
Refer the follwing figure to understand better.
createtabletext
• Now Map the columns properly.
• Since we have used Data conversion we need to map the columns according to the output name from data conversion. Refer following figure.

map columns

• Click OK and run the package
run

Step 6
See the output in Database table.
tableoutput

Points to Remember
• Give proper connection for Excel Source and Database destination.
• You can use other transformation tasks from SSIS toolbox and perform the manipulation.
• While inserting the data from Source to destination, give only required Column data for the better performance of the package.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: