Debugging DTS Errors

Hi,

I have a small import application that just calls a stored procedure which calls a DTS which imports a .csv file. the file usually has about 300 records and is created every day or so. Sometimes the DTS fails because one of the records has already been imported (primary key contraint). and the only error handling I have returns a message from the stored procedure if the DTS has failed or not. What I would like to be able to do is tell the user which line or record the DTS failed on. So that they can remove the line and import the file again. I don't want to just skip over the line in the DTS because it may have failed because there was an extra double quote or something like that. Any ideas?

Here is my code for the stored procedure which is calling the DTS:
CREATE PROCEDURE [dbo].[sp_Run_USPS_DTS]
@.strError nvarchar(100) OUTPUT

AS
DECLARE @.object int
DECLARE @.hr int

--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
BEGIN
SELECT @.strError = 'error create DTS.Package'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'LoadFromStorageFile',
NULL, '\\DBServer\DTS_Files\USPSReports.dts', ''
IF @.hr <> 0
BEGIN
SELECT @.strError = 'error LoadFromStorageFile'
RETURN
END

EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
SELECT @.strError = 'Execute failed'
DECLARE @.MaxNum int
SELECT @.MaxNum = max(ReportNum) FROM USPS_Reports
DELETE FROM USPS_Export WHERE ReportNum = @.MaxNum
DELETE FROM USPS_Reports WHERE ReportNum = @.MaxNum
RETURN
END
GOI've implemented quite a number of data import processes, and its usually a multi-step process. What you need to do is separate out import file validation (the possible extra double quotes) from import file import..bringing the data into the dB.

Don't assume your data is good. Assume it is bad. Invoke a "validation" process and ensure it passes before you ever try to import the data. CSV files are great because you can write a little managed class that just opens and parses the file line by line looking for errors. You could even trigger the Stored Procedure to fire from within this framework only if validation passes.

Alex