SSIS Connection to ORACLE (Multiple-step OLE DB operation generated errors)

Question

I have an "Native OLE DB\Oracle Provider for OLEDB" connection in SSIS package to execute a procedure on ORACLE.

Procedure is working fine but in log file I am seeing below mentioned warning, which is slowing down the execution.

Warning : Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

The procedure is having 5 inputs and 2 out paras. After reading few articles which point says it could be due to data type/size mismatch.

I personally think its something to do with connection setting. I created a simple task of deleting data from a table without any para or variable. Still there was a warning

Its simple delete statement.

BEGIN DELETE FROM KC.KC_PAYMENT; END;

The warning appears after "validation is completed". and before the delete statement "Progress" event.

Deepak

I am having the same issue. I dont have any parameters to my sproc but I still get errors. This doesnt seem to be a data type issue. — rvphx
– rvphx, Commented Feb 7, 2014 at 18:29
Can you use an OLE source to a rowcount? Does preview data show anything. — Zane
– Zane, Commented Feb 7, 2014 at 19:18

Maverick · Accepted Answer · 2014-02-07 18:50:54Z

4

+50

There are many ways to work around this problem. What I am thinking as of now is

1) Store the Connection string that points to oracle into sql server table or in variable.

https://www.connectionstrings.com/oracle-provider-for-ole-db-oraoledb/

2) Check for security while fetching the records.

3) Use the "Oracle provider for OLE DB" from SSIS, don't use the "Microsoft Provider for Oracle" because a 64 bit version of it does not exist.

4) Schedule your packages with the SQLAgent.

answered Feb 7, 2014 at 18:50

Maverick

1,1851 gold badge8 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rvphx Over a year ago

I have tried all the options that you listed. But none of them seem to work. I am working in VS 2008 environment and have all the permissions to execute and connect to Oracle.

Maverick Over a year ago

Did you try changing your project to 32 bits?

rvphx Over a year ago

I cannot award the bounty right away. 23 hours to go.

Collectives™ on Stack Overflow

SSIS Connection to ORACLE (Multiple-step OLE DB operation generated errors)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related