CODEWITHSUNDEEP
sqlssisformattingetl
ANASI-Newbie
ANASI-Newbie

Reputation: 111

Convert number format in SSIS

Need some help please .. I have excel data that is formatted. Example 33.257615111 shows as 33.258 (thirty three million two hundred and fifty eight thousand)

I am trying to import data file into SQL using SSIS ETL. Is there a way I can convert this during ETL process as a number with 2 decimal places? i.e. 33257615.11

Thank you

Upvotes: 2

Views: 1791

Answers (1)

Troy Witthoeft
Troy Witthoeft

Reputation: 2676

This can be done. Use a Derived Column and the ROUND() expression.

Inside your Dataflow, between the Excel File Source component and the OLE DB Destination component, add a Derived Column component. Inside that Derived Column component create a new column that rounds the value.

(DT_NUMERIC,25,2)ROUND(MyColumnName, 2)

Map this new Derived Column above to your SQL destination and we are all set.

But! Are you SURE you want to store rounded numbers in your database?

Will you ever need to sum this column? If so, the rounded values are going to yield a very different total than the non-rounded values. Maybe, don't do this rounding in SSIS and instead store the full numbers. Let applications reading this data do the formatting. It's just as easy.

SELECT ROUND(MyColumnName,2) AS MyColumnName
FROM MyTable

In multitier architecture, the formatting of numbers should be left up to the display layer, not the data layer. This is exactly what Excel is doing. It's showing you a rounded number, but behind the scenes is the full value. Don't drop precision for the sake of formatting.

Upvotes: 1

Related Questions