Date conversion in ssis

  • Charmer

    SSChampion

    Points: 14885

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148769

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

    John

  • Thom A

    SSC Guru

    Points: 98653

    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.

  • Charmer

    SSChampion

    Points: 14885

    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

  • Thom A

    SSC Guru

    Points: 98653

    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.

  • Luis Cazares

    SSC Guru

    Points: 183633

    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
  • Charmer

    SSChampion

    Points: 14885

    (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

  • Thom A

    SSC Guru

    Points: 98653

    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.

  • Luis Cazares

    SSC Guru

    Points: 183633

    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
  • corey lawson

    Hall of Fame

    Points: 3730

    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 10 (of 10 total)

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