Load data from Excel file to SQL Server table using SSIS

This article demonstrate how to load data from Excel file into SQL Server table using SSIS.

Lets see step by step implementation of simple basic SSIS package which loads the data from Excel file into SQL Server table using SSIS.

As you can see here we have sample excel file which contains customer’s data, this file will be source file, and data will be loaded from source file to destination that will be SQL Server table.

Below is the SQL server table, currently there are no records in SQL Server table.

Lets open the SQL Server Data Tool (SSDT) and create a new package as shown below.

If you do not know how to create a SSIS project – refer Create a new SSIS project.

Now in Solution Explorer, select SSIS packages folder and right click then then select New SSIS Package.

Once the package is created, give it meaningful name as shown below.

Now go to Toolbox and select Data Flow Task and drag it into Control Flow view as shown below.

Now double click on Data Flow Task or Right click then select Edit.. from context menu, it will open a Data Flow view for selected Data flow task.

Once you double click on Data flow task, you will be navigated to Data Flow view.

Now here we will add a Excel Source, and configure a connection for excel file.

Lets go to Toolbox then expand Other Sources and select Excel Source component and drag into Data Flow View as shown below.

Now double click on Excel Source.

Once you double click on Excel Source, a Excel Source Editor window opens, here you can select an existing connection or can create a new connection manager.

Lets create a new connection manager, click on New.. button, after that a new window Excel Connection Manager opens, now you need to select excel file path.

Also can specified excel file version, and if first row in your excel file contains column then keep the option First row has column names as checked else can uncheck.

Lets select the excel file from location, then click on Open button.

After that, you will see excel path, and excel version are specified. Now click on OK button.

Now you can see a connection is created, and in Data Access Mode you will see four mode as follows:

• • Table Or View: This option displays the list of available worksheet in excel file, and you have to select desired sheet.
  • Table name Or View name variable: In this option, you need to specify the variable name which store the table or view name.
  • SQL Command: This option allow you to specified the SQL command.
  • SQL Command from variable: In this option you can specified a variable name which store the SQL command.

Here we go with Table Or View mode, so just leave it as is.

As we have selected data access mode as Table Or View, so we need to select a worksheet from Name of the Excel sheet dropdown.

Once you done with this, you can click on Preview button to see the data preview.

Now go to Column tab, you can verify the columns also can remove the column by unchecking the column.

After that in Error Output Tab,  you can specified the behavior of the component in case of failure, you can specified any one option as follows:

1. • Ignore Failure: It ignores any failure while reading rows from source and the package will continue executing even any error occurred.
   • Redirect Row: It redirects the failed rows to other component which is connected with the error precedence constraints.
   • Fail component: It stops the execution of package in case of any failure.

We go with default selected value that is Fail component.

Now click on OK button to complete the configuration for Excel Source.

You can also see a Excel connection manager is created that can be seen in Connection Manager window as shown below.

In case you want to rename the Excel source component then right click on Excel source and select Rename from context menu.

Now we will add a Destination component into Data flow view.

As our destination is SQL Server table so we will add a OLEDB Destination, for this go to Other Destinations in toolbox tab and expand it and then select OLEDB Destination and Drag it into Data Flow view.

Now will add the path, means connect the Excel source component which is the source of path to the OLEDB destination component which is the end of path.

For this select Excel source component then drag the arrow and connect it to OLEDB destination as shown below.

A path connects two components in a data flow by connecting the output of one data flow component to the input of another component. It has a source and a destination.

Once you done it will look like as shown below.

Now double click on OLEDB Destination component.

Now double click on OLEDB destination component. You will see a OLEDB Destination Editor window opens.

Here you can select an existing connection or configure a new Database connections.

To create a new connection click on New.. button.

After that a Configure OLEDB connection Manager window opens, click on New.. button.

Once you click on New button..  a connection Manager window opens.

Here you can specified a Server name, Authentication mode, also can select Database name.

Once you done with all these you can also verify the connection by clicking on Test connection button.

After that just click on OK button.

Once you click on OK button, you can see a connection string is created as you specified.

Once you click on OK, you will get back to OLEDB connection Editor window.

In Connection Manger tab, you can select the table to which you want to load flat file data.

So in Data access mode option select as select table or view, and in Name of the table or the view option select table name that is CustomeDetails.

Now in Mappings tab, you can map the appropriate input columns with output columns. You can see by default source file columns that is input columns and destination table column that is output columns are mapped automatically.

If any columns are not mapped correctly or missed then you can rectify or mapped them manually.

Next, in Error output tab, you can specified the behavior of the component in case of failure, which is same as we have discussed above when configuring the Excel source component.

We go with default value Fail Component.

Now click on OK button to complete the configuration.

Once you done with this, you will see a OLEDB connection manager is created in Connection Managers window.

Now our package is ready, lets execute the package, right click on Data flow view and select Execute task from context menu.

Once you select Execute task, you can see Package run successfully, and can see 9 rows are loaded to SQL Server table.

Lets quickly check the SQL Server table, and you can see there are 9 rows.

 

Also Read..

Create a new SSIS project.

Load Flat file data into SQL Server table using SSIS