How to locate error rows from DTS Exception Log

Question

How to locate error rows from DTS Exception Log

DavidNPD

Valued Member

Points: 55
More actions
February 6, 2006 at 12:52 pm

#166836

I am using an ActiveX script thru DTS in Sql2000 to transfer some data. Apparantly there is some bad data in one of the fields that I am trying to move and the transfer chokes on the bad data. I have an exception log created that tells me the error is occuring at "Error at Source for Row number 262834" My problem is that I do not know how to find that specific row what is Row 262834, I have a Primary Key on that table called CSMAINID, is there a way to have the value from CSMAINID for the bad row go to the exception log, or how do I go about finding the row just from its "Row Number"
Here is the full error log.
DATA TRANSFORMATION SERVICES: Data Pump Exception Log
Package Name: TESTING
Package Description: Transformations also done in Select Query prior to task
Package ID: {C8AB89C6-A5C2-4944-8E86-0D0F635DFF91}
Package Version: {836E9170-A4E3-4FD0-B299-3143B2E245BA}
Step Name: Copy Data from CSMAIN to [Leas].[dba].[cfs] Step
Execution Started: 2/6/2006 11:48:00 AM
Error at Source for Row number 262834. Errors encountered so far in this task: 1.
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:Invalid length parameter passed to the substring function.
Error Help File:
Error Help Context ID:0

Execution Completed: 2/6/2006 11:49:51 AM

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

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

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello David,

Check up the table "cfs" and find out the last row it has inserted into it and based on it you can find the same in the source data file.

Thanks and have a nice day!!!

Lucky

DavidNPD Valued Member Points: 55 More actions · Answer 2

Thanks but because the transfer fails no data get written to the cfs table, is there a way to make it write even if it fails?

iBuildDW Mr or Mrs. 500 Points: 587 More actions · Answer 3

Go to the Options tab in the Transform Data Task Properties.

In the Data movement section change your Max Error count from zero (to anything higher, depending on how many errors you want to allow)

In the Exception file section, put a file location\name in the Name section. (I leave the default 7.0 format)

Re-Run your Transform Data task, the offending rows will be in the Exception file, and the remaining rows will be in your destination table.

Hope this helps..

Michael E. Baker Say Hey Kid Points: 676 More actions · Answer 4

I assume you are running it in the designer screen:

In the ActiveX Script, trap the error and make the script display the key data should an error be found. Example:

Function Main()

On Error Goto MyMess

--- Your transforms are here ---

Main = DTSTaskExecResult_Success

Exit Function

MyMess:

Msgbox DTSSource("KeyColumn1") & "|" & DTSSource("KeyColumn1") {& etc....}

End Function

It will messagebox you with the first error only then it will fail the package. If you have subsequent data errors, you will need to deal with them one at a time.

Michael E. Baker Say Hey Kid Points: 676 More actions · Answer 5

BTW,

Be sure to comment the msgbox out before you schedule the package.

DavidNPD Valued Member Points: 55 More actions · Answer 6

I like the messagebox method, thanks. I have recieved another idea from another source as shown below, this worked well also. Yours is eaiser to implement though, thanks.

SELECT IDENTITY (int, 1, 1) AS LineNumber, CSMAINID, STREETNBRN

INTO #Temp

FROM dbo.CSMAIN

SELECT * FROM #temp WHERE LineNumber = 262834

DROP Table #Temp