CODEWITHSUNDEEP
sql-serverssisetl
user3482527
user3482527

Reputation: 247

SSIS importing extra decimal values in to destination sql table from excel

I am trying to import "Financial data" from Excel files in to sql table. Problem I am facing is that My ssis package is incrementing decimal values. e.g -(175.20) from Excel is being loaded as "-175.20000000000005" in SQL. I am using nVArChar (20) in destination SQL table. Images attached. What's the best data type in destination table. I have done a lot of reading and people seem to suggest decimal data but Package throws error for Decimal data type.Need help please.Sql Extract Source Excel File

Upvotes: 3

Views: 1520

Answers (2)

user3482527
user3482527

Reputation: 247

Ended up changing the Data type to "Currency" in my SQL destination. Then added a data conversion task to change "DT_R8" data type from excel source to "currency[DT_CY]. This resolved the issue. could have used decimal or Numeric (16,2)data type in my destination as well but then i just went ahead with currency and it worked.

Upvotes: 2

Chris Mack
Chris Mack

Reputation: 5208

You could use a Derived Column Transformation in your Data Flow Task, with an expression like ROUND([GM],2) (you might need to replace GM with whatever your actual column name is).

You can then go to the Advanced Editor of the Derived Column Transformation and set the data type to decimal with a Scale of 2 on the 'Input and Output Properties' tab (look under 'Derived Column Output').

You'll then be able to use a decimal data type in your SQL Server table.

Upvotes: 0

Related Questions