SQL SSIS Convert question

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.

score 2 · Accepted Answer · answered Jan 19 '11 at 19:13

2

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

answered Jan 19 '11 at 19:13

bobs

21,844
12
67
78

I tried this but it still spit out the same way. I was thinking of this (DT_DECIMAL,2)Round((DT_Decimal,2)[Copy of Debit Amt] / (DT_DECIMAL,2)) would that work? – GabrielVa Jan 20 '11 at 21:08
@gabrielVa, what does the Derived Column transformation editor say is the output data type? And, yes, you can try your expression; it should work. – bobs Jan 20 '11 at 22:29
HI Bobs, the data type is decimal[DT_DECIMAL]. The expression is (DT_DECIMAL,3)[Copy of Debit Amt]. – GabrielVa Jan 21 '11 at 18:06

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

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.

edited May 23 '17 at 12:11

Community

1
1

answered Jan 19 '11 at 19:10

Registered User

8,357
8
49
65

SQL SSIS Convert question

2 Answers2

Linked