SSIS is not based on T-SQL, so you can't use T-SQL staments in the expression editor.

I think to acheive the result you want, you will have to break apart the source date (YYYYMMDD) and contruct the date yourself

(you'll have to check the syntax on this, but hopely you'll get the gist of what its doing)

SUBSTRING(0,4, {sourceDate} ) + '-' + SUBSTRING(5,2, {sourceDate} ) + '-' + SUBSTRING(7,2, {sourceDate} ) + ' 00:00:00'

output that to a DT_DBTIMESTAMP data type.

let me know if that works