Post reply

Date conversion in ssis

  • April 13, 2017 at 5:03 am

    #320393

    Hi Team,

    I am trying to convert this date format "11-APR-2016 11:24:36.000000" in derived column in ssis but i am getting error while inserting into the table. 

    the above date is coming from Oracle database and target is MS SQL.

    Can any one help me please?

    Thanks,
    Charmer

  • April 13, 2017 at 5:10 am

    #1937996

    What error are you getting?  It might be easier to import the raw dates into a staging table and convert them from there.

    John

  • April 13, 2017 at 6:11 am

    #1938012

    When you say "trying to convert", what is it you're attempting to do to convert the number as well?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • April 13, 2017 at 6:32 am

    #1938019

    I am trying to convert into datetimestamp. I am able to get until first 3 digits of milliseconds but not the whole 6 digits. Actually the value is coming through a variable as varchar and trying to capture it in a derived column and trying to convert into datetimestamp since the target field is datetime.

    Thanks,
    Charmer

  • April 13, 2017 at 6:36 am

    #1938022

    Charmer - Thursday, April 13, 2017 6:32 AM

    I am trying to convert into datetimestamp. I am able to get until first 3 digits of milliseconds but not the whole 6 digits. Actually the value is coming through a variable as varchar and trying to capture it in a derived column and trying to convert into datetimestamp since the target field is datetime.

    Yes, but how are you converting it? What is your expression?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • April 13, 2017 at 7:07 am

    #1938030

    Charmer - Thursday, April 13, 2017 5:03 AM

    Hi Team,

    I am trying to convert this date format "11-APR-2016 11:24:36.000000" in derived column in ssis but i am getting error while inserting into the table. 

    the above date is coming from Oracle database and target is MS SQL.

    Can any one help me please?

    Have you tried converting the string to output as an ISO format?
    Something like TO_CHAR( OriginalValue, 'YYYY-MM-DDTHH:MI:SS')
    I don't remember the exact syntax, but hopefully, you'll get the idea.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 13, 2017 at 7:16 am

    #1938035

    (DB_DATETIMESTAMP) (@Var_DateTime) this was my first expression in derived column.
    then I tried splitted into date and time separately into two columns and then tried concatenating (DT_WSTR,25) (SUBSTRING(@var_datetime, 1, 11))
     (DT_WSTR,25)(SUBSTRING(FINDSTRING(@var_datetime, " ", 1), 17, ))

    Thanks,
    Charmer

  • April 13, 2017 at 7:30 am

    #1938040

    SSIS does no understand named months (or at least I'm pretty sure it doesn't, as I know I have conversion expressions to deal with it in some my my packages). So, when you have a date 2017-Apr-13... it sees the "apr" and basically throws a tantrum and falls over.

    One option, is, like Luis said, to use an ISO compliant format YYYY-MM-DDTHH:MI:SS. Otherwise you'll likely need to build an expression to convert your datetime to a compliant version, and then convert it.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • April 13, 2017 at 9:09 am

    #1938079

    Thom A - Thursday, April 13, 2017 7:30 AM

    SSIS does no understand named months (or at least I'm pretty sure it doesn't, as I know I have conversion expressions to deal with it in some my my packages). So, when you have a date 2017-Apr-13... it sees the "apr" and basically throws a tantrum and falls over.

    One option, is, like Luis said, to use an ISO compliant format YYYY-MM-DDTHH:MI:SS. Otherwise you'll likely need to build an expression to convert your datetime to a compliant version, and then convert it.

    Or even better (I'm not sure why I didin't mention it before) convert the value to a date data type in Oracle.
    If it's stored in Oracle as date, use that data type. If it's stored as varchar convert to date. Keep it in a date/time data type the whole process.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • June 16, 2020 at 7:43 pm

    #3763612

    SQL Server at some point (2012? 2014?) added the datetime2 to be more compatible with Oracle and DB2's date/time data types that can have more scale in the sub-second part of time values.

    in SSIS, I think you'd first target  casting the source value to DT_DBTIMESTAMP2 , and then convert it to DT_DBTIMESTAMP, to target a datetime field in SQL Server.

    Or, cast it from within the Oracle query to a string pattern SQL Server can implicitly convert.

    Or, other options, too, such as string manipulations.

     

     

Viewing 10 posts - 1 through 9 (of 9 total)

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