Flat File Sources and the Decimal Data Type
Question: What Data Type should you use for importing a column in Flat File containing Decimal data?
Answer: numeric [DT_NUMERIC]
You cannot use the decimal [DT_DECIMAL] type, because in the Advanced Editor of the Flat File connection, the decimal type for some reason only allows you to set the Scale (the number of digits after the decimal point) – the Precision is greyed out (precision is the total number of digits). The numeric data type allows the setting of both values.
Fortunately the SSIS numeric type maps to SQL Server decimal columns without complaint, so you don’t have to add a Data Conversion to change numeric to decimal before using it. I have raised a bug on Connect – please vote it up if you consider this worth fixing.