CODEWITHSUNDEEP
ssiscastingetlderived-columndataflowtask
AnS
AnS

Reputation: 1

What is wrong in this statement for SSIS derived column transformation?

I am performing some transformation to create a derived column using SSIS derived column transformation task This column "Column" to be rounded 2 decimal and put space in front. I am using this statement: RIGHT("       "+(DT_WSTR,9)(DT_NUMERIC,7,2)ROUND(Column,2),10)

Data type for Column is unicode string DT_WSTR with length 10.

But it is giving me error : Error at Data Flow Task 1 [Derived Column [2]]: The function "ROUND" does not support the data type "DT_WSTR" for parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to be explicitly cast with a cast operator.

Please help what is wrong and how to cast it?

I tried with above statement but giving me error.

Upvotes: 0

Views: 543

Answers (2)

KeithL
KeithL

Reputation: 5594

you can do this easily with a script transformation (this assumes the values can be parsed).

//Added MidPoint Rounding to always round up instead of toward the even number
var step1 = Math.Round(Decimal.Parse(numberAsString), 2, MidpointRounding.AwayFromZero).ToString("F2"); 

var final = new string(' ', 10 - step1.Length) + step1;
  1. Replace numberAsString with your column
  2. Replace var final with your new OutPut column.

Upvotes: 0

billinkc
billinkc

Reputation: 61259

Order of operations is the problem.

Column is DT_WSTR of length 10

The first operation you are performing is a ROUND. As the fine error says

The function "ROUND" does not support the data type "DT_WSTR" for parameter number 1

Therefore, you need to convert to a numeric data type before you attempt to round

(DT_NUMERIC,7,2)Column

Assuming it converts gracefully, the current operand is a floating point value which is perfect for your ROUND call

ROUND((DT_NUMERIC,7,2)Column, 2)

To get that leading space in there, then you'll need to convert back to a string type. At this point, I need you to understand that the first number in the cast to numeric is the scale or "how many total digits" The choice of 7 with a precision of 2 means the total printed length is 8 characters. In your original formula, you mention (DT_WSTR,9) which would be for languages with implicit decimal places but is more than you need based the preceding cast/round operations.

(DT_WSTR, 9)ROUND((DT_NUMERIC,7,2)Column, 2)

At this point, you want to left pad the value with spaces. I am going to use the REPLICATE function instead of a bunch of spaces. It's the same net result but I find it's easier to twiddle with if needed.

REPLICATE(" ", 10) + (DT_WSTR, 9)ROUND((DT_NUMERIC,7,2)Column, 2)

Finally, you correctly use the RIGHT operand to slice out the last 10 characters of the resulting value.

RIGHT(REPLICATE(" ", 10) + (DT_WSTR, 9)ROUND((DT_NUMERIC,7,2)Column, 2), 10)

If at any point along the way, the Expression language gets bothered by a data type conversion, I find more parenthesis help guide the order of operations but my mental parser says the above is what you want.

Final notes on doing all this --- that's a really long expression. If any piece of it fails, you can't diagnose what's wrong. I advocate using lots of Derived Column transformation in my data flows when I talk to people about good designs for SSIS. The cost of inflating the size of your data buffer is generally inconsequential to the pain of trying to debug what has happened. You don't have to land all the columns in your destination but when unexpected data happens, you can drop data viewers or data taps between the various Derived Columns and identify the culprit so much easier.

Upvotes: 1

Related Questions