Thank you both for your suggestions.

Luis, The pattern splitter is going to be very useful for me, thanks for introducing me to it, but in this case I dont think I can use it because even though in SQL it works great, I dont think will be able to convert it to an SSIS expression. I would probably have to convert the logic in the function to a script task which is beyond my expertise at the moment. I will definitely get some use out of this function in other places.

MM, this looks like an approach that I should be able to translate to an SSIS express(not sure if I can do anything with cross apply) and there is less logic to convert than my original.

Thanks again.

-edit: final SSIS expression using MM example

DATEDIFF("ss",(DT_DBTIMEstamp)"1899/12/30",(DT_DBTIMEstamp)(REPLACE(REPLACE(REPLACE((LEN(SessionDuration) < 8 ? "0 hrs 0 min " : LEN(SessionDuration) < 15 ? "0 hrs " : "") + SessionDuration," hrs ",":")," min ",":")," sec","")))