Convert CASE expression in SQL to derived column i

Question

CASE 
        WHEN CHARINDEX('%', '{FixedARMRateReductionLimit}') > 0 THEN
            CAST(SUBSTRING('{FixedARMRateReductionLimit}', 0, 
               CHARINDEX('%', '{FixedARMRateReductionLimit}')) as decimal)/100
        WHEN '{FixedARMRateReductionLimit}' = 'Weekly PMMS Rate' THEN
            PARAM_VAL_TXT
        ELSE
            .02
    END

Answer 1

The moral of the following story is that just because you can do something in SSIS, it's not always a good idea.

Case in point, this query. It would be far more efficient to use the existing sql logic to generate the final value than using derived columns or a script task in SSIS (not to mention the waste of pipeline memory, CPU, etc)

Source query

I used the following as a source query.

SELECT '50%' AS FixedARMRateReductionLimit, .1 AS PARAM_VAL_TXT
UNION ALL  SELECT 'Weekly PMMS Rate' AS FixedARMRateReductionLimit, .3 AS PARAM_VAL_TXT
UNION ALL  SELECT 'Frack',  .5

Find Percent Position

Determine whether a percentage symbol exists in the column. This creates an column called PercentPosition

FINDSTRING(FixedARMRateReductionLimit, "%",1)

Check for rate text

It should be sufficient to do a simple comparison as the first expression shows but I was having issues with it. I assume it's a string conversion/comparison issue (see first Note). Rather than diddle with getting a boolean value, I used findstring to generate the ordinal position.

FixedARMRateReductionLimit == "'Weekly PMMS Rate"
FINDSTRING(FixedARMRateReductionLimit,"Weekly PMMS Rate",1)

Derive Output

Enjoy the double usage of the Ternary operator.

(RateTextPosition > 0) ? (PARAM_VAL_TXT) : (PercentPosition == 0) ? .2 : ((DT_NUMERIC, 18,2) SUBSTRING(FixedARMRateReductionLimit,1,PercentPosition - 1))/100

You could have simplified some of this in a script task but I'd just do the logic in the source.

Answer 2

This is just to show how the expression should be constructed in the derived transformation. I wouldn't suggest doing it this way since it is very hard to maintain.

Refer @billinkc answer to this question Convert CASE expression in SQL to derived column in SSIS

Here is how the expression should be written in Derived transformation.

(FINDSTRING(FixedARMRateReductionLimit,"%",1) > 1) ? ((DT_NUMERIC,5,2)SUBSTRING(FixedARMRateReductionLimit,1,FINDSTRING(FixedARMRateReductionLimit,"%",1) - 1)) : (FixedARMRateReductionLimit == "Weekly PMMS Rate" ? (0.35) : (0.02))

Formatted version of the expression.

(FINDSTRING(FixedARMRateReductionLimit,"%",1) > 1) 
    ?   (
            (DT_NUMERIC,5,2) 
                SUBSTRING(  FixedARMRateReductionLimit, 
                            1, 
                            FINDSTRING(FixedARMRateReductionLimit, "%", 1) - 1
                          )
        ) 
    :   FixedARMRateReductionLimit == "Weekly PMMS Rate" 
            ? (0.35) 
            : (0.02))

Sample data used:

SELECT c1 AS FixedARMRateReductionLimit
FROM
(
            SELECT '2.00%'              AS c1 
    UNION   SELECT '13.95%'             AS c1 
    UNION   SELECT '%52.00%'            AS c1 
    UNION   SELECT '%%'                 AS c1 
    UNION   SELECT '85.%42%'            AS c1 
    UNION   SELECT 'Weekly PMMS Rate'   AS c1 
    UNION   SELECT 'Monthly PMMS Rate' 
) T1

Output of derived transformation when viewed in data viewer: