Problem importing date from string in flat file into SQL Server 2005 table using SSIS

Hi

I've got a really irritating problem that I just can't seem to trace.  I'm new to SSIS, as we've just moved from SQL 2000 to 2005.

I have a pipe-deliminated flat file I want to import into a table in SQL Server 2005,  It's mostly a straight import, but there are two date files I need to convert to DateTime.  The fields have a simple yyyymmddhhmmss layout (i.e. 20080201153000 is 3:30pm on 1st Jan 2007).  The seconds are always blank so they can be left out if it helps.  I've set up an import and output and data converter, which is trying to convert the string to a DateTimeStamp.  I've also put in a calculated column to try changing the structure of the strings about before the conversion, but it doesn't seem to matter what layout I put it in I still get the same error:

Status value 2.  The value could not be converted because of a potential loss of data

Is there some way I can set the structure of a date string for a specific SSIS package? (I can't change the locale settings etc on the database).  Alternatively is there any layout of string that will import whatever the locale settings?  I've tried yyyymmdd hh:mm:ss and yyyyddmmhhmmss and yyyy/mm/dd hh:mm:ss.

Thanks
LauraStart Free Trial