SSIS package configurations

Question

how to load multiple Excel files using SSIS. I have had Packages in the past where I was looping through multiple Text files in a folder and loading them into SQL server tables.

question from:https://stackoverflow.com/questions/65713879/ssis-package-configurations

Answer 1

Create a variable named FileName, set the scope to ImportMultipleExcelFiles, Data type is String.

Add a Foreach Loop Container in the Control Flow Task.

Edit the Foreach Loop Container, in the Collection section change the Enumerator value to Foreach File Enumerator

You need to change the Enumerator configuration as shown below :

• Folder: Provide a complete folder path location where all our Excel source files are stored.
• Files: you need to read the Excel files from our source folder, so enter *.xls in the Files section, this will make sure our SSIS package will read all available .xls files from the source folder. Here * indicates that the Excel file name can be anything, but file extension will be .xls. If we need to read data from a specific Excel file name then we have to configure it accordingly.
• Retrieve File Name: select the Fully Qualified radio button.

Then, create variable mappings for the Foreach Loop container, select the "User::FileName" variable and set the Index value to 0 in the Variable Mappings section.

Add a Data Flow Task inside the Foreach Loop Container. Right click on the recently added Data Flow task and click on Properties and mark the DelayValidation property to True.

Add an Excel Source in the Data Flow Task and create a new connection to any of the Excel source files.

You have to make the Excel connection dynamic so that it can connect to each Excel file in the source folder. To make the Excel source connection dynamic, right click on Excel Source Connection and then click on Properties.

Expand the Expression Properties, then select the Connection String property and then click on the expression icon, in the expression window :

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+@[User::FileName]+";Extended Properties="Excel 8.0;HDR=YES";"

Finally, execute the SSIS package and see the result.