I am verifying set of values from two tables and intend to send a mail to users if there is a mismatch in the records, You mentioned Users, do you have a EMAIL field in the reocrds? and if so will each record gets emailed to that specific Users? Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Do you wish to send one mail for the entire table? If thats the case you could do the check in a stored procedure or join and get the number of mismatch records. Save it to a variable and use this variable in precedence constraint before sending the mail. In the precedence constraint you couls use the expressions @[User::Count] >0

I am verifying set of values from two tables and intend to send a mail to users if there is a mismatch in the records, You mentioned Users, do you have a EMAIL filed in the reocrds? and if so will each record gets emailed to that specific Users?Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Hello everyone, I am verifying set of values from two tables and intend to send a mail to users if there is a mismatch in the records, Any suggestions appreciated, Thanks

Please check here to seding email from SSIS package http://www.mssqltips.com/tip.asp?tip=1731 Cheers

Use variables whenever possible. Especially for the To: and Subject: lines. You can link your variables via the SSIS Configuration Manager to a table that stores the variable values. This way you won't have to go into the package after it's in Production to change to who the emails get sent, and you can make the Subject generate different values based on a function that says success, error, datetime, etc. As far as identifying the errors: In the Dataflow if you use a Conditional Split or Lookup No Match (for example) to identify mismatched records, you will route that through a RowCount task and store the number in a variable. In the Send Mail task you can then link to the RowCount variable and say "varRowCount > 0" as the condition and if True it will send the email. In the Data Flow, you'll Multicast the mismatched records to a Flat File and drop that in a directory so the email can then link to that File as an attachment when it goes out. Good luck.Brian

simplest wasy is to use execute sql task and call sp_send_dbmail If exists(select top 1 * from dbname..table1 a, dbname..table2 b where a.col1 <> b.col1) Declare @sub varchar(100) Declare @qry varchar(1000) Declare @msg varchar(250) Select @sub = 'subject ' + Convert(Varchar(10), GetDate(), 101) Select @msg = 'message' Select @qry = 'select top 1 * from dbname..table1 a, dbname..table2 b where a.col1 <> b.col1' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'sqlprofile', @recipients = 'me@mymail.com', @body = @msg, @subject = @sub , @query = @qry ;http://sqlworkday.blogspot.com/

Thanks Brian Thank you Yogesh, I implemented the logic, thank you all for your support.S.Shekhar

Please mark the post that answered your question and vote for the post so others can get to the answer easily.http://sqlworkday.blogspot.com/