Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DTS Issues


DTS Issues

Author
Message
El barto
El barto
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
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 FileBigGrinTSFFile.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
tosscrosby
  tosscrosby
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2688 Visits: 5316
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
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44794 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44794 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
El barto
El barto
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
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
tosscrosby
  tosscrosby
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2688 Visits: 5316
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
Greg Charles
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4089 Visits: 5818
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
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44794 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
El barto
El barto
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 425
The issue is now resolved.

Thankyou all for you assistance.


Kindest Regards,

Nick
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44794 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search