Execute SQL Task - Pass Parameter in for each loop

I am using a Exectute sql task to collect an email from a table and pass it to a parameter inside a for each loop. I get an error An error occurred while assigning a value to variable "LeadEmail": "Single Row result set is specified, but no rows were returned.". Here is part of my select statement that uses sql to collect one or more email addresses and uses it as a string that i have mapped to a variable called "LeadEmail" in my package. ---------- SELECT ISNULL(CONVERT(VARCHAR(4000),STUFF((SELECT ';'+ dtl.EM_ADDRESS FROM DEV_USR_KENW.dbo.T_OS_WEEKLY_RPT_DIST_DTL AS dtl WHERE dtl.EM_TYP = 'TO' AND dtl.CNTRC_ID = HDR.CNTRC_ID FOR XML PATH('')),1,1,'')),hdr.CNTC_EMAIL) as [TO_EMAIL], then in my precedence constraint I am using expressions. I have a flag for sending emails. If the email flag is 1 then it's a valid customer and 0 is ignore. If we ignore then successfully sends the control flow (email does not get sent) and then when the email flag is set to 1 then it is supposed to send an email and attachment to the list of recipients..... does any one have an idea why I'm getting this error message? It works fine but it shows failures and I need to reflect a clean process

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ commented · Mar 09, 2011 at 05:59 PM

It sounds like at least one of the rows in your result set is not getting a list of email addresses. If you just run the query in its entirety outside of the SSIS package, does every row in the result set get a list of emails in the [TO_EMAIL] column?

2 ·

question