How to single out problem row and continue processing?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » How to single out problem row and continue...

How to single out problem row and continue processing?

Rate Topic

Display Mode

Topic Options

Author

Message

WebTechie38

Posted Monday, March 23, 2009 10:34 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 12:04 PM
Points: 255, Visits: 625

Hello,

I have a DTS package that is running well. Each night it runs as it should with no problem.

The problem comes in the form of a flat file. Each night the DTS package reads a flat file and imports the data into a table.

I noticed a problem last night with the run. At a particular row (15994), the packages had an error and stopped. I am sure I can bring the file into excel and find the problem row. I will fix it, save back as a text file and rerun the package.

How do you those with more experience than me account for rows in an import that may have problem? How do you have your package keep processing the rest of the rows when one row fails?

Thanks for the help.

Tony

Post #681631

dmc-608719

Posted Monday, March 23, 2009 10:57 AM

Old Hand

Group: General Forum Members
Last Login: Monday, April 23, 2012 7:35 AM
Points: 373, Visits: 761

One option is that you can send error rows to a seperate output flow. So send them to another task like writing them to an error table or file. Then you can deal with just the error records and allow the rest of the records to be processed.

Another option I have seen is to pre-process some the file and check for common errors. Then address these before the real processing begins. This obviously will still fail for unexpected issues (not neccessarily a bad thing).

I would suggest you look at error outputs on your tasks and setting failure properties.

Post #681648

WebTechie38

Posted Tuesday, March 24, 2009 4:54 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 12:04 PM
Points: 255, Visits: 625

Hi,

That is exactly what I am asking. w00t

How would you set up a Transformation Data Task to send error rows to an error table and still process the other rows that succeed?

When this step hits a record that has an error, the entire step fails. I then have a fail workflow that sends me an email.

But how do you send errors to a error table?

Tony

Post #682966

WebTechie38

Posted Wednesday, March 25, 2009 9:31 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 12:04 PM
Points: 255, Visits: 625

Hi,

After digging into this a bit, I see what I need to do is turn on the multi-phase datapump.

Doing this, I can code for insert failures. I've also found some articles that tell me that this functionality is not there for 2005. In order to turn on multi-phase data pump, you have to edit the registry. It is a value called DesignerSettings.

The problem is that in my registry under Current User\software\microsoft\microsoft sql server\80\dts, there is not a settings folder.

Anyone have any ideas how I can turn on multiphase data pump, when I don't see this folder in the registry?

Thanks.

Tony

Post #683388

« Prev Topic | Next Topic »

Permissions