The problem you have ( and I assume your data is text in a text file ) is that datetime expects a certain format for the data.
If you were importing date data in format '2003-09-30 14:00:00.000'
( including the single quotes ) all would work well. I use UK date format. If you are in the US its '2003-30-09 14:00:00.000'
Date format in BOL is not documented well, sadly.
So, may need to alter your text data as part of the DTS package ( not easy, and time consuming), or alternatively , import the data straight into a new table using DTS ( easier ), then modify it to insert the " - " etc to make it the correct format then copy it into another table if needed.
Let me know if I have interpreted your problem correctly.
i would not alter the text file, rather, i would DTS it into a table where the datatype of the date fields is char(8) and char(6)
once you have the data loaded, you can then use SELECT INTO syntax to create your "final" table
e.g. if you've loaded yyyymmdd data into fieldx and yyyymm into fieldy, then you'd say
+'-'+substring(fieldx,7,2) as datetime ) as fieldxdate
, cast( left(fieldy,4)
+'-01' as datetime ) as fieldydate
the temporary table idea works for sure, I have implemented that before. Plus you can use the Date Time String conversion in the Transformation tab. Where the source would be in yyyyMMdd format and the destination would be any of your desired formats.