CODEWITHSUNDEEP
sql-serverssis
Rob Tyketto
Rob Tyketto

Reputation: 33

SSIS - stored procedure, decimal(18,2) columns with values of 0.00, leading zeros being dropped to csv flatfile

I'm really struggling to get 0.00 values saved down to a .csv as 0.00 and not .00 (opened in Notepad++, not Excel issue).

Other posts recommended changing all the outputs to string, this did not help leading zeros are truncated.

Converting to currency via DT_CY (Doen this on mapping and derived column) oddly I just got 0 rather than .00 or 0.00, a colleague believe he fixed it with his conversion.

I've used derived columns to format dates, but not helping to get my leading zeros out.

An older stackflow post suggested this method in a new derived string column

[Price] < 1 ? ([Price] >= 0 ? "0" + (DT_WSTR,18)[Price] : [Price] > -1 ? "-0" + (DT_WSTR,18)(-1 * [Price]) : (DT_WSTR,18)[Price]) : (DT_WSTR,18)[Price]

When I put that in, it complains about the conversion between integer and string.

That post was from way back in 2010. I'm using VS 2019 and all my stored procedure datatypes are decimal(18,2).

I can't really change the stored procedure at this stage so looking for an SSIS solution.

Any tips would be appreciated, thanks.

When I add a dataviewer between Derived Column > Flat File Destination it shows the column I'm working with as 0.0000 (converted to dt_Cy) writes to file as 0. All other values like 7.56 appear as they should. Perplexing...

Upvotes: 0

Views: 924

Answers (1)

Rob Tyketto
Rob Tyketto

Reputation: 33

Resolved it, made sure the source columns were output as numeric(18,2) then derive column to string using the expression I posted in question, output as string and set to string in flat file column output details (albeit slow when to action if you have tons of columns).

All works and happy :)

Upvotes: 1

Related Questions