Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

clip_image002

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

clip_image004

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.

clip_image006

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.

clip_image008

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.

clip_image010


Comments

Leave a comment on the original post [mikedavissql.com, opens in a new window]

Loading comments...