Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DTS Issues Expand / Collapse
Author
Message
Posted Wednesday, March 26, 2008 5:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 9, 2011 4:45 AM
Points: 84, Visits: 425
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
Post #474630
Posted Wednesday, March 26, 2008 7:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316
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.
Post #474711
Posted Wednesday, March 26, 2008 7:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #474717
Posted Wednesday, March 26, 2008 7:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #474720
Posted Wednesday, March 26, 2008 8:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 9, 2011 4:45 AM
Points: 84, Visits: 425
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
Post #474754
Posted Wednesday, March 26, 2008 8:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316
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.
Post #474794
Posted Wednesday, March 26, 2008 9:19 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 9:17 AM
Points: 4,065, Visits: 5,316
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
Post #474836
Posted Wednesday, March 26, 2008 11:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #474966
Posted Wednesday, March 26, 2008 3:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 9, 2011 4:45 AM
Points: 84, Visits: 425

The issue is now resolved.

Thankyou all for you assistance.



Kindest Regards,

Nick
Post #475121
Posted Thursday, March 27, 2008 7:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #475417
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse