Blog Post

Loop Through Excel Files in SSIS


You can loop through excel files using SSIS. This will use the For Each Loop container and a data flow task.


First Create a variable named strExcelfile as a string variable; you can leave the value blank.


Next, drag in a For Each Loop. Set it to For Each File, and point it to the folder where the excel files exist and type .xls or .xlsx for the file type. In this example the excel files are in c:\test\excelfiles. There are three files named USCustomers1.xls, USCustomers2.xls, and USCustomers3.xls.


Next, drag in a data flow task and drop it in the loop container. Open the Data flow and drag in an Excel Source. Set the Source to one of the excel files in the folder above. This will set the column names and the metadata for the files. Each file in the loop must have the same lay out. If they have different column widths are data types then you cannot use this technique.

Now you will map the excel file to the connection. Click on the Excel connection in the connection manager. This was created when you created the Excel source in the Data Flow. Click on Expressions in the properties windows and open the expressions editor for the Excel connection manager. Select the File Path property and drag in the strExcelFile variable.


One Last step is to set the data flow to delay validation. This is so the data flow will not check for the excel file until after the file name is loaded into the variable.



You rated this post out of 5. Change rating




You rated this post out of 5. Change rating