Invalid Character in Flat File or Turncate problem

My source is a csv flat file. Currently I use that same flat file on SQL 2000 and SQL 2005.

On SQL 2000 it runs fine and it inserts that character as part of the string (varchar), however, it gives me truncate error on sql 2005. I already use the "Suggest Types...." and my Output columns have the correct lenght (specially that lehght of that column is only 30 char which is less than the default anyways). If I remove that character it runs fine for that column.....

This is the values that I get in my flat file for the trouble coulmn is "ATTN: JON OLSEN a€“ CTRL8 "

And the error that I get when running the SSIS is

[Flat File Source OrderDetail [1]] Error: Data conversion failed. The data conversion for column "Column 15" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Flat File Source OrderDetail [1]] Error: The "output column "ShipToAddr1" (63)" failed because truncation occurred, and the truncation row disposition on "output column "ShipToAddr1" (63)" specifies failure on truncation.
A truncation error occurred on the specified object of the specified component.

[Flat File Source OrderDetail [1]] Error: An error occurred while processing file "C:\Inetpub\ftproot\orderdetail.csv" on data row 6.

Any help greatly approciated.....

Thank you,

Maria

What code page are you using? 1252?|||

Yes, It is 1252.

|||There are more than 30 characters in your example.|||

Hi Phil,

yes, you are right I just counted and it is 32 characters.....What do you suggest - Should I have the flat file to be corrected OR should I handle it on my side in SSIS? If to handle on my side in SSIS - than what's the best way to address it? The table column is 30 char.

I did not realize as it never has been problem in sql 2000

Thank you,

Maria

|||Since you are going into a varchar, try trimming the column.

Stick a derived column between the source and destination and use the RTRIM() character function to eliminate the trailing whitespace. Then it'll fit.

So, in your expression, you'll do: RTRIM([columnname])

Try that. The characters are valid in the 1252 code page, so that shouldn't be an issue.|||

Phil,

The derived column did not solve the issue - it fails before it reaches the derived column. I went to the Flat File Source Editor -> Error Output and change to "Ignore Failure" for Truncate for that Column and that did it. But I am not comfortable with doing it....not sure if that is a right way to do it.

Can you think of anything that I am overlooking before reaching the derived column?

Thank you,

Maria

|||

It may be better to use 'Redirect rows' to a external file for further inspection. May be if you see all the faulty rows you would get better clues. If you still want those rows to make the final destination use a multicast after the error output; then use one output to the error file destination and the other to merge them back to the main flow (via Union all). This won't solve your problem but at least will give you the ability of looking into the faulty rows.

Just a suggestion...

|||How exactly do you have your SSIS package setup? What source, destination, and transformation components do you have and in what order?

You might have to put the derived column *right* after the source and then you may have to remap some columns in downstream components so that they pick up the correct column lengths.|||

Thank you Rafael. I will try that.

Maria

|||

Phil,

within my Data Flow i have it set up as follow

Flat File Source -> Derived Column ->OLE DB Destination.

The data flow part should pretty much take the flat file and insert it into my Tmp table.....and then within Contol Flow i do the update, insert, etc from that tmp table. But the problem that I get is within my Data Flow, Flat File Source to be specific. I only have one Derived Column to format data for couple of other columns including the RTRIM(column_name) that you suggested earlier.

Thank you,

Maria

|||Yep, try Rafael's suggestion and then report back with the error number that's provided for those rows.

Thanks,
Phil|||

Phil and Rafael,

I ran a test with just first 6 rows to see what exactly is happening......And in my ErrorOutput file I was able to see the ErrorCode which was always -1071607675 (Truncate error) in my case and then ErrorColumn which was the ID. What seems to be is that when I did the "Suggest Type....." it picked the Flat File Source External Column width from flat file which has columns width bigger by 1 or 2 char from Flat file source Output column....So that is why this is failing and gives me the truncate error message. The Flat file source Output Column has the correct width. The bigger width in External Column is random and it is dependent on whats in the flat file so I cannot assume that my Product Description column will be always 81 varchar, for some products might be bigger depending on what the invalid character will be......I guess it is caused by the ascii char that cannot be properly handled by text editor.....

We will have to address the problem at the point where the flat file is being generated and my SSIS should work just fine.

Thank you both for your help.

Maria

|||

mariap wrote:

Phil and Rafael,

I ran a test with just first 6 rows to see what exactly is happening......And in my ErrorOutput file I was able to see the ErrorCode which was always -1071607675 (Truncate error) in my case and then ErrorColumn which was the ID. What seems to be is that when I did the "Suggest Type....." it picked the Flat File Source External Column width from flat file which has columns width bigger by 1 or 2 char from Flat file source Output column....So that is why this is failing and gives me the truncate error message. The Flat file source Output Column has the correct width. The bigger width in External Column is random and it is dependent on whats in the flat file so I cannot assume that my Product Description column will be always 81 varchar, for some products might be bigger depending on what the invalid character will be......I guess it is caused by the ascii char that cannot be properly handled by text editor.....

We will have to address the problem at the point where the flat file is being generated and my SSIS should work just fine.

Thank you both for your help.

Maria

Just remember that suggest types is only valid for getting you to a starting point. You should still implement your own quality control for every column to be sure they are correct. Don't rely on the suggest types feature to be accurate as you are finding out.

Phil|||

Thank you Phil. you are absolutly right!

Maria