Error handling in DTS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Error handling in DTS

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-21 : 10:22:44

Otto writes "I have a requirement to load several text files to SQL/Server (2000) tables using DTS. The tables are typed, and some fields have 'not null' constraints. At this stage there is no referential integrity (DRI or triggers). There is a controller DTS package which calls sub-packages to load the individual files.

I need to be able to process as many rows as possible without failing the step (the max error count is set to 9999), whilst capturing any and all errors and produce a detailed error report. The error report must show data from the row, and indicate the exact error.

When I run a test on an input containing known errors, DTS puts error information in the log file similar to the following:

@@LogSourceRows: C:\Documents and Settings\Otto\My Documents\Projects\Avanade\DTSTest\Exception2.txt.Source

@@LogDestRows: C:\Documents and Settings\Otto\My Documents\Projects\Avanade\DTSTest\Exception2.txt.Dest

@@ErrorRow: 7

Error during Transformation 'DTSTransformation__2' for Row number 7. Errors encountered so far in this task: 1.

Error Source: Microsoft Data Transformation Services (DTS) Data Pump

Error Description:TransformCopy 'DTSTransformation__2' conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Table1Key' (DBTYPE_STR), destination column 'DTSKey1' (DBTYPE_I4)).

Error Help File:sqldts80.hlp

Error Help Context ID:30501

@@SourceRow: Logged

@@DestRow: Not Available

I have used the example program from the MS Knowledge Base article 319985 (HOW TO: Handle Data Transformation Services Package Events in Visual C# .NET), which works fine but does not give me the level of detail on the error which I'd like. The error handler only makes the following information available:

EventSource: DTSStep_DTSDataPumpTask_3

ErrorCode: 0

Source: DTS Data Pump

Description: Error during Transformation 'DTSTransformation__2' for Row number 7. Errors encountered so far in this task: 1.

HelpFile:

HelpContext: 0

IDofInterfaceWithError:

Is there a way to obtain the specific error message (italics above) in the error handler? I've spent two days researching the subject and haven't been able to find any documentation showing either someone who'se wanted to do something similar, or actually done it."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources