Multiple inputs to MSOLEDB fails

[RodBarnes]

[If this is in the manual or in the help somewhere, I've missed it.]

I have a single MSOLEDB stage which has two input links from the same transformer stage. The two links target two separate tables and each uses the generate SQL option with read committed transaction isolation.

When I run the job, only the first link in order carries any data. The second link always shows zero records. If I switch the order of the links in the transformer (so the second link is now the first, the first is now second in order), then, again, only the first link outputs any data and the second shows zero records.

If I separate the links and direct them to separate MSOLEDB stages, both links output data.

I've used multiple output links on the same MSOLEDB stage without any problems.

What am I missing?

[RodBarnes]

[Oh sure, figure it out just after I post it. Oh well, I'll keep this here in case anyone else encounters this issue. Discovery below.]

Though the job didn't indicate any issues, the log showed an error for every insert attempt on the second input link. The error was "OLE DB provider error: Cannot start more transactions on this session." I was hoping that directing the links to the same stage would make them part of the same transaction. Doesn't seem to be the case. Seems that DataStage treats each link as a separate transaction.

[ray.wurlod]

In version 7.x only the ODBC stage has the ability to create one of these "grouped transactions".

[RodBarnes]

Thanks for the tip on the ODBC stage. I'll check that out as an alternative.

[chulett]

Hmmm... the OCI stage can, don't recall that as a feature of the ODBC stage.