Dynamic file name for Excel Connection Manager – SSIS
I recently worked on a project that uses lot of excel files of same format. Those files are located in a local folder and my task is to upload all data into a table. It is not a one time task to complete this task using Import and Export wizard. So, I decided to design a SSIS package to handle this task for now and later.
I have a folder named ExcelFiles\ that contains my excel files. I am going to design a package with for each loop to iterate files in that directory and pass the file name to the excel connection string.
Excel file used in this example : Sample1.xlsx
Step 1: Create a package with an Excel connection and connect to the sample1 excel file.
Step 4: Add For each loop container and configure the task as given below.
Variable Mapping: assign fully qualified file name of the Foreach Loop container value to the package variable.
a. We have to set the DelayValidation property to true. This will tell the package loader not to validate the connection or file availability.
b. Set the ExcelFilePath property with package variable in the expression property.
Step 5: Add Data flow task in to the Foreach loop container
Add Excel Source task:
1. Select sheet name for excel data source
Step 7: Set the package execution mode to 32 bit.
Step 8: We have completed our package design. Now, Execute the package.