DTS Issues

Question

DTS Issues

El barto

Ten Centuries

Points: 1072
More actions
March 26, 2008 at 5:48 am

#70149

Hi, We have a DTS package that loads data, around 20,000 each night. This job has run happily for a number of years without issue.
Yesterday the job failed with the following error :-
Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Invalid delimited data: text qualifier must be followed by a column delimiter (except the last column).
Step Error code: 80004005
Step Error Help File:DTSFFile.hlp
Step Error Help Context ID:0
The environment has not changed, nor has anything on the source system. After reviewing the source file I am still unable to locate where the errror is.
Could anyone provide me with some assistance?
Many thanks.
Kindest Regards,
Nick

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

tosscrosby SSCoach Points: 18045 More actions · Answer 1

Something has changed if it ran for years without issue. My guess is your source file. The error is pretty self-explanatory as it states your column delimiter is missing (what type of delimiter is defined in your DTS package).

-- You can't be late until you show up.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

If nothing has changed on the system, then keep looking in the source file... the problem is there and you've just not seen it yet. My favorite error is when they use a "text qualifier" as part of a name or something. For example...

"Jackson, Andrew "Stonewall"",123,456,"some other info"

See the error? They've included a nick name in the first field and decided to use the text qualifier character of (") to indicate the nick name.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

One way to find the bad row is to import it into a two column table (1 for a row num, the other for the data). You know how many quotes there should be for all the rows... if you do something like the following, it may find the row...

SELECT RowNum, TheData

FROM yourtemptable

WHERE LEN(TheData) <> LEN(REPLACE(TheData,'"',''))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

El barto Ten Centuries Points: 1072 More actions · Answer 4

Hi,

Thankyou all for your replies. I have imported the file into a seperate table and have noted the error.

It would appear that carriage returns are causing the issue, this then impacts on the alignment of the fields. I'll will have to complete some further investigation.

One additional question... Is there a way when importing records using DTS to capture the line that the process fails on?.

Many thanks for your assistance.

Kindest Regards,

Nick

tosscrosby SSCoach Points: 18045 More actions · Answer 5

Would be a great feature but I haven't found one. It's usually an all or nothing process. One common error here occurs when our source file contains more characters than expected and we get the dreaded possible truncate error. But again, I've only seen an all or nothing process.

-- You can't be late until you show up.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

El barto (3/26/2008)
Hi,
One additional question... Is there a way when importing records using DTS to capture the line that the process fails on?.

If you're using a Transform Data task to import the data, you can designate exception files to capture source error rows.

Open the properties of the Transform Data task and go to the "Options" tab. Enter a name for the exception files and, in the "File type" section, uncheck "7.0 format" and check "Error text" and "Source error rows".

Execute the task and open the exception files when the task fails.

Greg

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Dunno about that, but there sure is in a BCP import. But, I think the DTS route would be better because if you're using text qualifiers in the file, you'd also need a format file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

El barto Ten Centuries Points: 1072 More actions · Answer 8

The issue is now resolved.

Thankyou all for you assistance.

Kindest Regards,

Nick

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

Glad you got it resolved... what did you do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

El barto Ten Centuries Points: 1072 More actions · Answer 10

Hi Jeff,

I split out the file into four segments and loaded it into Excel. I could then filter out the formatting characters until I found the row with illegal characters.

A field had come across from the source system with the characters ' 48" 'for fourty eight inches. The " broke the formatting on the file preventing it from loading.

Many thanks.

Kindest Regards,

Nick