x

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

more ▼

asked Mar 09, 2011 at 11:22 AM in Default

avatar image

siera_gld
1k 82 88 93

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?

Mar 09, 2011 at 05:59 PM KenJ
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1219
x93
x7
x1

asked: Mar 09, 2011 at 11:22 AM

Seen: 2426 times

Last Updated: Mar 09, 2011 at 11:51 AM

Copyright 2017 Redgate Software. Privacy Policy