Data Conversion Failed Importing from text file

Question

Data Conversion Failed Importing from text file

pop

SSCrazy

Points: 2130
More actions
March 9, 2007 at 11:41 am

#171617

I am getting the error below on one column "sexcod". It can be either 'M','F', or NULL. It is 1 char in the source, and char(1) in the SQL table. Anybody know what might be causing this? I don't know about you guys, but I'm having a heck of a time using SSIS so far compared to DTS.
The data conversion for column "sexcod" resturned status value 4 and status "text was truncated or one or more characters had no match in the target code page"
HELP?!?!?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i see errors like this when the source file is not formatted perfectly...for example, if you think the file is comma delimited, but a COMPANY column has a a comma in it as well, like a value of 'Widgets, Inc.' or something.

the comma throws off the values , and seemingly adds an extra column to tehd ata...then some columns get shifted over and you might get a string stuffed into an integer, or a smaller field into a larger, like you see here;

double check the source file and see if that might be the case.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jakub Rycombel SSChasing Mays Points: 640 More actions · Answer 2

I had similar problem and found a workaround for that putting a Derived Coulmn task after the Flat File Source.

The following transorm for "sexcod" column should help - "N" - dummy value raplacing NULL or empty :

ISNULL(sexcod) || LEN(TRIM(sexcod)) == 0 ? "N" : TRIM(sexcod)

pop SSCrazy Points: 2130 More actions · Answer 3

Thanks! I'll give that a try.

But what if you wanted to allow NULLS? Does SSIS have issues importing NULL values? Anybody else have any thoughts on this? Thanks guys!

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 4

I have had problems like this in the past and what I have done is read the source file into a loader file using a script component.

I wrote a function in the script component which cleaned each line before writing it to the new loader file and the data flow picked this up instead.

Quite useful if you don't have control of the source data...

Kindest Regards,

Frank Bazan