Post reply

Convert Oracle unix time to SQL Server 2008 R2 using SSIS Data Conversion task

  • January 26, 2012 at 11:38 am

    #250211

    Using SSIS, I need to import an Oracle table directly into a SQL Server 2008 R2 table.

    - The Oracle table has a column Create_Date Number(15) stored as Unix time (seconds since midnight, 1/1/1970).

    - The SQL Server 2008 R2 column is defined as Create_Date (datetime,null)

    Can I use an SSIS Data Conversion task to convert the Unix date time? If so, what should I be seeting the following attributes to in the Data Conversion task pane:

    - Data Type (eg. Unicode string [DT_WSTR]

    - Length

    - Precision

    - Scale

    - CodePage

    Optionally - if I were to use a SQL Command in the Data Flow, what would the SELECT look like?

    BT

  • January 26, 2012 at 11:55 am

    #1438976

    Express12 (1/26/2012)

    - The Oracle table has a column Create_Date Number(15) stored as Unix time (seconds since midnight, 1/1/1970).

    select DATEADD(second, @ORA_Create_Date, {d '1970-01-01})

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • January 26, 2012 at 12:12 pm

    #1438990

    thanks Paul..

    Is this also doable via the SSIS 2008 R2 data conversion task.

    Would be great to use that task.. in place of the SQL Command w/ SELECT SCRIPT..

    BT

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply