How to save error Message in Database through SSIS

Question

Post reply

How to save error Message in Database through SSIS

M_GREAT_4_SQL

Right there with Babe

Points: 752
More actions
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,

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

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

gtschech Ten Centuries Points: 1341 More actions · Answer 1

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

M_GREAT_4_SQL Right there with Babe Points: 752 More actions · Answer 2

M_GREAT_4_SQL

Right there with Babe

Points: 752

January 12, 2010 at 1:19 am

#1102212

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

Thanks,

starterm Mr or Mrs. 500 Points: 518 More actions · Answer 3

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

gtschech Ten Centuries Points: 1341 More actions · Answer 4

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://qa.sqlservercentral.com/blogs/michael_coles/archive/2007/10/09/3012.aspx).

WM_HOPETHATHELPS

Guenter

starterm Mr or Mrs. 500 Points: 518 More actions · Answer 5

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.