How to make variable update for each instance of for-each loop?

Question

I have a for-each loop that goes through a file directory. These files are transaction information from different clients. When there is an error with a file (i.e. can't be processed), the Event Handler is to send an email to the client notifying them. There is a SQL task that uses an existing variable (Client ID from the file) that queries a table to get the Client's email address. I created a variable for the Client Email. I want to make this update each time the for-each loop is run, since each file will have a different value for the client email variable.

How can I ensure this?

Inside the for-each loop, there are three data flow tasks that could conceivably "error" and that's when I will be sending these notification emails. Will I need to scope a variable for each of the three executables within the loop for the Client Email? Or can I scope a single variable in the for-each loop?

I understand this may be a bit confusing so please let me know if you have any questions.

Event Handler Flow

For-Each loop with data flow task

Answer 1

You can create the variable either at the foreach loop container or at the global scope, by making sure that the foreach container is selected when you create the variable, or nothing is selected when you create the variable.

Inside the foreach loop container you can then add an Expression Task in order to change the value of the variable at run time.

Depending on what you need exactly, you could also use a Script Task for this purpose.

There's a similar question here with the relevant details:

https://stackoverflow.com/questions/4052864/ssis-set-variable-at-runtime