CODEWITHSUNDEEP
sql-servervariablesssisetlexecute-sql-task
Errol Paleracio
Errol Paleracio

Reputation: 634

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

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.

Upvotes: 3

Views: 775

Answers (1)

Hadi
Hadi

Reputation: 37368

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

Upvotes: 2

Related Questions