Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS send mail question Expand / Collapse
Author
Message
Posted Friday, October 5, 2012 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 12:14 PM
Points: 1, Visits: 60
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
Posted Wednesday, October 31, 2012 11:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 29, 2014 10:41 AM
Points: 53, Visits: 118
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 »

Add to briefcase

Permissions Expand / Collapse