November 29, 2004 at 11:08 am
I am trying to import a text file into SQL Server. Some records in the file do not quite meet the typical format of the majority of the records. Is there any way to direct records that fail to load to another flat file and continue on with the load? I'm looking for something similar to the Oracle fast load option which will allow a user to specify the number of errors that may occur before aborting the load and direct the records in error to a file.
November 29, 2004 at 12:56 pm
Are you using DTS to import the file? If so, look on the options tab of the Transform Data Task. There, you can specify error files that will contain rows that weren't written to the destination. You can also set a maximum error count that will allow the task to continue until the number is reached. See Data Transformation Task in BOL.
Greg
Greg
November 29, 2004 at 1:27 pm
Yes, I'm using DTS. That sort of accomplishes what I need, but not exactly. The package fails as soon as it hits the first error, which is "column delimiter not found". I did not have the Fail Package on First Error marked. Also, the error file isn't a file of the records in error, it merely contains the error message.
I'm using a column delimiter of a single space, {LF} for row delimiter, and double-quotes (") for text qualifiers. The problem I'm encountering on some records is that the last column occassionally contains " as part of the text so it appears that there is another column without a delimiter, but there really isn't. I cannot change the file format coming in so was hoping I could spool the records in error out to a separate file and then set up a slightly different package to handle those records.
Am I missing something or is this not possible?
November 30, 2004 at 6:18 am
If you are using dts and using a transform task object, you can go to properties then options then set max error count. When I want to ignor bad rows I set it to the total extimated rows imported. That would get your 'good' row.
I have found no easy way to import 'bad' data other than to import based on row as string to a separate table structure or something like that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply