Rajendra Gupta
add an excel source

Import data from multiple Excel files into SQL Server tables using an SSIS package

December 13, 2019 by

This article explores an SSIS package for importing multiple Excel files data into SQL Server tables.

Introduction

Recently I come across a situation in which third-party vendors send multiple Excel files to load data into SQL Server tables. We can use SSMS Import and Export Wizards for data import. You can read more about this in How to import/export data to SQL Server using the SQL Server Import and Export Wizard.

Import Data SSMS

Suppose you received multiple Excel files (let’s say 50) for data import. You need to launch an import wizard 50 times and complete it. It is a manual, tedious, and time-consuming task.

As I started earlier in my articles, SSIS is a true friend for DBA and developers. It comes for rescue in such situations. Let’s explore the SSIS solution in this article.

Sample data

For this article, I want data import from the following Excel files:

Sample data

Each Excel file contains one row, and source column shows the excel file name:

View excel file data

Prerequisites

  • SQL Server Data Tools or Visual Studio 2019
  • SQL Server instance

Create an SSIS package for the data import from multiple Excel files

First, we will create an SSIS package for importing a single Excel file data into the SQL Server table. Later, we will convert the same package and import multiple Excel files data using SSIS variables and parameters.

Create a SQL table for data import

We require a SQL table that will have data from SSIS import operation. Execute the following script for creating a SQL table for data import:

Add a Source excel file connection

Open Visual Studio and create a new SSIS package project. In Control Flow, right-click and go to New Connection:

New connection SSIS

It shows all available connection types. Click on EXCEL connection manager and add it:

Connection Manager types

In Excel Connection Manager, provide the path of Excel file, and it automatically selects the Microsoft Excel versions. The first row of the Excel sheet contains the column header; therefore, we have a check on the First row has column names option:

Excel connection Manager

Click OK, and it shows the Excel connection in the connections manager area:

excel connection in the connections manager

Add a destination OLE DB connection

Now, add a destination OLE DB connection manager. Click on add new connection and select an OLE DB connection:

Add a destination OLE DB connection

Add SQL Server instance name, authentication (windows\SQL) in the OLE DB connection manager:

Add SQL Server instance name, authentication (windows\SQL) in the OLE DB connection

We have both source and destination connections in the connection manager window. We will use these connections in the data flow and control flow tasks of the SSIS package:

Connection Manager

Add a data flow task for data import from Excel to SQL table

Add a Data Flow Task in the Control Flow. This data flow task will flow data from the Excel files into SQL Server tables:

Add a data flow task for data import

Rename Data Flow Task to Data import from multiple excels files. It is an optional step. However, it is a better approach to renaming the tasks. We can easily recognize the task desired behavior using the custom names:

Rename data flow task

Double-click on this data flow task, and it takes you to the Data Flow tab. Drag an Excel Source to the data flow. A red cross icon shows that configuration is required for this SSIS task:

add an excel source

Double-click on this excel source and in Excel Source Editor, select the Excel sheet that contains data. We should have data on similar sheets of all Excel files:

Select the sheet that contains data

Click on Preview, and we can get a glimpse of the Excel sheet data:

Preview data

In the left-hand menu, click on Columns and verify the Excel sheet columns. If we want excluding any column from data import, we can remove the checkmark from the particular column:

Validate Mappings

Click on OK, and it shows the successful connection of an Excel Source:

Succeessful excel connection

Now, drag an OLE DB Destination and connect it with the Excel Source using blue precedence constraint:

drag an OLE DB destination

Double-click on OLE DB Destination and it opens the OLE DB Destination Editor. Specify the OLE DB connection and SQL table that we created earlier:

Select the name of the table

We need to verify the source and destination mapping. Click on Mappings and verify it:

Validate mappings

Click OK, and it verifies the conversion error between Unicode and non-Unicode string data types:

Unicode and Non-Unicode mapping error

