MSBI : SQL Server Integration Service (SSIS) - Loading Data from Excel to SQL Server Table

SQL Server Integration Services (SSIS) is a component of SQL Server which can be used to perform a wide range of Data Migration and ETL operations. SSIS is a component in MSBI process of SQL Server. This is a platform for Integration and Workflow applications. It is known for a fast and flexible OLTP and OLAP extensions used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and multidimensional data sets.

In this post i'm going to explain how to load Excel data to SQL Server database table.

Create New SSIS projec by clicking the File--> New-->Project.

Select the Business intelligent Project Type select the Integration Service project template.Gave the name as you wish.in here i'm naming as 'Excel2SQLServer Table'.Click Ok.

Project will be open as below.

Drag and drop 'Data Flow Task' from Control flow Items category to Control Flow tab.

Create EMP.xlsx file as below and going to upload these records to SQL Server Database.

To Create Excel file connection click on the Connection Manager area and Select New Connection.

Select the Excel Connection Type and click Add.

Brows the Excel file where you have saved in your computer and select the Excel Version.

Then Excel Connection will be seen as below.

Go to the Data flow task tab and Drag and drop the Excel Source from Data Flow Sources category.

Double Click on the Excel Source component and select The earlier created Excel connection.it will select by default.

Then Give the Name of the Excel sheet.

If you click on the Preview button, You can see the excel sheet's data.it means your Excel connection has been created successfully.Then Red cross of the Excel source will be disappear.

Create a SQL server table as below.

Create a OLE DB Connection to connect to the earlier table. Wright click on the Connection manager area and OLE DB connection type.

Create a New connection buy clicking New button.

In the connection manager window give the server name. Give username and password for login details of the server and select the Database. click on the Test connection and button and you will see the following message.

Then Click Ok OLE DB Connection Manger window.

Drag and drop the OLE DB Destination from Data Flow destination category to Data flow tab.

Select the Excel source. Drag and drop the green arrow from Excel source to OLE DB Destination.

Select the Excel source. Drag and drop the green arrow from Excel source to OLE DB Destination.
Then Drag and Drop the Data Conversion Component to Data Flow tab from Common Category. Connect Three component as below

Then Double click on the OLE DB connection and Select the Connection manager name, table of the SQL server. Double click on the Data Conversion tool and set as below.