Data truncation does not trigger truncation error - why?

Hi,

I have a data file that has numeric data that looks like:

1.123456

And this column is defined as a DT_NUMERIC(18.6) in the flat file conn mgr.

As an experiment, I changed the destination column to a NUMERIC(18,0) - hoping that this would throw a truncation error at the flat file task level (where I have Truncation on all columns set to "fail component").

Not a peep. It loaded the data into the table, chopping off the 6 digits after the decimal point.

You would THINK that this would cause an error, but no. Why is this? The flat file task complains about all kinds of things, but this is such a gross error, you would think it would catch it!

Thanks

Well, double-click on the green arrow right before the destination. Verify that the metadata for the column in question is different than NUMERIC(18.0).|||

I'm not sure I follow your reasoning... although that's a cool way to view the metadata for the flat file.

I am more concerned about a situation where a table column changes, data is getting truncated unknowingly and no errors are ever triggered.

I just find it strange that it doesn't catch something like this, whereas it's extremely picky about other things that seem inconsequential (to me anyways). I find this inconsistent, especially when losing digits after the decimal point could create a big problem for someone. I don't think I'm being unreasonable here...!

|||

sadie519590 wrote:

I'm not sure I follow your reasoning... although that's a cool way to view the metadata for the flat file.

I am more concerned about a situation where a table column changes, data is getting truncated unknowingly and no errors are ever triggered.

I just find it strange that it doesn't catch something like this, whereas it's extremely picky about other things that seem inconsequential (to me anyways). I find this inconsistent, especially when losing digits after the decimal point could create a big problem for someone. I don't think I'm being unreasonable here...!

My reasoning for having you double check the metadata is to ensure that in fact, there is truncation going on. Having a destination column of NUMERIC(18,0) should yield a truncation error when the metadata for a column going into the destination is specified as NUMERIC(20,5) or something like that.

If the precision is the same though, such as a NUMERIC(18,5) going to a NUMERIC(18,0), the default SQL Server behavior is to round. See: http://msdn2.microsoft.com/en-us/library/ms187928.aspx|||

Ok, thanks for the clarification

I verified this myself, you have to overflow the precision before you will get an error, although I was hoping it would catch it on scale