Data Conversion Transformation
-
Comments posted to this topic are about the item Data Conversion Transformation
-
I dont know what hap
-
One hooray for the inconsistency of SSIS!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
If SSIS were a civilised piece or work it would give me the option to tell it allow or disallow truncation, and if I picked disallow tell it what to do with long values.
Tom
-
Got me on this one.
I need to play with SSIS in 2016.
I just guessed that the truncation would work similar to previous versions and throw an error unless you specified to handle it differently and route it somewhere else.
-
Not knowing much about SSIS beyond how to spell it, my guess based on knowledge of SQL.
-
Had a doubt between option # 2 and 4...but I finally decided to go by option # 4 (Rows with 20 or less characters are passed through to the output. Those > 20 are routed to the error output.) and did not score anything.
Why not option 2 is the right one? If anyone had tried with this option, please let me know more on this.
Thanks.
-
One of the many reasons I never use this transform.
I do all my data conversion in a Derived Column task, which does let you handle truncation errors by column.
-
That is a bit of a surprise. Glad I haven't moved to 2016.
-
I found this:
https://msdn.microsoft.com/en-us/library/ms141679.aspx
From BOL:
Truncations. A truncation is less serious than an error. A truncation generates results that might be usable or even desirable. You can elect to treat truncations as errors or as acceptable conditions. For example, if you are inserting a 15-character string into a column that is only one character wide, you can elect to truncate the string.
-
I'd be very very surprised if this changed for 2016. Will have to test when I get home. Currently working on 2014 and the default for truncation is Fail Component, you have the option to redirect the row or ignore the failure.
-
The default setting for truncation is failure / error.
from MSDN:
Fail Component The Data Flow task fails when an error or a truncation occurs. Failure is the default option for an error and a truncation
the "correct" answer is actually not the correct one
-
Just checked
1. MSDN
https://msdn.microsoft.com/en-us/library/ms141706.aspx no mentioning about truncation.
2. VS Studio 2015 & 2016 project. Behavior as expected - by default component configured to fail. Test proved that.
Where is quality control in QoT ?
MS SQL 2008 MCITP x 3
MS SQL 2012 MCSE x 2 -
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.
-
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply