Hello,

I want to put data from an Access-database into a SQL-server2005
database, using SSIS Data Flow. The problem I've got is that I cannot
convert a string value (like '20060101' for the first of januari 2006)
from the Access database into a Datetime value in the SQL-database. I
try to use a Data Conversion in SSIS, but none of the available
possibilities to convert the input string into a date are
successfull...

It does not support changing the datatype of the column, using the cast
functions does not work either. I also tried to convert using the
Derived Column Transormation, but that one also fails...

When I use SUBSTRING to get the parts of the date and then append them
together to mm/dd/yyyy then my string gets more than 8 characters and
SSIS fails because of that.

Can anyone please tell me how to do this conversion? It should be very
simple!

Thanks in advance,
Jeroen

Hello JB,

You could use a script component as a transform.


Or you could use a derived column transform with this as the expression where
dt == column

RIGHT(dt,2) + "/" + SUBSTRING(dt,5,2) + "/" + SUBSTRING(dt,1,4)

and you output to a datatype of database_timestamp





Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com