Need to send variable in Email

Question

Need to send variable in Email

nabaj

SSC-Addicted

Points: 410
More actions
February 13, 2008 at 8:09 am

#154126

Here is my problem
I have a table where the date is entered manually by an operator and at times they enter incorrect dates in the database.
I need to search for all the dates that are entered incorrectly and update the rows with the correct date. that is not an issue. I have an if (select.... conditions)>0 , update...
the problem is that they want me to send out an email with the number of rows that were updated along with the row ID. I have no idea how i can do that. I have sent out emails before on SSIS failures and stuff but nothing with such detail.
I was thinking of not in so much detail maybe i can send out an email everytime we actually have an update.. can that be done?
Any help would be great. I am searching on my own as well..
Thanks so much..

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Nabaj,

It can be done through a trigger on the underlying table or else you can code the logic in such a way that you can keep track of the number of updates that have happened and then at the end you can send an email with the relevant information. You can have a table for storing different email addresses and after you get the information which needs to be sent call a sub procedure by passing the input values which sends out an email to the concerned parties.

Hope this helps.

Thanks

Lucky

nabaj SSC-Addicted Points: 410 More actions · Answer 2

Hey Lucky,

Thanks for the reply, i can not do a trigger on the table because there are updates going on in the table all the time. and this is a SSIS package that runs every hour and they need an hourly update.

I was wondering if this could be done:

When you run this in management studio it shows you how many rows are affected, if i could somehow take that result and put that in an email. i am not sure if that is possible or not.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

Do you need to send out a mail everytime there is a wrong entry or is it ok if its once a day. Then i suggest you to create a job with your select statement to include the number of rows, row ids and the values and mail the concerned persons. You can use Database mail to send mails to user groups.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 4

You can use an execute SQL task within a ForEach Loop sequence container to populate a couple of int32 variables representing the count and the RowID. SQLIS has a good article on how to implment variables in this task (http://www.sqlis.com/58.aspx).

You can then use an expression for the Subject in the Send Mail task i.e.

"Some warm fuzzy welcome text here:" + "" + "" + "RowID: " + (dt_wstr,9)@[varRowID] + "" + "RowCount: " + (dt_wstr,9)@[varRowCount] + "" + ""

Tommy

Follow @sqlscribe

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 5

There are a number of ways you can do this. The one I think would be best in your situation is the T-SQL OUTPUT clause new in 2k5. With this you will be able to write any columns from the records you've updated into a temp table and use this info in your email. (you can even include what the mistakes were).

If this is too much info or you're not using 2K5, then the @@rowcount variable will tell you how many rows were affected by the last DML statement. If you capture this in a variable and output it as part of your T-SQL statement, you will be able to pick this up in the SSIS execute SQL task using its output parameters.

See BOL for further info.

Hope this points you in the right direction.

Kindest Regards,

Frank Bazan

nabaj SSC-Addicted Points: 410 More actions · Answer 6

Thanks for all the replies. i will further look into output. I was also thinking of doing another job that can run right before the update and give me the ID for all rows that are matching my where clause criteria. Now im researching if i can somehow save the resulting id numbers in a file and then send that file via email as an attachment or something...

thanks again for all the help

nabaj SSC-Addicted Points: 410 More actions · Answer 7

Sugesh,

I am trying to use your way to get this done. We have to send emails every time we run the job and there is an update. I can get the result and save it as a variable as data type system.object but i don't know how i can send that table in an email?.. or if i can write the rowid to a file and that file in the email..

this is very frustrating.. ive been trying to use the output clause as well.. and can not figure out how to use send mail task with that either..

any help would be great again!!

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 8

Hi,

I'm not sure where you got to with this, but I thought I'd give you a few pointers... If you're using the send mail task in SSIS you will find that you have an attachments property ... So its up to you what format you send the file in (Excel, flat file etc).

So within your Execute SQL task in the control flow, you put your update with an OUTPUT clause added. The OUTPUT clause would look something like this...

[font="Courier New"]

IF EXISTS... DROP TABLE blah blah

CREATE TABLE dbo.TemporaryTable (RowId INT, Text NVARCHAR(4000))

UPDATE MyTable

SET DateField = CorrectedDate

OUTPUTdeleted.MyTableId,

deleted.DateField

INTO dbo.TemporaryTable

WHERE DateField = BLAH BLAH

[/font]

Next create a dataflow with your temporary table as OLEDB source, and your chosen file format as the destination.

You now have a file you can include in your email with rowid and incorrectly input data.

Hope this helps

Kindest Regards,

Frank Bazan

nabaj SSC-Addicted Points: 410 More actions · Answer 9

Thank you so much..for all of your help. i finally got it to work. i finally got what i needed.. i am new to this so everyones input helped a lot..thanks again..