Data truncation issues, for no reason.

Question

Data truncation issues, for no reason.

winston Smith

SSCoach

Points: 19500
More actions
February 6, 2009 at 6:13 am

#127929

I have a flat file with a number of columns.
I have a destination table with the same number of columns.
In between i have a data convesion object to map the flat file data to the correct
datatype to be inserted in to the table.
The dataConversion output columns are all set to the correct datatype and size as specified
by doing a sp_help dest_table.
When i run the package I get a ton of truncation errors and the data transformation fails.
the first column in the error list is COL1 for simplicity.
in the table, this is a nvarchar(200).
In the data conversion object i have set this to a Unicode String[DT_WSTR] of size 200.
This still fails.
When i click on advanced properties and view the OLE DB destination External Columns (columns
in the table that im mapping to, the COL1 size is 100. I can clearly see from SSMS that the
column size is 200, but SSIS sees it as 100. whats worse, when i try to change it to 200, close the
advanced editor and open it again, the value has gone back to 100.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I think I recall having seen an issue like this before. I'd suggest deleting the OLEDB Destination and recreating it. I think the package caches the datatype information somewhere, so I'd try that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ssexton Mr or Mrs. 500 Points: 574 More actions · Answer 2

Would your suggestion be the same when the datsource is an ACCESS database that is the source for follow-on packages?

Thanks,

Sabrina

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

The other thing you want to look at is that the definition of the input columns (in the SSIS package) actually matches the lengths. They default to 50, and that in itself can cause truncation errors. Go into the advanced settings of the "starting point" where the input data is being defined, and you will likely find something too small in there.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

winston Smith SSCoach Points: 19500 More actions · Answer 4

you were correct. In the connection manager, i had not set the flat file column sizes correctly. once i done this, the flat file source was correct.

i have this joined to a OLEDB sql table destination. the problem now though is that i keep getting the truncation error, and when i look at the column types and sizes on the destination they are not what they should be.

eg. sp_help tableName shows me that col1, col2 and col3 should all be varchar(50), but in the advanced editor for the OLEDB destination all of these columns are Unicode (Nvarchar) of size 100, which is default.

i cant change this in the dtsx project as when i change them and come out of the advanced editor, they revert to unicode string (100).

I thought once i specified the table in the OLE DB connection, the column sizes should be picked up correctly from the table scheema. whats going on here?

winston Smith SSCoach Points: 19500 More actions · Answer 5

Here is some further information to help get my issue across more clearly.

here is an image of the table, as seen via the OLE DB Destination in SSIS:

http://s78.photobucket.com/albums/j87/schmintan/?action=view&current=Output_Columns.jpg

You can see for Address Line 1 column the datatype is Unicode String [DT_WSTR] of size 100. this is the same for Address Line 2, Line 3 and a number of other columns.

Here is an extract from SP_helpText table:

Column_nameTypeComputedLengthPrecScaleNullable

Address Line 1nvarcharno200 yes

Address Line 2nvarcharno200 yes

Address Line 3nvarcharno200 yes

as you can see in sp_helptext the columns are clearly nvarchar(200), yet SSIS sees them as 100. why is this?

winston Smith SSCoach Points: 19500 More actions · Answer 6

im sorry to bump this up, but having tinkered with it for hours on end, and googled it a lot i still cannot see why the column datatypes are a certain size, and Visual studio SSIS project sees them as a different size.

anyone got any theories?

Robert Ham-354502 SSC Veteran Points: 234 More actions · Answer 7

The length returned by sp_help is "Column length in bytes." (BOL) and nvarchar uses two bytes to store each character. So, the columns are actually nvarchar(100), and should show as such when using SSMS to browse the table or generate a create table script.

This is not immediately obvious in the results of sp_help, which would be more helpful if it used a column heading of bytes instead of length.

tcormack Newbie Points: 3 More actions · Answer 8

i was having the same issue. I was importing a pipe delimited file. ssis kept saying there was a problem with column 6.

today I tried again but on the first page where you select flat file data source I went into each column and changed text qualifier to No. The import has finally run.