Post reply

Data transfer with ODBC Link

  • June 12, 2007 at 8:36 am

    #68746

    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  

     

     

  • June 13, 2007 at 9:08 am

    #712647

    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.

  • June 14, 2007 at 2:53 am

    #712845

    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.


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • June 14, 2007 at 6:24 am

    #712899

    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 ....

     

     

  • June 19, 2007 at 2:38 am

    #713756

    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