Sorry to revive an old thread but I'm having the exact same problem and didn't see that a final 'Hey - this worked!' was posted.

I double-checked all my data and it's all in YYYYMMDD format.

I have the source set as a string.

I've tried breaking it out into MM-DD-YYYY and other permutatations.

In no way can I get this value to convert to a datetime.

One oddity here. I have the source set as a non-unicode string. When I tried to pull the year from the field I received an error that the YEAR function does not support data type of "DT_WSTR" and the source truly is set to be DT_STR.

Thoughts?