How to convert below SQL Expression into derived column in SSIS?

Convert CASE expression in SQL to derived column in SSIS

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

  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 http://msdn.microsoft.com/en-us/library/ms138047.aspx 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 http://msdn.microsoft.com/en-us/library/ms138047.aspx. (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.