SSIS Excel Foreach Loop Container - Different Worksheet Names

Question

SSIS Excel Foreach Loop Container - Different Worksheet Names

ricardodeano

SSC Veteran

Points: 275
More actions
September 24, 2009 at 3:12 pm

#211407

Hello all - newbie here so be gentle!
Ok, here is the situation. I have a number of files with weekly sales information:
Sales1.xls
Sales2.xls
Sales3.xls
Each of these have identical data sets and I have succeeded in creating a foreach loop container..lovely stuff!
However, each of these weekly reports have slightly different sheet names:
Sales1.xls >>> Wk1
Sales2.xls >>> Wk2
Sales3.xls >>> Wk3
Now this poses a problem with the forloop each container.
I attempted to created a variable based on the worksheet name and change the excel file so that it looks at the table with varible and selected that worksheet variable.
However, when I attempt to run this, it runs through the first file, executes that data flow but then fails when it encounters the second file.
Has anyone got any ideas how I can sort this and/or a step by step procedure to complete this action?
Apologies for being thick and any help gratefully received.

Viewing 6 posts - 1 through 5 (of 5 total)

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

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 1

I used the method in http://msdn.microsoft.com/en-us/library/ms345182.aspx

go down about halfway to the section titled: "To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator"

SQLFanatic SSCommitted Points: 1640 More actions · Answer 2

Those instructions are for looping within a single excel file and then importing data from the tables with different name in that Excel file.

Would that apply when we need to import data from multiple Excel files with same definitions but different table names?

So, do we need to use two For Each Loop Container components for this purpose. One for Files and one for tables within those files.

or just make a different dataflow task for each individual excel file.

Maybe Ricardo can help here by telling how he did it Or Anybody is welcome to put their views forward.

Thanks.

________________________________________________________________
"The greatest ignorance is being proud of your learning"

P Jones SSChampion Points: 12353 More actions · Answer 3

I would use the SQL Command from Variable option on the Excel data source - the standard query is Select ... From ['Sheet1$'] so you could use a script component as the first item in the for each loop to set your variable up with the required select statement depending on the file name in the loop variable and then follow the script with a data flow to use the variable as the source sql command.

SQLFanatic SSCommitted Points: 1640 More actions · Answer 4

Hi P. Jones,

I had three large Excel files to import. All Excel files had only one table but different names. For Each Loop Container was failing. So, I used UNION ALL task after setting up Excel Source task for each file and it worked just fine.

I got all the data into sql table but it was not in order. Like few hundred records from first file then few hundred from second and then third and so on so forth. I also used a derived column task with a variable as expression which could be the reason behind disorder but it didn't matter as long as I had the complete data. 🙂

Thanks.

________________________________________________________________
"The greatest ignorance is being proud of your learning"

eddyjawed SSC Rookie Points: 41 More actions · Answer 5

Have you tried getting SSIS to create a new generic spreadsheet in between your data flow process. For example get SSIS to create Generic.xls from your first spreadsheet, with a worksheet named 'Generic' too > then import Generic.xls into your sql table (assuming that's where all your data is going?