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.

Hadi · Accepted Answer · 2019-08-06 07:28:41Z

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 6, 2019 at 7:28

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Errol Paleracio Over a year ago

I used single. Is it still the same?

Hadi Over a year ago

@ErrolPaleracio yes it is the same concept. But Make sure first that there is no problems with the Execute SQL Task configuration.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related