sql server - SSIS Variable Scope Issues -

i'm trying iterate on few excel files in document using foreach loop. in control flow drag , drop each loop container. create new variable called excelfilepath , put under foreach loops scope:

i edit foreach loop , set it's variable mapping map created variable:

great. @ stage i'm assuming every iteration foreach loop does, it's going store file path (or whatever) current excel file in variable.

next insert data flow task , add excel source. create excel connection manager, point correct sheet in document , specify columns want. test , see preview works nicely.

nice. next want change connection manager, doesn't map same file whole time, instead uses variable foreach loop sets, specifying file use. right click on excel connection manager > properties, , add expression. choose 'excelfilepath' 'property' , excelfilepath variable created in foreach loops scope.. nothing. found.

what i'm doing wrong here? i've followed various tutorials on subject letter, yet on side foreach variable found??

edit

i went ahead , changed scope of variable foreach loops scope 'package' scope. , hey can see it, perfect. select , happens:

edit 2

finally got working, yet have yet another error:

i don't know how can work done using this. take hat off you. mean error code 0x80004005, what's that supposed mean?!

i think, can not use variable excel connection point data file on runtime changing because there many problems in excel , ssis.

so, need thing below.

add 1 scrip task in each loop container before data flow task. create 1 template file static location same header has file.

now while iterating file each loop. file processing particular iteration need replace template using script task.

excel connection manager point template file.

  ever file name getting each loop need replace template