DTS DB2 Dates 0001-01-01

Hi,

I am trying to transfer a table from DB2 to SQLServer 2000 through a DTS package. The DB2 table contains fields with default dates of "0001-01-01". The DTS package errors out whenever it reads this date as "invalid data value". In SQLServer 2000, the date fields are of type ShortDateTime. I have searched the Internet but did not find a workable solution. Please, can anyone help me find a solution?

Older applications sometimes used dates like '9999-12-31' or '0001-01-01' to signify that the date was either NULL, invalid or not-entered. If DB2 is using that date as a valid point in time then the following would not work. However if it is a sentinal value used to represent NULL then you could transform such dates into NULL on-the-fly as you extract them from DB2; in pseudocode:

SELECT

NullIf(theDate, '0001-01-01') as theDate, <other fields>

FROM theTable

(You would need to find out the equivalent function for NullIf in DB2 parlance) This would have the effect of leaving all dates alone except for '0001-01-01' which would be translated to NULL, which would keep SSIS happy.

If you translated it to NULL then you would have to be careful that you did not break other business rules.

|||Thanks but I don't have any control over the creation of DB2 tables hence I was looking for DTS to do the trick. Can DTS handle this?|||In the source component of your DTS package, use the SELECT statement and conversion functions as detailed in my previous reply.|||

Thanks.

I am rather new to DTS and am trying to get an example of creating a package using vbscript. I have looked at Books on line but the examples are a little confusing for me right now. Do you by chance have any sample code that shows how to do something similar like this ?

Appreciate your help.