Post reply

How to save error Message in Database through SSIS

  • January 10, 2010 at 5:22 am

    #140553

    There is a requirement to capture error Discription and get stored in Audit Table in SQL Server. I am using SSIS 2005.

    I added one Sql Task in Event Handlers(OnError) but not able to Get the description of first error message.

    For example I have to store below mentioned error

    [Flat File Source - Load GDC Data [7132]] Error: Data conversion failed. The data conversion for column "Emp ID" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

    but I am getting below mentioned error which is the last error mesage in Execution Results tab...

    [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

    I have to store descriptive message.

    Please let me know if there is any query.

    Thanks,

  • January 12, 2010 at 12:29 am

    #1102201

    Hi,

    I would suggest to use the SSIS logging features. You can enable SSIS to log any error messages to a database table inside your database. If you keep the package ID in a separate table as a reference, you can JOIN the two aand get all error messages afterwards.

    For details how to write error information to a table see

    http://msdn.microsoft.com/en-us/library/ms138020(SQL.90).aspx

    HTH

    Guenter from Frankfurt/Germany

  • January 12, 2010 at 1:19 am

    #1102212

    Thanks Dear, but is there any other way through that we can get error message.

    Thanks,

  • February 24, 2010 at 5:29 am

    #1124208

    Can u please explain the way how to get error message in sqlserver 2005 from alerts when a job which has jobstep to run maintenance plan fails. Maintenance plan contains database backup task which is being failed because some databases are offline and restoring mode. How to specify severity number when backup task fails for any reason

  • February 24, 2010 at 5:52 am

    #1124218

    Hi,

    unfortunately, I am not really an expert when it comes to SQL jobs. I prefer using native SSIS and develop it using BI Developer Studio.

    So my direction will only work when you have access to the SSIS package that runs when the SQL job starts, and when you have BI Developer Studio installed.

    Having said this, it is quite easy to log events in a package:

    1) open the package in BIDS

    2) create a new Connection to your database where the result shall be stored (you can also reuse an existing connection if available)

    3) Open the menu "SSIS" -> "Logging"

    4) Select "SSIS log provider for SQL Server" in the "Provider type" box and click "Add..."

    5) select your connection in the "Configuration" column

    6) make sure the Check Mark next to the root entry of the "Containers" tree is checked

    7) go to the "Details" tab and select all events you'd like to see in the database

    Now you can run your package. By default, it will create a table named "sysdtslog90", where all the logging goes.

    You can override this, but that's another topic (one I found recently is http://www.sqlservercentral.com/blogs/michael_coles/archive/2007/10/09/3012.aspx).

    WM_HOPETHATHELPS

    Guenter

  • February 24, 2010 at 6:36 am

    #1124235

    Thanks for your reply. But the requirement is like the following, in sqlserver management studio, i have created a maintenance plan which has database backup task which takes backup of all databases and one of the database backup failed. When the job of corresponding maintenance plan runs. Error is displayed in maintenance plan history. I want to capture that information in the alert of the job. So i should specify the error number or severity level to sent the mail regarding the job failure. But i dont know which error number or severity level i should specify in the alert.

  • February 25, 2010 at 4:33 am

    #1124836

    M_GREAT_4_SQL (1/12/2010)

    Thanks Dear, but is there any other way through that we can get error message.

    Thanks,

    I have been using a technique loosely based on this article for several years now and it works very well.

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • February 25, 2010 at 9:48 am

    #1125076

    Thats a very nice article.. thanks a lot willem..

Viewing 8 posts - 1 through 7 (of 7 total)

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