Hi

I'm importing 40 text files, with headers and delimited by Vertical Bar into
SQL tables with exact column matchings.
Initially the files has {CR}{LF} as the end-of-row delimeter, but this has
been changed to just {LF}

I am having trouble with just 6 of the 40 files, and there is nothing
different from them to the rest.

The last column is failing with "Conversion invalid for datatypes on column
pair xx"

If I ignore the last column it goes through OK.


When I preview the contents of the text file in the connection properties,
the last column shows up as NULL, when I preview in the DTS Transform data,
there looks to be a character there, but I don't know what it is....

Any help would be much appreciated.

Ben

Have you run it through a Hex Editor?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Beema" <none (AT) supplied (DOT) com> wrote

No, I don't know enough about that to do it.


I've done a test which may shed some light..

The destination column was an integer, I changed to varchar, an dthe file
imported fine. In the text file there only values were 1 and blank (ie NULL)

When I did the following query I got interesting output...

select distinct 'AA' + TheColumn + 'BB' from TheTable

The result I got was:

TheColumn
------------
AA
BB
AABB
NULL
1


I am confused about how this happened??





"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

So I think the end-of-line characters may be inconsistent??

Possibly some ending in {LF} and some ending in {CR} ??




"Beema" <none (AT) supplied (DOT) com> wrote