Date conversion in ssis

Question

Date conversion in ssis

Charmer

SSChampion

Points: 14903
More actions
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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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: 99129 More actions · Answer 2

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

Charmer SSChampion Points: 14903 More actions · Answer 3

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: 99129 More actions · Answer 4

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

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: 14903 More actions · Answer 6

(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: 99129 More actions · Answer 7

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

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: 3773 More actions · Answer 9

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.