For Each Loop [Exec SQL Task, Send Mail]

Question

Post reply

For Each Loop [Exec SQL Task, Send Mail]

olafdedig

Right there with Babe

Points: 768
More actions
September 1, 2008 at 9:23 am

#154679

Hello
I have a SQL script, A Send Mail in a For Each Loop
now this is not working so i wanted to try the folowing approch any thoughts would be great:
the SQL script selects top 1 address from table where flag = 1, once email address is selected change the flag to 0 that variable mapped to SSIS package variable then passed to send mail task, however when I put this into a for each loop, the process is repeated only 25 times and there are many more mail addressthen the for each loop end and the package continues, any idea why that might happen?
or is there a better way to get this done ?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Yes... there are a thousand reasons why this may happen...

1. Code to set the flag to 1 didn't work correctly.

2. Code to reset the flag to 0 didn't work correctly.

3. Code that counts the flags for the while loop didn't work correctly.

4. The loop code didn't work correctly.

5. The email code didn't work correctly.

6. The server has a major fault and didn't work correctly.

7. SQL Server has a bug in it and didn't work correctly.

8. Code to verify the actual count compared to the loop didn't work correctly.

9. Etc, etc, etc.

Without additional detail, it's impossible for any of us to tell, though... please see the link in my signature for a better answer more quickly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

olafdedig Right there with Babe Points: 768 More actions · Answer 2

Hi Jeff,

you are right, sorry for the very limited details. I have made the following changes to the process, and made the following progress

Script number 1 executes and Selects the 2 Variables the to mail and the body of the mail (checked script works fine) the two variables are then "sent" to a Script Task that does a send mail using script (for html mail) the mail is sent fine (check this is working) then last step of the for each loop runs another sql script that then changes the flag for the variables just created so they are not created again (check the script this works fine)

so the issue is now the three steps work fine the mails are sent and end up in the mail box but the for each loop does not stop when there are no more mails to be sent, i.e. when the variables created by the first script are null.

The process errors out when the first script gets null values as the script cannot accept a null value.

how do I set the fore each loop to stop running when these variables becomes null / (can use a isnull to set to a certain value if that helps)

Hope this is a better formed question,

Again, thanks for any help

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Ummm... I'm not very smart about SSIS because I don't use it... I always use something else like BCP, etc.

That, not withstanding, I follow the logic of the flow you've laid out and I have to ask why the variables are becoming "NULL"... they should become "0" if you're counting down of looping until there is no rowcount.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

olafdedig Right there with Babe Points: 768 More actions · Answer 4

True, I see what you mean - I get a result the resutl is null but I assume that the foere each loop still sees it as a row, will try to adapt the script to return no rows when result get to nulls

olafdedig Right there with Babe Points: 768 More actions · Answer 5

SOLUTION:

Hello, Here is the solution I found to my problem:

For doing a task like sending html mail (built using a SQL script and a Script Task) the for each loop does not make things easy. To get this working use the For Loop Container: The for loop container allows you to set exactly how many times to loop the sequence of tasks contain inside that for loop I did the following

make a simple t-sql script to count the number of rows (i.e. count the number of email to be sent/times to loop) assign/map the result of that script to a user variable called iEndCount, also created a user variable called icount.

Then in the For Loop properties set the following as the expressions

for InitEpression @iCount=0

for evalExpression @iCount<@iEndCount

for AssignExpression @iCount=@iCount+ 1

So the process now works as follows a t-sql script counts the number of mail that need to be sent, passes that to the For Loop Container, the For loop container checks that variable icount < than iendcount it it is starts the process, at then end of a complete adds 1 to the icount then starts again.

This might not be the most elegant method, but did the trick for me

Additional Resources:

Many Thanks for the help though 🙂

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 6

Many ways to skin a cat, whichever works best for a particular situation...

Assuming that you were using a For Each ADO enumerator, I dont see any reason why the loop did not stop when it was supposed to. The ADO enumerator loops on an Object variable, which must be populated prior to executing the loop.

~PD

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

olafdedig (9/3/2008)
SOLUTION:
Hello, Here is the solution I found to my problem:
For doing a task like sending html mail (built using a SQL script and a Script Task) the for each loop does not make things easy. To get this working use the For Loop Container: The for loop container allows you to set exactly how many times to loop the sequence of tasks contain inside that for loop I did the following
make a simple t-sql script to count the number of rows (i.e. count the number of email to be sent/times to loop) assign/map the result of that script to a user variable called iEndCount, also created a user variable called icount.
Then in the For Loop properties set the following as the expressions
for InitEpression @iCount=0
for evalExpression @iCount<@iEndCount
for AssignExpression @iCount=@iCount+ 1
So the process now works as follows a t-sql script counts the number of mail that need to be sent, passes that to the For Loop Container, the For loop container checks that variable icount < than iendcount it it is starts the process, at then end of a complete adds 1 to the icount then starts again.
This might not be the most elegant method, but did the trick for me
Additional Resources:
Many Thanks for the help though 🙂

Very cool... thanks for telling us what you did. It'll help others who have the same problem. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

olafdedig Right there with Babe Points: 768 More actions · Answer 8

hi there,

in the beginning no i was not using a ADO enumerator, I was using a variable - that might have been another way to do it I think. - thanks, will check that out have not used a ADO enumerator before

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 9

For each ADO enumerators are actually really easy.

- Create an object variable, and add a prepare SQL task to your control flow.

- Write the SQL statement, and change to full result set. On the result set section, bind your object variable. Remember for full result sets, the result name must be left as zero.

- Add a for each enumerator, and change the enumerator type to for each ado - this will provide you with a single option being the object variable that you just created.

tada...

Good luck man!

~PD

olafdedig Right there with Babe Points: 768 More actions · Answer 10

Excellent, Thanks I will try that out this weekend , let you all know thansk again