SSIS send mail question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » SSIS send mail question

SSIS send mail question

Rate Topic

Display Mode

Topic Options

Author

Message

keithcorkran

Posted Friday, October 05, 2012 7:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:11 PM
Points: 1, Visits: 50

I am trying to create a package that reads email data from a tables based on if an email has already been sent.

The problem I'm having is updating the record after email acknowledgement from the SMTP server.

First Issue: I have no idea how to capture that negotiation.

Second Issue: Updating the table based on ID as to whether email sent
UPDATE tblNotification SET datSent =getdate()
WHERE ID = @P2
---where @P2 is a parameter that comes down and is loaded into the ForEach container (ForEach ADO Enumerator)

I'm having trouble finding somewhere a similar solution has been posted.

Any ideas welcomed.
Thanks,
Keith

Post #1369041

WillyWonka

Posted Wednesday, October 31, 2012 11:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 4:37 PM
Points: 49, Visits: 106

I have done something similar to this. Here's how I did.

1.) For each loop through your table sending you e-mails. Storing the EmailID you are working with in a user variable (i.e. User::EmailID).
2.) Execute your Send Mail Task
3.) Execute SQL Task

In your Execute SQL Task set the SQLStatment via the expressions tab.

Your expression would look something like this. Note I put single quotes around the variable and the date.

"UPDATE dbo.table SET DatSent = '" + CSTR(GETDATE()) + "' WHERE EmailID = '" + User::EmailID + "'"

There multiple ways to do this I just found this to be easier.

Hope this helps,

Eric

Post #1379451

« Prev Topic | Next Topic »

Permissions