SQL SSIS Convert question

问题:

I have an excel file I'm converting to a prn in an SSIS package. My issue. One of my columns has currency, everything should have a decimal point but not all the numbers do. For example if there are no cents it simply reads 10425. What I need to happen in SSIS is to make is read 10425.00 just as the other numbers in the file already do.

Please help.

回答1:

You can use the Derived Column transformation to convert the data type. I don't think think the Currency (DT_CY) data type will force the decimal places for you, but you can use the Decimal (DT_DECIMAL) data type.

(DT_DECIMAL,2)MyMoneyColumn

回答2:

SSIS is relying on a registry key to determine how many rows to sample. If you are using default settings, then it may only be checking the first 8 rows. You can increase the number of rows it uses by modifying the TypeGuessRows key in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel. You can read about this here, here, here, and here.