Geeks With Blogs

Arthur Zubarev Compudicted

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:

Incoming 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:

Data Conversion Transformation

 

 

 

Please note two things here:

  1. We used a Copy Column to make it numeric, and
  2. 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:

Output

The end!

Posted on Monday, March 28, 2011 3:21 PM | Back to top


Comments on this post: How to Import Negative Decimal or Floating Values to Flat File Without Losing Leading Zero

# Just Regarding Contribution
Requesting Gravatar...
Hi Dear
Just Wanted to ask you something
I have also satred blogging recently
Would you like to join with me to creat world of BI
Together we can do lot of things
Reply me ASAP
Left by Vishal Pawar on May 18, 2011 4:15 AM

# re: How to Import Negative Decimal or Floating Values to Flat File Without Losing Leading Zero
Requesting Gravatar...
Very good post, thank you.

Just one thing, here:

"Its expression is

(DT_NUMERIC,18,2)[ModifiedFloat] < 0 && [ModifiedFloat] > -1 ? "-0" + REPLACE((ModifiedFloat),"-","") : (ModifiedFloat)"

you missed the second conversion:

"Its expression is

(DT_NUMERIC,18,2)[ModifiedFloat] < 0 && (DT_NUMERIC,18,2)[ModifiedFloat] > -1 ? "-0" + REPLACE((ModifiedFloat),"-","") : (ModifiedFloat)"

Thanks a lot.
Left by Marco Tulio on Oct 27, 2011 1:27 PM

# re: How to Import Negative Decimal or Floating Values to Flat File Without Losing Leading Zero
Requesting Gravatar...
I was able to do this in a single derived column - by converting the field to dt_str then simply replacing "." with "0." if the value was between -1 and 1. Here's the expression I used (also had to right pad for a fixed width output file):
RIGHT(" " + ((Amount > -1 && Amount < 1) ? REPLACE((DT_STR,14,1252)Amount,".","0.") : (DT_STR,14,1252)Amount),14)
Left by PS on Dec 05, 2012 10:14 AM

# re: How to Import Negative Decimal or Floating Values to Flat File Without Losing Leading Zero
Requesting Gravatar...
thanks a lot
Left by Marina on May 23, 2013 3:11 AM

# re: How to Import Negative Decimal or Floating Values to Flat File Without Losing Leading Zero
Requesting Gravatar...
This could be acheived in one derived column expression.
The below expression preserves leading zero if number is between -1 and 1.

Effect_Upon_BPR < 1 && Effect_Upon_BPR >=0 ? "0" + (DT_WSTR,6)Effect_Upon_BPR : Effect_Upon_BPR < 0 && Effect_Upon_BPR > -1 ? (DT_WSTR,6) REPLACE((DT_WSTR,6)Effect_Upon_BPR,"-","-0") : (DT_WSTR,6) (Effect_Upon_BPR)
Left by Nisha on Dec 06, 2013 2:25 PM

# re: How to Import Negative Decimal or Floating Values to Flat File Without Losing Leading Zero
Requesting Gravatar...
I was able to write a single derived column expression.Please try this one.

Purchase_Qty < 1 && Purchase_Qty >= 0 ? "0" + (DT_WSTR,6)Purchase_Qty : Purchase_Qty < 0 && Purchase_Qty > -1 ? (DT_WSTR,6)REPLACE((DT_WSTR,6)Purchase_Qty,"-","-0") : (DT_WSTR,10)(Purchase_Qty)

-Nisha V Krishnan
Left by Nisha on Dec 06, 2013 2:44 PM

# re: How to Import Negative Decimal or Floating Values to Flat File Without Losing Leading Zero
Requesting Gravatar...

Purchase_Qty < 1 && Purchase_Qty >= 0

means 0 only works

say 27 does not

you cannot have a real number less than 1 and greater than 0 at the same time
Left by Arthur on Dec 06, 2013 2:51 PM

# re: How to Import Negative Decimal or Floating Values to Flat File Without Losing Leading Zero
Requesting Gravatar...
just another major fail from ms
Left by hitcher on Jul 15, 2014 8:41 AM

# re: How to Import Negative Decimal or Floating Values to Flat File Without Losing Leading Zero
Requesting Gravatar...
Purchase_Qty < 1 && Purchase_Qty >= 0

Why does 27 may not work??


Any number which is less than 1 and greater than or equal to zero works. (=0) means 0 is also taken into account.
Left by Nisha V Krishnan on Oct 07, 2014 4:02 PM

# re: How to Import Negative Decimal or Floating Values to Flat File Without Losing Leading Zero
Requesting Gravatar...
Hi Nisha V Krishnan,

Let's test:

27 < 1 && 27 >= 0 as you may see

the left part breakes the logical conjunction ( && ).
Left by Arthur on Oct 07, 2014 10:02 PM

# re: How to Import Negative Decimal or Floating Values to Flat File Without Losing Leading Zero
Requesting Gravatar...
Artigo muito útil. Consegui resolver o meu problema com a sua ajuda. Obrigado.
Left by Daniel Viana on Feb 06, 2015 7:05 AM

Your comment:
 (will show your gravatar)


Copyright © Compudicted | Powered by: GeeksWithBlogs.net