25

I have a flat file source that grabs a decimal value for the column Note Amount. The Source system specifies that this is a DECIMAL(12,2) file. I have the column in SSIS set to be DT_Decimal

File

The particular Flat file has a value of 122735.13 for one of it's amounts. However if I place a data viewer on the flow after the source it seems to be cutting the decimal off.

DataFlow

ResultsCut

Any idea why this is happening? For reference I'm going to a Decimal(12,2) SQL Server 2012 Database.

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
Zane
  • 4,129
  • 1
  • 21
  • 35

2 Answers2

31

Well it was pointed out that I needed to add the Scale that was missing.

Decimal

Or even better do as this suggests and switch to numeric where I can set precision and scale.

Numeric

hjpotter92
  • 78,589
  • 36
  • 144
  • 183
Zane
  • 4,129
  • 1
  • 21
  • 35
  • 1
    It may also be useful to know which data type in the Import Export Wizard matches which data type in SQL Server: [link](https://msdn.microsoft.com/en-us/library/ms141036.aspx#Mapping%20of%20Integration%20Services%20Data%20Types%20to%20Database%20Data%20Types) – sevenkul Feb 16 '17 at 20:39
  • 2
    In essence, per sevenkul's mapping link, DT_DECIMAL in SSIS is not equivalent to the DECIMAL(x,y) data type in SQL Server and DT_NUMERIC should be used instead for both decimal & numeric data types in SQL Server. – Leonard Sep 14 '17 at 22:30
  • That was doing my headache for last hour - thank you very much! – Daniel Hanczyc Jun 01 '19 at 22:23
-1

I ended up with reading it as text and alterering the table field to decimal before and after import. No other way let me keep the deciaml point in SSIS 2013.

Anonym
  • 11