SSIS SQL get Oracle date value defined as varchar2

Question

Hi,
Having Windows, SQL 2016 and VSDT 2017. Have date (yyyy-mm-dd) value but defined as VARCHAR2(10), SQL statement to retrieve the field, set input/out properties to [DT_WSTR] and got following error:

[OLE DB Source] 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".

when tried to convert the filed with TO_DATE(START_DATE, 'YYYY-MM-DD) AS START_DATE:
[OLE DB Source] 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".
[SSIS.Pipeline] Error: OLE DB Source failed the pre-execute phase and returned error code 0xC0202009.

Is there a way to get around? Thanks.

Answer 1

Hi @Christopher Lee ，

I'm completely unfamiliar with Oracle.

ORA-01861: literal does not match format string  

Make sure that the literal that you provide matches the format string that you've specified.

Could you please use the TO_CHAR(START_DATE, 'YYYY-MM-DD) AS START_DATE for a try?

Also please help check if you use the TO_DATE or TO_CHAR in Oracle database, will the error thrown?

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks for the help. Tried the sql in Oracle SQLDeveloper:

   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,  

Attached the result. SSIS still show same error. Just weird, there are other fields in query have same VARCHAR2 but did not cause any issue. Only date value in string caused the issue.