Post reply

Need to send variable in Email

  • 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..

  • February 13, 2008 at 8:22 am

    #777860

    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

  • February 13, 2008 at 8:29 am

    #777869

    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.

  • February 13, 2008 at 8:43 am

    #777884

    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

  • February 13, 2008 at 8:48 am

    #777886

    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

  • February 13, 2008 at 8:52 am

    #777891

    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

  • February 13, 2008 at 3:38 pm

    #778132

    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

  • February 14, 2008 at 2:50 pm

    #778603

    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!!

  • February 15, 2008 at 8:48 am

    #778874

    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

    OUTPUT deleted.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

  • February 15, 2008 at 3:36 pm

    #779031

    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..

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

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