Precision and scale from flat file with no decimal point
I can't figure out if "precision" and "scale" in flat files connection manager can give me an implied decimal. Say each record is an amount and a name: 00101MrTuesday 00102MsWedndesay The numbers represent 1.01 and 1.02 I would hope I could put in the flat file something to say the Scale is 2 and Precision is 5, and down-pipeline tasks would see 1.01 and 1.02. I've tried dt_numeric and dt_decimal data types in my flat file connection manager advanced properties and tried setting DataScale to 2 and (in numeric, no such option in decimal) setting DataPrecision to 5, but the numbers just come out as 101 and 102 when I write them into an ODBC database. Would you expect this to work (I'm doing the right thing wrong) or is there something similar (I'm doing the wrong thing)? In fact what I have is dozen of currency fields and percentages with no decimal points - old Cobol style data we get from competitors who won't change the format. I realize that I could transform the field(s) but then that has to be done for each dataflow that uses the connection manager.
November 24th, 2010 2:55pm

Try Changing it to Numeric [DT_NUMERIC], here you can set Precision and scale.Gaurav Gupta http://sqlservermsbiblog.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
November 24th, 2010 3:01pm

When you say "try changing it" does "it" refer to column properties (DataPrecision and DataScale) in the flat file connection manager? I've tried that and got nowhere but I'll start over and see how it goes - is this somethin you know should work? SSIS with 2008, not R2 Thanks, Levin
November 24th, 2010 3:30pm

I don't believe you can set up the Flat File Connection Manager to infer implicit decimals. What you need to do is set those columns to be a numeric type in the Connection Manager with the appropriate precision and scale you want to end up with. Then add a Derived Column transformation after the Flat File Source to divide the columns "in-place" by whatever scale you're looking to end up with. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
November 24th, 2010 3:33pm

Thanks, I just tried starting from scratch after reading Gaurav's post and I think you're right but I'll hold off on marking that as the answer for a bit, just in case someone has something to add. So I don't understand what the point is of bothering to tell SSIS what the data type and scale is in the connection manager if I have to fix it by another transform. Is there a simple explanation/example of why/where it would make a difference if I left it with scale 0 and if I told it the correct scale? Also (maybe related to my "why bother" question) - DT_Numeric is an exact number, not floating point, is that right? So ultimately what I care about is if I say a field is 12345 / 10, I always get 123.45 and never get 123.46 or whatever when I write out to my destination - I realize that's dependent in part on my destination (an odbc driver from some obscure publisher) but on SSIS side, I'm write to think it's my DT_NUMERIC is an exact number? Thanks very much
November 24th, 2010 3:56pm

DT_NUMERIC is an exact number (DT_DECIMAL is too - but larger). DT_R4 and DT_R8 are the "float/single" and "double" representations. The scale and precision figures in the connection manager have nothing to do with implied decimal places. They tell SSIS what to shove the text data into. The point is that you can tell SSIS what specific data type to interpret the text file data as, rather than a 50 character string (the default). Specifying the type should save your bacon almost always - but not in the case of implied decimals, there's just no setting for that, you have to do that "one more" calc. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
November 24th, 2010 6:12pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics