Import data from multiple Excel files using SSIS

2
5805

SSIS Tutorials – In this interesting post, we will learn how to import data from multiple Excel files to a single destination say, SQL Server using SSIS. This question is asked in many MSBI interviews and also by many followers, so the wait is over. Let me explain the scenario to understand this problem. Suppose, there is one folder and on a daily basis data is kept in separate Excel files in this folder. Now, you are asked to extract the data from this folder containing multiple Excel files to the SQL Server. In simple words, we have to extract data from a folder containing new Excel file being added each day.

NOTE :-  An important point to ponder here is that the column structure (format) of all the Excel files remains the same.

There can be numerous solutions to solve this problem of “Import data from multiple Excel files using SSIS”. We will use ForEach loop container approach to implement this scenario. In the below article, we will be solving this practically by taking an example.

Import data from multiple Excel files

STEP 1 :-  Folder with multiple Excel files

  • Create a folder and place multiple Excel files in this folder. But, make sure that all Excel files have same columns format (identical schema).

NOTE :-  Sheet name should be uniform across all the Excel files present in the folder.

Folder containing multiple Excel files (same Schema) - One for each day
Folder containing multiple Excel files (same Schema) – One for each day

STEP 2 :-  SSIS Package

  • Create one SSIS package and give it a desired name, say “Import data from multiple Excel files”.
Open SQL Server Data Tool (SSDT) and Create new SSIS project
Open SQL Server Data Tool (SSDT) and Create new SSIS project

STEP 3 :-  Add SSIS Variable

  • Right click in the control flow pane and choose Variables.
  • Add a variable at package level with name as “FileName” and Data type as “String”.
Add SSIS variable
Add SSIS variable

STEP 4 :-  SSIS ForEach loop container

  • In the Control Flow pane, drag and drop ForEach loop container from the toolbox to the surface.
SSIS ForEach loop container
SSIS ForEach loop container
  • Double click on the ForEach loop component to edit it or Right click on the Foreach loop container and select “Edit” option.
  • Click on “Collection” tab to specify Folder path, Files type and option to retrieve file name.
  • Now, provide the folder path where you have created it to keep the multiple Excel files. File type will be “.xls” as all files are Excel files. You can take a reference from the below screenshot.

NOTE :-  [*.xls] means all file names with an xls extension. As, you have multiple excel files present in a folder so* will fetch all Excel files present in the folder specified above in the Folder path.

Configure SSIS Foreach loop container
Configure SSIS Foreach loop container
  • Go to Variable Mapping tab and map the created SSIS variable in Step 3.
  • Finally, click OK button to finish with ForEach loop configuration.
ForEach loop container's - Variable Mapping tab
ForEach loop container’s – Variable Mapping tab

STEP 5 :-  SSIS Data Flow Task

  • Inside ForEach loop container, drag and drop Data Flow Task (DFT). You can give DFT a desired name, say “Import data from multiple Excel files”.
Adding Data Flow Task into ForEach loop container
Adding Data Flow Task into ForEach loop container
  • Double click on Data Flow Task to configure it with Source, Transformations and Destination.
  • In Data Flow pane, drag and drop Excel source from the toolbox.

STEP 5(A) :-  Excel source configuration in SSIS

Double click on the Excel source to get the Excel source window editor.

Click on New button and then browse to the location of first Excel file (i.e. “C:\PhpRing\Day_1.xlsx”).

Excel Source configuration in SSIS
Excel Source configuration in SSIS

Now, choose “Sheet1$” from the drop down list for “Name of the Excel sheet”. This finishes the configuration of Excel source in SSIS.

Excel source editor - Connection Manager tab
Excel source editor – Connection Manager tab
  • Columns tab – Choose the columns from the Available source.
Excel source editor - Columns Mapping tab
Excel source editor – Columns Mapping tab

STEP 5(B) :- SSIS OLEDB destination

  • Drag and drop OLEDB destination component to the Data flow pane.
  • Now, connect Excel source to OLEDB destination.
Connect Excel source to OLE DB destination
Connect Excel source to OLE DB destination
  • Double click on OLEDB destination to configure. Now, map it to your database and create a new table or use existing table. You can take a reference from the below screenshot attached.
OLEDB destination editor - Connection Manager tab
OLEDB destination editor – Connection Manager tab
  • Mapping tab – Map Input columns to Destination columns.
OLEDB destination editor - Column Mappings tab
OLEDB destination editor – Column Mappings tab

STEP 6 :-  Update SSIS Excel source configuration

NOTE :-  This is the main step here  to import data from multiple Excel files. So, put your concentration on this point to understand the tricky part.

  • Right click on “Excel connection manager” (you can find Excel connection manager in the “Connection Managers pane”) and select “Properties”. Other ways to go to properties are – Select the component and then press “f4” or press “Alt and Enter”, etc.
  • In the properties pane for Excel connection manager, find Expressions and expand it. This will pop up Property Expressions editor.
  • Now, Choose “ExcelFilePath” property and assign the value for expression as @[User::FileName]. This will provide dynamic connection for each Excel file with the help of variable (User::FileName) used in ForEach loop container.
  • Click on “Evaluate expression” button at the bottom left to check for errors. Follow the below image for a clear understanding of these steps.
Expression for SSIS Excel Connection Manager
Expression for SSIS Excel Connection Manager
  • Once you set the Expression for Excel connection manager, you will get a red cross sign on Excel source component. The reason for this error is that the source component is not aware about the connection because it will dynamically accept the connection (User::FileName) when package will get executed.
Setting Excel file path Expression for SSIS Excel Connection Manager
Setting Excel file path Expression for SSIS Excel Connection Manager

STEP 7 :-  Set Data Flow Task – Delay validation property = TRUE

  • Lastly, a very crucial point to import data from multiple Excel files using SSIS is – to set “Delay validation” property of the Data Flow Task = “TRUE”.
Setting Delay validation = TRUE for Data flow task in SSIS
Setting Delay validation = TRUE for Data flow task in SSIS

STEP 7 :-  Execute SSIS package.

  • Finally, let’s Execute our SSIS package.
Execute SSIS package - Import data from multiple Excel files
Execute SSIS package – Import data from multiple Excel files
  • Data in SQL Server before executing SSIS package.
Data in SQL Server table before executing SSIS package
Data in SQL Server table before executing SSIS package
  • “Import data from multiple Excel file” SSIS package execution – Control Flow and Data Flow pane.
SSIS package execution - Control flow and Data flow pane
SSIS package execution – Control flow and Data flow pane
  • You can now view your imported data from multiple Excel files into SQL Server table.
Final SQL Server table after SSIS package execution - Import data from multiple Excel files
Final SQL Server table after SSIS package execution – Import data from multiple Excel files

I hope you must have enjoyed our post on “How to import data from multiple Excel files to SQL Server using SSIS”. This is a very important question asked during MSBI interviews. With this, we reached to an end of this post. I understand sometimes it gets difficult working with Excel source. So, try these steps very carefully and still if you have any queries or issues then do comment below. We will really appreciate your feedback.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 8.9/10 (15 votes cast)
VN:F [1.9.22_1171]
Rating: +8 (from 8 votes)
Import data from multiple Excel files using SSIS, 8.9 out of 10 based on 15 ratings
  • shiva

    Thank you for ur valuable example

  • Sri

    Explained clearly in this article. Same way can you please post all concepts of SSIS, SSAS & SSRS in this site.