You could use an ActiveX script to do something similar to the following:

Function Main()
IF ISDATE(DTSSource("DB2_Text_Field")) THEN
DTSDestination("SQL_Server_Date_Field") = DTSSource("DB2_Text_Field")
ELSE
DTSDestination("SQL_Server_Date_Field") = Now()
End If
Main = DTSTransformStat_OK
End Function

You'll probably have to use the FormatDateTime( <<Date>> , <<NamedFormat>> ) method for the DTSSource("DB2_Text_Field") to put it in the format you want in the SQL Server table.

You can substitute some other standard date for the ELSE clause so that you know exactly which records in the DB2 table didn't have a valid date that SQL Server recognizes.

Steve


-----Original Message-----
From: Schultz Christine [mailto:mssqldba-ezmlmshield-x10457861.[Email address protected]
Sent: Tuesday, May 30, 2006 3:35 PM
To: LazyDBA Discussion
Subject: RE: migrating data from db2 as400 to sql server 2000

Pedro,

You can do this using DTS. Established a connection to the DB2/400 using ODBC or OLE DB and setup the transformation from char to date (or if it's easier, do it in two steps ... bring it over in char form and then use a convert SQL transformation to convert it into a date). If it's not a valid date, you will have some issues that you will need to decide how to treat these.

Christine


-----Original Message-----
From: Pedro Zambrano Bojorquez [mailto:mssqldba-ezmlmshield-x76594356.[Email address protected]
Sent: Tuesday, May 30, 2006 1:29 PM
To: LazyDBA Discussion
Subject: migrating data from db2 as400 to sql server 2000

I need to mígrate some tables located in db2/400 to sql server 2000, but db2
not use fields date, use character to store a date, example:"200060430" in
db2, have idea how can I do this? I'ts possible to use dts services to
change this fields character to date format?





Regards

Pedro Zambrano Bojorquez





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page