Data transfer with ODBC Link
-
Hi
I am trying to transfer data with DTS Package using ODBC Link from other server. I have Date field which has data type datetime but when i run my dts pacakage it always failed because of some non-date type data in invoice date . I am using allow null condition also which is not working.What should i need to do?
Thanks
-
Can you change the datetime field to character field before the transfer? Each database (for example Oracle and SQL Server) has its logic to process the date, that's why it is not working.
-
If you just wanted to default any row to NULL or A.N.other date you could use an ActiveX script to copy column in your datapump task. The below will default to NULL:
Function Main()
If ISDATE(DTSSource("Source")) Then
DTSDestination("Destination") = DTSSource("Source")
Else
DTSDestination("Destination") = NULL
End If
Main = DTSTransformStat_OK
End Function
You could add in extra checks and functionality to format any incorrectly formatted date strings from other systems etc.
-
Thanks for reply
Yes i have done that. I have changed smalldatetime field to char but out put i got is '0007-05-27'
so thinking not a good idea.Now trying this activex script ....
-
I'm using this sql query to select the field which I want, if it is a datetime field I use to_char.
SELECT "T$ORNO", TO_CHAR("T$TRDT", 'mm-dd-yyyy') AS "T$TRDT"
After this in the transformation I use an activex script and for datetime fields I use this :
If IsDate(DTSSource("T$TRDT")) Then
If DateValue(DTSSource("T$TRDT")) < DateValue("01-01-1753") Then
DTSDestination("deliverydate") = DateValue("01-01-1753")
Else
DTSDestination("deliverydate") = DTSSource("T$TRDT")
End If
Else
DTSDestination("deliverydate") = NULL
End If
Hope you can use this !
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply