SSIS 2016 query to oracle failed

Question

1.00/5 (1 vote)

See more:

Hi,
Switching from MSDAORA1 to OraOLEDB.Oracle for migrating MS SQL 2012 SSIS to 2016. Field is defined as VARCHAR2 on Oracle but has date value. SQL query returns an error:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E14 Description: "ORA-01861: literal does not match format string".
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01861: literal does not match format string".

What I have tried:

Tried TO_CHAR and TO_DATE but failed with same error. Is there a way to resolve the issue?

Posted 5-Apr-22 10:18am

Chris sc Lee

Add a Solution

Comments

Dave Kreskowiak 5-Apr-22 18:25pm

"Field is defined as VARCHAR2 on Oracle but has date value"
Well, there's a huge mistake in the original database. That NEVER should have been defined as a string.

TO_DATE is the way to go, but since you haven't shown the TO_DATE function you used and a sample of the strings it needs to convert, there's little anyone can say.

Maciej Los 6-Apr-22 1:47am

We need details to be able to help you. What is input date format? What have you tried so far (complete example)?

Chris sc Lee 6-Apr-22 17:37pm

Tried with following but still no luck:
TO_CHAR(START_DATE) AS TO_CHAR_START_DATE,
TO_CHAR(END_DATE) AS TO_CHAR_END_DATE,
TO_DATE(START_DATE, 'YYYY-MM-DD') AS TO_DATE_START_DATE,
TO_DATE(END_DATE, 'YYYY-MM-DD') AS TO_DATE_END_DATE,

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)