A question popped up not so long ago on the MSDN SSIS Forum from somebody struggling with a leading zero being dropped from the data being exported to a flat file to be returned back. The person asking was also interested in trimming (not rounding) the values to a certain scale.
As an aside, this issue occurs when you have decimal or floating point numbers in a database and its value is between 0 and 1 e.g. 0.445 or a negative one e.g. -0.123 which you want to export to a flat file as CSV being used as an example here.
Frankly, I have seen a lot of questions about this and the remedy seemed to be always in converting the incoming data to NUMERIC, DT_NUMERIC if to be SSIS-precise. So the expression would be like follows:
[Number] < 1 ? "0" + (DT_WSTR,20)([Number]) : (DT_WSTR,20)([Number])
Alas, this, and none of the workarounds I could find so far covered the situation when a number in the source is negative.
In other words trying to run a package on a value of -0.114 results in 0-.114 passed into the flat file, this is not good!
Then how to properly handle all the situations?
Well, let's start with defining our test data:
As you can see we have covered all the possible ranges that may occur. Please note that in my test table the flNumber column is defined as Float, but it may very well be a decimal.
Now, as we all know, the workaround is in converting this column value to DT_NUMERIC, let's do that by adding a Data Conversion Transformation:
Please note two things here:
- We used a Copy Column to make it numeric, and
- Specified scale of 2 to trim (not round) any extra figures thereafter.
If we leave the package as is at this stage and you would run it, the leading zeros would not appear for those values on rows 1, 4 and 5 inside the flat file (a complete package layout to follow).
All is standard at this point, thus let's move to using the expression that adds the leading zero. We will do that by dragging and dropping a Derived Column Transformation to our DFT.
An expression in it I used covers the range between 0 and +1:
[Copy of flNumber] < 1 && [Copy of flNumber] >= 0 ? "0" + (DT_STR,18,1252)([Copy of flNumber]) : (DT_STR,18,1252)([Copy of flNumber])
As you can see we added a new column this time. But you may ask then what about the negative values?
And I can tell you this is a legitimate question. The answer is because you can only have one expression per column in the Derived Column Transformation we will need to add another one!
Its expression is
(DT_NUMERIC,18,2)[ModifiedFloat] < 0 && [ModifiedFloat] > -1 ? "-0" + REPLACE((ModifiedFloat),"-","") : (ModifiedFloat)
The above expression is more complex but covers the negative values.
Let me tell you at this point how the logic works if you have not figured it out yet:
If we get a positive number with a leading zero (0 inclided to +1) then we artificially insert in front of our number, and convert to string.
Then we proceed with another evaluation. At this time we check if the number is negative (-1 to 0), if yes, we append -0 and remove the original minus (-) sign.
The last step is adding the Flat File Destination which I guess is trivial and should not be discussed here, but I will mention that you can define your FlatFile Connection as you want, in my case, since a flat file is really data type agnostic, I used Unicode String as the data type for all my output columns. Below is a snapshot of the entire package:
Running the package produces this output:
The end!