Why is the value of a variable null inside a foreach loop container

Question

I have a package that has a foreach file enumerator. Now I have a variable called EmailTo which is populated by an execute sql task outside the foreach container placed just before the loop container since i only need to run the execute sql task once. The variable EmailTo is being accessed by an send mail task inside the foreach container. When i inspect the breakpoint. I learned that the value of EmailTo variable is null inside the foreach container. Why is that?

Currently, I moved the execute sql task inside the foreach container to make it work because I have to present it to my boss but there is no point of moving the execute sql task inside the loop since i only have to fetch the email once.

What should I do.

score 2 · Accepted Answer · answered Aug 06 '19 at 07:28

2

When you store an Execute SQL task Full ResultSet within a variable, it is stored as a Recordset.

A Recordset can be consumed once; if it is used by a Send Mail Task, then you cannot use it again.

You can use a Script Task to copy the Recordset into a DataTable object and store it within an SSIS object variable. Then you can use it multiple times.

Additional Information

answered Aug 06 '19 at 07:28

Hadi

36,233
13
65
124

I used single. Is it still the same? – Errol Paleracio Aug 06 '19 at 07:51
@ErrolPaleracio yes it is the same concept. But Make sure first that there is no problems with the Execute SQL Task configuration. – Hadi Aug 06 '19 at 11:03

Why is the value of a variable null inside a foreach loop container

1 Answers1