Solved

DTS error handling

Posted on 2004-04-04
15
1,624 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 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