DTS error handling

Hi

I am importing a number of text csv files into a number of tables with SQL 2000.

I have set the DTS package up in such a way that if an error occurs then the task will continue and write the error to an exception file e.g. PK violation.

What I also need to do is this.....

For every row that fails, write that row to a "bad" text csv file.  So basically after the DTS package has finished I will have a number of exception files (for each csv to table) stating either total success or failure and if the latter occurs at least one bad file.

e.g.

Customer CSV

123, Mr, Fred, Smith
123, Mrs, Joyce, Smith

Exception file would state first row in ok, but second row failed.

My Customer_Bad.csv would contain:
123, Mrs, Joyce, Smith


How do I do this, is it possible?

Thanks.



pigfaceAsked:
Who is Participating?
 
GhostModCommented:
PAQd and 500 points refunded

GhostMod
CS Moderator
0
 
arbertCommented:
I'm not totally clear on your question with what you are already logging for errors.  You say you're writing the error to the exception file--on the exception file, you can also specify the format (CSV), and the errors that you want to write out (Error Text, Source Error Row, or Dest Error Row).

 If you want anything above a beyond this, you'll have to code it in ActiveX script with the FileSystemObject.
0
 
plqCommented:
The way I did this was to import ALL the data to a table in a different database which was tolerant of all data errors, so no PK's, not unique indexes, all fields NULLABLE etc

Then you validate the data before sending the data across to the main database using insert ... select. ... where ...

Its a different architecture from what you have, but gives the following advantages

1. Your temp tables can be in a different DB so you can use non logged operations like "bulk insert" to import the data quicker
2. You can cross join your temp tables with your live tables to produce reports on what data didn't make it across
3. Keeps your main DB clean
4. Gives you greater control to program what the validation rules are through sql statements
5. Is a clean and well structured solution. Its server friendly and doesn't cause the DBA any probs because all the bad data is out in a transient database

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pigfaceAuthor Commented:
arbert

The exception file will contain something like

Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:Violation of PRIMARY KEY constraint 'PK_DTS_Test'. Cannot insert duplicate key in object 'DTS_Test'.
Error Help File:
Error Help Context ID:0
 
Error at Destination for Row number 3. Errors encountered so far in this task: 3.

Which is great as it tells me an error for a given row and what the error is.  But imagine my csv has 1/2 million rows in it and 200 rows fail.  I would like to place those 200 rows into a "bad" file (in the sameformat as the original csv file) so that I have the bad rows isolated and I can examine them in more detail rather than looking through 1/2 million rows for my 200.

Why do I want the exception file?  So I have a basic summary file so I can see what went wrong and where.

Is this any clearer?
0
 
arbertCommented:
I guess I still don't understand.  On the options tab for your transform, you can tell it to log your  "Dest Error Rows" into a CSV file and even tell it the format you want.    When the job runs.  The error text is logged to the filename you give.  If you want to see the Soure Error Rows, it gets logged to the same filename with a .SOURCE extension.  Destination error rows get logged to the same filename with a .DEST extension.
0
 
pigfaceAuthor Commented:
arbert

I have tried what you recommended in your last post and I get the following in my exception file:

@@ErrorRow:  1
Error at Destination for Row number 1. Errors encountered so far in this task: 1.

Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:The statement has been terminated.
Error Help File:
Error Help Context ID:0


Error Source: Microsoft OLE DB Provider for SQL Server
Error Description:Violation of PRIMARY KEY constraint 'PK_DTS_Test'. Cannot insert duplicate key in object 'DTS_Test'.
Error Help File:
Error Help Context ID:0
@@SourceRow:  Not Available
@@DestRow:  Not Available

In the directory that the exception file is saved I have no *.dest or *.source files?

Am I doing something wrong.  I have one text object pointing to an ole db object and have setup the object transaformations bit as you stated.
0
 
arbertCommented:
Not totally sure--I use this method without problems.  You don't have the "7.0 format" check box checked do you?
0
 
pigfaceAuthor Commented:
No I have unchecked that, selected all of the other options in that grouping - source, dest and error text.

I have specified an exception folder and file name.

set an error count > 1 and set insert batch size to 1.
0
 
arbertCommented:
Hmmm, I'll try and setup a test with just primary key violation and see what I get...You're running SP3a on both your server and your desktop tools right?
0
 
pigfaceAuthor Commented:
yep sp3a
0
 
arbertCommented:
So what's the final word on this?  Did you still not get it to work?
0
 
pigfaceAuthor Commented:
No I had two issues:

Primary key issues were not logged to a dource / dest file

If more than one error occured on a given row only row error was reported to the exception file.

So as you can see my question was never answered totally and given the points on offer I would have expected a good answer or a good url.

I have since started to design a dts package myself that uses vb script and stored procedures to define every single error as and when they occur.
0
 
arbertCommented:
Like I said above " If you want anything above a beyond this, you'll have to code it in ActiveX script with the FileSystemObject."
0
 
arbertCommented:
Maybe I should have been a little clearer on my post...What the question asker finally ended up doing is what I said in my first post...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.