Text was truncated error in SSIS package

Open the connection manager for the file. Go to Advanced tab, highlight the suspect column and you can change the length there. I believe the default is 50.

Let me know,

HoggZilla

I am testing the error now, will follow up with my findings.

HZ

pven13,

I'm trying to load in a flat file into a SQL Server database.  The data is being piped from a flat file source through a Data Conversion component, and into a OLE DB Destination component.  I verified that the flat file source is where the issue is, and I can even put a trash destination right after the Flat File source, and it will still fail.  I'm sure the source file is ASCII.  I switched it to UTF-8 but it won't even load at that point.  I also tried switching it to Unicode.  The data I entered into Experts Exchange didn't go over correctly though, so it doesn't look right.  It's supposed to be |OLD - MICH| (not including the pipes), which is ten characters.  But the - is actually an en-dash (ASCII code 150).

HoggZilla,

Let me know if you can find anything.  I've been testing so many different things with this but this file just won't load.  I know that if I change the en-dash to a regular dash, it loads ok, so it seems to be related to that dash.  But this data is coming from SAP so it has to come through this way.  Other columns pull in the en-dash with no problem; just this column doesn't.

You need to look at the text with a Hex editor.  Until you do that all you are doing is assuming.  If you cannot do that, then upload a sample of the text file here and we can confirm.

Someone told me I had to try recreating the connection manager and the flat file source from scratch, so that might be what I have to do.  There's over 150 columns in this flat file though so I'm hoping I don't need to go through that.

Is the en-dash considered part of code page 1252?  HoggZilla, did you use an nvarchar or varchar data type, and did you set the column size to 10?  If so, I might just have to go in and redo the component.

I'm going to try a test like you did and see if it works that way.  So at least I'll know it's not the text file.  I'll respond soon.

acperkins,

Here's the output of a hex editor:

|OLD ⬠MICH|

7C 4F 4C 44 20 E2 80 93 20 4D 49 43 48 7C

The en-dash ends up showing three characters: an a with a caret, a euro sign (I think), and an apostrophe.  Just in case Experts Exchange doesn't show them.  Not sure what this means but I do see all the other values are accounted for.

>>The en-dash ends up showing three characters<<
Yep.  That is the cause of your error.  You will have to increase your field definition to 12.

Incidentally ASCII 150 looks like this:
|OLDMICH|
7C 4F 4C 44 96 4D 49 43 48 7C

Which is in fact 10 bytes (within the pipe symbols)

Post a sample file and your package. Save the package with a .txt extension to post. As I said, I loaded the same data you posted with no issues. Something in your setup and I will tell you what it is when I see your package.

Thanks, HZ

Here's my package and a sample file.  I have all the records in there that are failing and a couple that aren't are at the bottom.  The couple that aren't don't have the en-dash; at least not in the SORTL field that I'm having trouble with.  The field that has the name (it might be NAME1) has en-dashes for the rows that are failing but it's not failing on that field.

acperkins,

For your first response back about the size, I tried setting it to 11, 12, 20, 30, 40, and none of them worked.  I thought maybe there was something it was doing but it didn't help.  Also on your second post, the data is missing the spaces between the en-dash.

Take a look and let me know what you guys think.  I might end up calling the people responsible for the data and have them change the en-dashes to regular dashes if it gets us our data.  Either way I'd love to know if this is possible.

It is like you stated originally, they are not using ASCII 150 and adding additional characters.

acperkins,

I'm not sure what you mean by your last comment.  When you said they're not using ASCII 150 are you talking about the records that are succeeding?  Because they definitely are using ASCII 150 for the records that are failing (the SORTL column at least).  Also, what additional characters are they adding?

I will post the relevant part of your file at the end of this comment.

But if you open it up in a hex editor the first 6 records do not contain ASCII 150 and contain extra characters.  The last 2 records have dashes and not ASCII 150 character.

In fact there are no ASCII 150 (0x96) character anywhere in that file.

It should be also noted that the file contains a BOM, I am not sure if you noticed.  This could be significant in how it is read by most text editors.  Again a Hex editor is your best friend.

|OLD ⬠MICHIGAN TEAM ⬠COMM ONLY    |
|OLD ⬠MICHIGAN REGION ⬠COMM ONLY  |
|OLD ⬠MEIJER ⬠COMM ONLY           |
|OLD ⬠SPARTAN ⬠COMM ONLY          |
|OLD ⬠MICH/IND ⬠COMM ONLY         |
|OLD ⬠NORCAL MKT ⬠COMM ONLY       |
|OLD - PHOENIX BRK - COMM ONLY      |    
|OLD - PACIFIC NW BRK - COMM ONLY   |    
                                              

1:
2:
3:
4:
5:
6:
7:
8:

acperkins,

Thanks for taking a look at this for me.  I wasn't aware there was a BOM at the beginning of the file, but I hope SSIS is able to ignore it when importing the data.  What's interesting is I opened the original file in notepad and removed all the records before the YC124 record.  So there should be no BOM in the file at that point.  If there is, it must be because notepad ignores it and deleting info does not delete it.

Also, I took the flat file (the same text file that I uploaded) and pulled out the character that in the hex editor created the â¬.  I pasted it into SQL Server and did an ASCII() check on it and it came back as 150.  I also copied it and pasted it into Word and checked it out, and it's most definitely an en-dash.  I created a macro in Word that created a string with an en-dash and then compared that string with the en-dash I copied in and they matched.

I think I'm just confused as to the different characters that keep appearing on hex editors.  I used two online editors.  One showed two dashes like -- in place of the single dash, and one showed three periods like ... which I think is because it couldn't show special characters (the hex value of the first period was E2, the same as the â.  The last two didn't look like ¬ or anything close to it.  Why is everything reading it differently and what is SSIS trying to do with it.  When I check the preview of the data, I see just a single dash and none of the other characters I keep seeing.