Solved

DTS error handling

Posted on 2004-04-04
15
1,621 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

770 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