September 18, 2008 at 6:31 am
hey there, I have a quick question about handling DTS package errors. I am looking for a way to gracefully handle DTS errors while importing data. I would like to be able to log the record that was broken and then continue importing data without a stoppage. Is there a way to do such a thing?
Thanks much
September 18, 2008 at 7:01 am
Hi Rick,
You can set the max errors value (on the options tab of the transform data task) to 9999, allowing that many errors before it falls over.
To capture the failed rows you can execute the package like this:
CREATE TABLE #output (id INT IDENTITY(1, 1), output VARCHAR(8000))
INSERT INTO #output (output)
EXEC master.dbo.xp_cmdShell 'DTSRun /Sserver /NdtsPackageName /E'
SELECT *
FROM #output
WHERE output LIKE '%Error String%'
ORDER BY id
This will show a row for each failed insert followed by a row for the error description. I don't know if the failure description is always 8 rows on from the indicated error with the row number in, but if it is you could do something like the following to get a more readable result:
SELECT SUBSTRING(a.output, CHARINDEX('Row number', a.output) + 11, CHARINDEX('.', a.output) - (CHARINDEX('Row number', a.output) + 11)) AS row,
b.output AS error
FROM #output a
INNER JOIN #output b
ON a.id = b.id - 8
WHERE a.output LIKE '%Error String%'
AND b.output LIKE '%Error String%'
ORDER BY a.id
Hth,
September 18, 2008 at 7:42 am
This is exactly what I was looking for. I will give it a whirl as soon as I can.
Thanks friend!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply