SSIS Ole DB Data source: literal does not match format string
Dear All, I'm using SSIS in SQL Server 2k8 R2 on Windows Server 2k8 R2. I have installed Oracle Client 10.2.0.2 32 bits and Oracle Client 10.2.0.4 64bits. I want to connect to Oracle 8i and Oracle 9i by these clients. Unfortunately when I want to see a preview, only error occurs to me: ORA-01861: literal does not match format string (OraOLEDB) I have read a lot about this issue but nothing helps me. I was trying to_date, convert, cast functions, but they didn't resolve this issue. This is query which I use: SELECT COMPANYID, TRANSACTIONTYPE, TRANSACTIONDATE, PRODUCT, PRODUCTIONSITE, BATCHNUMBER, BINNUMBER, QUANTITYSTOCKUNIT, QUANTITY, UNITOFMEASURE, TRANSACTIONCURRENCYID, COST FROM APPS.DW_PRODUCTION WHERE TRANSACTIONDATE >= '20050101' AND TRANSACTIONDATE < '99991231' It is vey simple query, but unfortunately I cannot find any solution to resolve the issue with this error. I have investigate, that a TRANSACTIONDATE is a column by which this error occurs. Data type of this column is VARCHAR2. SELECT TRANSACTIONDATE FROM APPS.DW_PRODUCTION Even this qyery above occurs "literal string" error. I'm thinking that, maybe this is the problem with Oracle Clients... I have also some problems with getting proper data type from the source, but this was issue related with working in 64 bits. If someone has got this problem and resolved it, please help me. PS. I want also inform You, that this project was created in SSIS 2005 on Win 2k3, and it has been worked, while moving it to a new server it has stopped work. PS 2. Sorry for my English. It is not my National language.
May 17th, 2011 2:56am

what happen if you don't write sql select query? I mean just select table name and go to preview. let us know the result. also try to use ODP.NET and let us know result, you can get it form here: http://www.oracle.com/technetwork/topics/dotnet/index-085163.htmlhttp://www.rad.pasfu.com
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 3:34am

Have a read of http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/7d8e5514-6411-4de7-b54e-d21b9b5309ff Be sure to read the whole thread as it starts off with a different error however turns onto a "literal string" error about half way down.Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
May 17th, 2011 4:08am

If I only select table, below error occurs to me: Error at Production Source -> Extract NORDIC [OLE DB Source [9086]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. Error at Production Source -> Extract NORDIC [OLE DB Source [9086]]: Opening a rowset for ""APPS"."DW_PRODUCTION"" failed. Check that the object exists in the database. Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap) I want to mention that on SSIS 2005 and Win 2k3 64 bit and Oracle client 9, everything work fine. After installation ODAC 10.2.0.2.1.1 and using OleDB provider still occured this error, but when I used ADO.NET connection and .net provider for OleDb appears to me error from my first post(issue with string format).
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 6:21am

did you tried the latest ODAC from the link I provided? maybe it solved the problemhttp://www.rad.pasfu.com
May 17th, 2011 6:36am

I have written something like that: SELECT COMPANYID, to_char(nvl(TRANSACTIONDATE,'20030101'),'YYYYMMDD') TRANSACTIONDATE, TRANSACTIONTYPE FROM APPS.DW_PRODUCTION and SELECT COMPANYID, to_char(ltrim(rtrim(nvl(TRANSACTIONDATE,'20030101'),'YYYYMMDD'))) TRANSACTIONDATE, TRANSACTIONTYPE FROM APPS.DW_PRODUCTION but the same error occured(literal does not match format string)...
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 7:12am

Unfortunately ODAC in version 11 does not support Oracle 8i database. The latest version which can I use is 10
May 17th, 2011 9:04am

I'm completely unfamiliar with Oracle - so take this observation with a large grain of salt. It seems to me that since the column is a string, and the error talks about a "literal not matching a format string" that you might be dealing with a constraint on the column. Specifically, the table structure may use a string as the base data type for the column, but it may have defined a constraint that restricts the contents of the column. For example, it may have defined a format of "####-##-##" on the column, requiring that you use dashes, and not just numbers. If that were the case, then it makes sense that the command is being rejected. The command would be rejected because the "literal" value you passed (20030101) doesn't match the requirements of the format string. Possible? Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 12:48pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics