Import data from Excel to SQL Server using SSIS (SQL Server Integration Services)

 

In this blog post, I am going to show you how to import an excel file data into the database table using SQL Server Integration Services. 

Below is shown the sample excel file.

Step 01: -

First, we need to create a new database in the SQL Server. After that, we need to create a new table in the designed database structure to store excel file data.

Step 02: -

Create a new SSIS package for import the excel file data into the SQL Server table.

Step 03: -

Drag and drop the Data Flow Task into the Control Flow as shown below.

Step 04: -

Drag and drop the Excel Source into the Data Flow as shown below.

Step 05: -

We need to create a new excel connection manager. So, right-click on the connection manager window and select the New Connection.

Step 06: -

Select the connection manager type as Excel and click on the Add button.

Step 07: -

Configure the Excel connection manager by providing the excel file path. Then Excel Version is automatically selected. If your source excel file sheet has column headers. You need to tick on the "First Row has column headers." After that, click on the Ok button.

Step 08: -

Double click on the Excel Source. After that, configure the Excel Source Editor by choosing the source Excel Connection Manager and the Name of the Excel sheet as shown below. 

Step 09: -

Click on the Preview Button. Then we can see the data in the source Excel file.

Step 10: -

Drag and drop the Data Conversion into the Data Flow. After that, map the Excel Source and the Data Conversion as shown below.

Step 11: -

Double click on the Data Conversion. After that, configure the Data Conversion Transformation Editor by providing information as shown below.

Step 12: -

Drag and drop OLE DB Destination into the Data Flow. After that, map the Data Conversion and the OLE DB Destination as shown below.

Step 13: -

Double click on the OLE DB Destination. After that, click on the New button as shown below.

Step 14: -

Click on the New button in the Configure OLE DB Connection Manager window. 

Step 15: -

Configure the Connection Manager by providing SQL Server Database login information as shown below. (In here, if you can choose either Windows Authentication Or SQL Server Authentication)

Step 16: -

Click on the Test Connection button. Then you can see the succeeded message as shown  below.

Step 17: -

Select the destination table in the database. 

Step 18: -

Move into the Mapping tab window. After that, map the Available Input Columns and Available Destination Columns as shown below. 

Step 19: -

Save the Package and click on the Start Button. Then you can see the successful transition from the source excel file to the destination table.

Step 20: -

Move into the SQL Server Management Studio and execute the following SQL queries as shown. Then you can see the available data in the destination table.

Thank You!