To resolve this, we need a Data Conversion task from the SSIS toolbox. Drag it from SSIS toolbox and connect it with the Excel Source:

Add a Data conversion task

Double-click on Data Conversion. It opens the Data Conversion Transformation Editor. Select the Input Column (Source) and change the data type as Unicode_string[D_WSTR]. We also change the EmpName data type as String[DT_STR]:

Select the columns and change their properties

In the above screenshot, we also changed the output column alias. Click OK and add precedence constraint from data conversion task to OLE DB Destination:

Error in OLE DB destination

We can still see a red cross on the OLE DB Destination task. It requires a configuration with the new columns that we derived from the data conversion.

Open the OLE DB Destination Editor again and navigate to Mappings. In the Mapping, change the input columns, as shown below:

Validate mapping with new columns

Click OK. We can see that all configuration of the SSIS package is successful:

Connect data conversion with destination task

Execute the package, and it shows successful data import from a single Excel file:

Execute the package

Now, execute the following TRUNCATE TABLE statement to configure SSIS package data import from multiple Excel sheets:

Disable SSIS project debugging

Right-click on the SSIS project and go to properties. In the debugging menu, disable the Run64BitRunTime option:

Disable SSIS project debugging

Click Apply and OK to save changes.

Add variables in SSIS package

We will use SQL variables for defining multiple Excel sheets in a for each loop. Right-click in data flow and add variables.

  • Directory: It holds the path of all Excel files
  • ExcelPath: It holds the path of an Excel file along with file name

Add variables in SSIS package

Under the Connection Manager, click on the Excel Connection Manager and view its properties. Click on Expressions, as shown below:

Excel connection expression

In the Expression Builder, expand the Variables and Parameters folder. Drag the variable [User:: ExcelPath] to the expression and evaluate the expression. It should show the variable value we defined earlier:

Expression builder

Click OK, and you can see the variable in the Property Expressions Editor:

property expression editor

Click OK, and it shows a symbol “fx” in the Excel Connection Manager:

excel connection manager

Configure a Foreach Loop Container for data import from multiple Excel files

Now, go back to Control Flow and drag a Foreach Loop Container. We use this container for starting a loop for the number of executions specified. You can read more about For Each loop in the Using SSIS ForEach Loop containers to process files in Date Order and SSIS Foreach Loop vs For Loop Container:

Configure a Foreach Loop Container

Drag the Data import from multiple excel files task into the Foreach Loop Container:

Drag the Data import from multiple excel files task into the foreach loop container

Double-click on Foreach Loop Container, and it opens the following loop editor. Make the following changes:

  1. In the Expression, click on eclipse and specify the variable User:: Directory. You can also evaluate an expression, and it should show the value of the variable, as shown below:

    Specify expression for SSIS variable

  2. Under the Folder, specify the path of the folder. We want data import from all files in this folder, so we have specified *.* in the files section:

    Specify file path and name

Now, click on Variable Mappings and map the User:: ExcelPath variable as shown below. It automatically takes index value zero:

Variable mapping

Click OK, and it completes the configuration of SSIS package for importing multiple files:

Complete package configuration

Before executing the package, let’s verify that our SQL table does not have any data:

No data in SQL table

Execute the SSIS package using the Start button:

Execute the SSIS package

SSIS Package is successful now. You can see a green tick icon on the Foreach Loop Container and data import task:

Successful package

Let’s verify data in the SQL table. In the following screenshot, using the source table, we can verify that we have data from all Excel files. We have data from EmployeeData-1 to EmployeeData-10:

Data Validation

We should have a similar column structure in an Excel file for data import. You can configure additional tasks for error handling or capture bad data. Take reference of An overview of Error Handling in SSIS packages for it.

Conclusion

This article explores the process of importing multiple Excel files into SQL Server table. It saves manual efforts and expedites the data import process with N number of files. You can schedule a SQL agent job for SSIS package execution for frequent tasks.

Rajendra Gupta
1,643 Views