Converting a date with a derived column

I'm having trouble converting a date in the format of:

Jan 11 2006 12:00:00:000AM

to a smalldatetime in my new SSIS package. I'm trying the derived column transformation, but I'm at a wall now, especially with the conversion of 'Jan' string to integer/month/value of 1. Anyone have experience/advice on this transformation?

What happened to the DateTime String transformation in SQL 2000? Was that too unpopular to move to integration services? That transformation saved my butt many times - every banks' data feed we import uses a different date format.

Thanks in advance for any advice/help!

-Erik

Have you attempted to chop the seconds and milliseconds off the date? And then stuff it into a smalldatetime column.|||

Seems like it does not like those 000 at the end.

The only way I see at this point - to cat the 000 out of it and them cast it with DT_DBDATE or something

|||I can use SUBSTRING to skip the time at the end, but what about converting Jan to the value 1?|||After you do SUBSTRING, use type casting to cast to needed date format. If this is not enough, you can use YEAR,MONTH abd DAY after you converted to ANY date format