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.
STEP 2 :- SSIS Package
- Create one SSIS package and give it a desired name, say “Import data from multiple Excel files”.
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”.
STEP 4 :- SSIS ForEach loop container
- In the Control Flow pane, drag and drop ForEach loop container from the toolbox to the surface.
- 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.
- Go to Variable Mapping tab and map the created SSIS variable in Step 3.
- Finally, click OK button to finish with ForEach loop configuration.
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”.
- 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”).
Now, choose “Sheet1$” from the drop down list for “Name of the Excel sheet”. This finishes the configuration of Excel source in SSIS.
- Columns tab – Choose the columns from the Available source.
STEP 5(B) :- SSIS OLEDB destination
- Drag and drop OLEDB destination component to the Data flow pane.
- Now, connect Excel source to OLEDB 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.
- Mapping tab – Map Input columns to Destination columns.
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.
- 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.
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”.
STEP 7 :- Execute SSIS package.
- Finally, let’s Execute our SSIS package.
- Data in SQL Server before executing SSIS package.
- “Import data from multiple Excel file” SSIS package execution – Control Flow and Data Flow pane.
- You can now view your imported data from multiple Excel files into SQL Server table.
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.