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.
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.
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.
Open BIDS (Visual studio) and create new project.
Create new project in BIDS choosing Integration Services and give project name as shown in figure.
Add Data flow task from SSIS Toolbox in control flow surface as shown in figure
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.
Click New and give proper Excel file name with folder path shown in figure below.
After giving path, we need to select the sheet name and select column from left panel as shown below
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
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
Give Server name ,database name and test the connection as shown in figure.
We can give specific authentication as Windows authentication or SQL Server authentication (UserName and Password)
Now Select the table from the database dropdown or you can create table while configuring as shown below.
• Click New.
• Once we click "New" then tool will generate create table query as per the input.
Refer the follwing figure to understand better.
• 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.
• Click OK and run the package
See the output in Database table.
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.