Excel Loopeach Issue

[MrRichDean]

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.

 

[drlex]

Umm.
I see no SQL question here; how about reposting in forum707 ("VBA Visual Basic for Applications (Microsoft)")?

http://www.tek-tips.com/threadminder.cfm?pid=707

will get you there.


soi la, soi carré

 

[MrRichDean]

The foreach loop container is a data flow within SSIS and therefore it is a SQL question.

 

[drlex]

My apologies - I overlooked such. Would the pointers contained in

http://www.sqldts.com/246.aspx

assist?

soi la, soi carré

 

[drlex]

By way of further apology, how about:

http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html

and

http://bi-polar23.blogspot.com/2007/09/loading-multiple-excel-files-with-ssis.html

?

soi la, soi carré

 

[MrRichDean]

No need to apologise DrLex!

I have tried part II of the bipolar example but I must have set up the variable incorrectly as it fails when it encounters the second file.

I think giving it a miss tonight, and try again next Monday : )