Post reply

Looping Through Excel files and worksheets

  • July 20, 2008 at 3:08 am

    #124214

    Hey guys,

    Need help with a package I'm working on. The objective is to load Excel file data into tables. Each Excel file has a total of 4 worksheets (but could be less), each with specific (fixed) sheet names.

    I found the following MSDN article on looping through Excel files and tables (sheets): http://msdn.microsoft.com/en-us/library/ms345182.aspx.

    So my setup is one Foreach Loop Container for looping through all the Excel files in the folder, and another Foreach loop (nested) to loop through each file's worksheet---just as the artilce had instructed (including the expressions used). I ran it just to test it (just with those to containers) and it ran successfully.

    I then placed a data flow task inside the inner foreach loop (the one for worksheets), and used an Excel Source connected to the same Excel Connection manager used for the foreach loop (the one for files). The Data access mode I used was Table name or view name variable. I then pointed it to the variable I created to hold my worksheet names. I didn't create any desintation object yet, since I just wanted to check if the files will be read properly.

    When I ran the package, I got the error:

    Excel Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "Program_id" needs to be added to the external metadata column collection. The column "Cell_Code" needs to be added to the external metadata column collection.

    This goes on for so many of my other columns (I have a lot).

    I saw an article that says all I got to do is double click on my data task again and I should receive a prompt asking me if I wanna refresh the metadata. I tried it and sure enough it did prompt me. I clicked on ok. But the error persists. Any other ideas on what's causing this error?

    Looking ahead, I'm also not sure how I can set up my destination task to know which worksheet it's currently getting the data from. I need to point each worksheet to it's own table in the database. Will I have to use a script task for it, and a variable to hold the table to be loaded? I probably need to use the script to set the correct table depending on which worksheet is currently connected to.

    Thanks!

    Jafar

  • July 28, 2008 at 5:16 am

    #849079

    Do all of the worksheets in all of the spreadsheets have the same column headings?

    Tom

    Life: it twists and turns like a twisty turny thing

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply