Post reply

Inclusion of task failure error log in SSIS Mail Task

  • September 25, 2008 at 12:29 am

    #154756

    Hi all, i am trying to include an error log for the failure of a task with a mail task implementedin ssis package. i.e. if any of tasks gets fail an error log willl be generated and that should be attached with the mail task to be mailed to specific person....... hope some one will be able to help me

    thanks

  • September 25, 2008 at 1:33 am

    #876812

    You could work with event handlers.

    You would have to enable package logging, and make this write to some kind of log.

    Then in the OnError event have a send mail task which attaches the log and sends it to recipients.

    Good Luck

    ~PD

  • September 25, 2008 at 11:35 am

    #877228

    If you are already creating a log file for your pacakge you can attach this file in you Send Mail task but if you just want to alert the user with errors you can use the system variable like ErrorCode, ErrorDescription in your mail msg.

    Note that the above mentioned system variables are only available for Event Handlers.

    HTH

    ~Mukti

  • September 25, 2008 at 2:31 pm

    #877348

    everyoen is right here. Mukti it is only for "OnError" event handelers we have ErrorCode and ErrorDescription sys variables ( i know you knew it, it is just for starter, if any out there) . here si sample code that i ahve in my ssis packages to send emails on Error

    I have a table of emails lists and i use a SQL task to assign a variable with the email lists. ( i use this variable on runtime to replace recepients )

    here is the code........

    DECLARE @Names varchar ( 4000 )

    SET @Names = ''

    SELECT @Names = emailAddr + '; ' + @Names

    FROM dbo.tblEmailRecipients

    SELECT @Names = ltrim(RTRIM ( @Names ))

    SELECT @names = left(@names ,len(@names) -1 )

    SELECT @names AS Emails

    Now next SQL task will send emsils using DB mail.

    ( here even you can attach teh file with it) I cannot becoz i am using SQL auntication...and its gives error when u try to send attchements using SQL auntication....)

    HERE IS THE CODE. the code is in expression as sqlStatementSource

    "EXEC msdb.dbo.sp_send_dbmail

    @profile_name = '"+ @[User::DBProfileName] +"' , " +

    "@recipients = " + "'" + @[User::EmailLists] + "'" + ", " +

    "@subject = " + "'" + @[System::PackageName] + " @task--"+@[System::SourceName] +", FAILED!!!"+ "'" +"," +

    "@body = " + "'" + @[System::ErrorDescription] + " ' " + "," +

    "@body_format =" + "'" + "TEXT" + "'" + "," +

    "@importance =" + "'" +"NORMAL" + "'"

    here you can edit the code to add attachmenst like

    "@db_attachment = "Location of file'', In my case i have dynamic location which are on teh variables.......

Viewing 4 posts - 1 through 3 (of 3 total)

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