SSIS Variable Scope Issues

Question

I'm trying to iterate over a few Excel files in a document using a foreach loop. So in my Control Flow I drag and drop a For Each Loop Container. I then create a new variable called ExcelFilePath and put it under the foreach loops scope:

I then edit the foreach loop and set it's variable mapping to map to the created variable:

Great. So now at this stage I'm assuming that with every iteration this foreach loop does, it's going to store the file path (or whatever) to the current excel file in that variable.

Next I insert a Data Flow Task and add an Excel Source. Initially I create an Excel connection manager, just to point to the correct sheet in the document and specify what columns I want. I test it and see that the preview works nicely.

Nice. So next I want to change the connection manager, so that it doesn't map to the same file the whole time, but instead uses the variable that the foreach loop sets, specifying what file to use. So I right click on the Excel Connection Manager > Properties, and add an expression. I choose 'ExcelFilePath' for the 'property' and I look for the ExcelFilePath variable that I created that will be in the foreach loops scope.. But nothing. Nowhere to be found.

What is it that I'm doing wrong here? I've followed various tutorials on the subject to the letter, yet on my side the foreach variable just is nowhere to be found??

EDIT

I then went ahead and changed the scope of the variable from the foreach loops scope to 'package' scope. And hey I can now see it, perfect. I select it and this happens:

EDIT 2

Finally got that working, yet now I have yet another error:

I honestly don't know how anyone can get any work done using this. I take my hat off to you. I mean Error Code 0x80004005, what's that supposed to mean?!

Answer 1

I think, we can not use variable for excel connection which will point data file on runtime changing because there are so many problems in Excel and SSIS.

So, you need to some thing like below.

Add one scrip task in for each loop container before data flow task. Also create one template file with static location with same header which has file.

Now while iterating file by for each loop. The file we are processing for particular iteration need to replace template using script task.

Excel connection manager always point template file.

  what ever file name you getting by for each loop need to replace template