How to Import Data from Multiple Excel files to SQL using SSIS & Visual Studio

I was asked to import data to SQL from multiple (15) Excel files, and each Excel file contained historical data with more than 200,000 rows.

I could do it with 15 data flows and Excel connections in SSIS, but I didn’t want to do it that way because I wanted to find a more efficient way than creating 15 data flows and Excel connections. I’m too lazy to create all those data flows and Excel connections! That would take a lot of time.  So I Googled it.

I found many articles, but each article was slightly different and used a different approach to handle the situation.

I followed steps in several articles and Youtube videos, but  I had no luck. Every time something went wrong or I was missing something. I scratched my head.

After a while, I found a way to make it work and I’d like to share it with everyone to save your time.

Here are my step by step instructions to create a SSIS package for importing data from multiple Excel files to SQL with only one data flow and one Excel connection.

For example, I have two Excel files at C:\BI\SSIS\Import Hist Data\Source Files\Test

image1

Very Important Note

Ensure that each Excel file has the same schema.

For example,

  1. Number of columns
  2. Name of column headings
  3. Name of tabs
image2

How to Import Multiple Excel files to SQL

  1. Open Visual Studio
  2. Create a new project
image3

 

  1. Select Integration Services Project
image4

 

  1. Create a Variable
  • Name: FilePath
  • Data type: String
  • Value: blank
image5

 

  1. Drag and drop Foreach Loop Container to the Control Flow
image6

 

  1. Right click on the Foreach Loop Container then click Edit
image7

 

  1. Go to Collection > Enter the folder path > Enter the file extension

Make sure that Enumerator is “Foreach File Enumerator” and Retrieve file name is set to “Fully qualified”

image8

 

  1. Go to Variable Mappings > Select the User:FilePath variable > Click OK to close the editor
image9 image10

 

 

  1. Drag and drop the Data Flow Task in the container
image11

 

  1. Double click the Data Flow Task
  1. Drag and drop Excel Source in the data flow
image12

 

  1. Right click > Edit
image13

 

  1. Click New > Enter one of your Excel file path > click OK
image14

 

  1. Select Name of the Excel sheet and click OK
image15

 

  1. Drag and drop the OLE DB Destination > Enter your SQL server information and select the target table
image16

 

  1. Very important! Select the Excel Connection Manager > Click expand button for Expression
image17

 

  1. Click “…”
image18

 

  1. Select ExcelFilePath then Click “…” for Expression from its property
image19

 

  1. Drag and drop the “User::FilePath” Variable then click OK
image20

 

  1. Click OK. You will see an error markwe on the Excel Source. Don’t worry.
image21 image22

 

  1. Change DelayValidation option to True for Data Flow Task property
image23

 

  1. Ignore the error mark from Data Flow Task then click Start
image24 image25

 

That’s it. I hope that I saved you some time importing your multiple Excel files into SQL!

2017 Magic Quadrant for BI

Find out if the tool you are considering is a leader!

Read The Magic Quadrant Report

2017 Magic Quadrant for BI

Read The Report