Solved

DTS error handling

Posted on 2004-04-04
15
1,618 Views
Last Modified: 2013-11-30
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.



0
Comment
Question by:pigface
15 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10752620
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
 
LVL 8

Expert Comment

by:plq
ID: 10752686
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
 

Author Comment

by:pigface
ID: 10752737
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
 
LVL 34

Expert Comment

by:arbert
ID: 10753354
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
 

Author Comment

by:pigface
ID: 10753427
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
 
LVL 34

Expert Comment

by:arbert
ID: 10753618
Not totally sure--I use this method without problems.  You don't have the "7.0 format" check box checked do you?
0
 

Author Comment

by:pigface
ID: 10753728
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 34

Expert Comment

by:arbert
ID: 10753878
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
 

Author Comment

by:pigface
ID: 10755179
yep sp3a
0
 
LVL 34

Expert Comment

by:arbert
ID: 10794323
So what's the final word on this?  Did you still not get it to work?
0
 

Author Comment

by:pigface
ID: 10797508
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
 
LVL 34

Expert Comment

by:arbert
ID: 10803015
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
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 10809482
PAQd and 500 points refunded

GhostMod
CS Moderator
0
 
LVL 34

Expert Comment

by:arbert
ID: 10809774
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now