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

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.

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:

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

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:

CREATE TABLE [dbo].[SSISDataImport](

[EmpID] [int] NULL,

[EmpName] [varchar](50) NULL,

[Source] [nvarchar](50) NULL

) ON [PRIMARY]

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:

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

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:

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

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 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:

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:

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:

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:

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:

Click on Preview, and we can get a glimpse of the Excel sheet 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:

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

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

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:

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

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

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:

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]:

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:

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:

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

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

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

1	TRUNCATE TABLE [SQLShack].[dbo].[SSISDataImport];

Disable SSIS project debugging

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

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

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

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:

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

Click OK, and it shows a symbol “fx” in the 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:

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:

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:
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:

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

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

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

Execute the SSIS package using the Start button:

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

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:

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.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023