SSIS Variable Variable Issues

Question

I am trying to iterate over multiple Excel files in a document using a foreach loop. Therefore, in my control flow, I drag the container for each path. Then I create a new variable called ExcelFilePath and put it in the foreach loops area:

Then I edit the foreach loop and set its variable mapping to match the created variable:

Great. So, now at this point, I assume that with each iteration this foreach loop, it will store the file path (or something else) in the current excel file in this variable.

Then I insert the data flow task and add the Excel source. First, I create an Excel connection manager to point to the correct sheet in the document and specify which columns I want. I am testing it and see that the preview works well.

Nice. Therefore, I want to change the connection manager so that it does not appear in the same file all the time, but instead uses the variable that the foreach loop sets, indicating which file to use. So I right-click on Excel Connection Manager> Properties and add an expression. I select "ExcelFilePath" for "property" and I am looking for the ExcelFilePath variable that I created that will be in the foreach loop area. But nothing. Not found anywhere.

What am I doing wrong here? I have followed various tutorials on this subject, but on my side the foreach variable is simply not found.

EDIT

Then I went ahead and changed the scope of the variable from the foreach loops scope to the "package" scope. And hey, I see it now, great. I select it and this happens:

EDIT 2

I finally got this working, but now I have another error:

Honestly, I do not know how anyone can do any work with this. I take off my hat. I mean Error Code 0x80004005 , what does that mean ?!

Answer 1

I think we cannot use a variable for an excel connection that will indicate the data file when the runtime changes, because there are so many problems in Excel and SSIS.

So you need something like below.

Add one script task to each loop container before the data flow task. Also create one template file with a static location with the same header that has the file.

Now when repeating the file for each cycle. The file we are processing for a particular iteration should replace the template with a script task.

Excel Connection Manager always indicates the template file.

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