Data Conversion Transformation

Question

Data Conversion Transformation

Viewing 4 posts - 16 through 18 (of 18 total)

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

Just checked
1. MSDN
https://msdn.microsoft.com/en-us/library/ms141706.aspx no mentioning about truncation.

What I read in the middle of that page:

If the length of an output column of string data is shorter than the length of its corresponding input column, the output data is truncated. For more information, see Error Handling in Data.

If the data conversion is explicitly defined to convert varchar(200) to varchar(20), it does this without throwing an error. Neither the input nor output column lengths are being violated, so there is no error.

If you connect it to a source with a varchar(1000) column and pass it strings longer than 200 chars you might see the expected truncation errors. As well as design-time warnings that this truncation may occur.

Konstantin Reu Hall of Fame Points: 3149 More actions · Answer 2

Scott Coleman (7/26/2016)
Just checked
1. MSDN
https://msdn.microsoft.com/en-us/library/ms141706.aspx no mentioning about truncation.
What I read in the middle of that page:
If the length of an output column of string data is shorter than the length of its corresponding input column, the output data is truncated. For more information, see Error Handling in Data.
If the data conversion is explicitly defined to convert varchar(200) to varchar(20), it does this without throwing an error. Neither the input nor output column lengths are being violated, so there is no error.
If you connect it to a source with a varchar(1000) column and pass it strings longer than 200 chars you might see the expected truncation errors. As well as design-time warnings that this truncation may occur.

I'm fine with that !

Issue is in answer "The source data is truncated at 20 characters." which is wrong. Correct one should be "it fails"

because the default setting for truncation is failure / error.

Just try it ...

MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

Nope, there is no truncation error. A truncation error would be a violation of the input or output column definitions. The truncation is done by the defined data transformation, basically doing outputcolumn = LEFT(inputcolumn, 20). So at no time are either the input or output column lengths violated.

Your argument that an error should be thrown in spite of the intermediate transformation that makes the value legal would be like saying the expression SQRT(ABS(x)) should throw an error for negative values of x.

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 4

There is a truncation error. Because by default in SSIS truncation errors fail the component.

As shown below, without changing any other settings, taking a 100 char and converting it to length 50 char results in an error. I can choose to ignore this error but the question doesn't mention that.