4

we had one SSIS package with Oracle 11 Client, we would run our daily query with 30min to 1 hour run time.

we had to upgrade our oracle clients as one of our other oracle source got upgraded.

post upgrade to Oracle 12c, our daily job run time increased. oracle DBA said, its not running in parallel, as its occupying only one processor.

when we run the same query from SQL Developer or toad, its running in parallel. but if we run from SSIS OLEDB Source component its not running in parallel.

I'm clue less with this behavior. any solution will be helpful. ask me more clarifications if required.

Hadi
  • 36,233
  • 13
  • 65
  • 124
user3292078
  • 41
  • 1
  • Why ignoring the provided answer if it solved the issue or you agree with you have to upvote it or accept it, else you should leave a comment to elaborate more with the answerer – Yahfoufi Mar 11 '19 at 11:42

1 Answers1

1

Trying to figure out the issue

I tried to search on this topic, i didn't find a lot of informations but i think it is based on the OLEDB Connection string provided in the OLEDB Connection Manager.

Check the following Oracle documentation it may give you some insights:

In the link above, in the Distributed Transactions part, they mentioned that:

The DistribTX attribute specifies whether sessions are enabled to enlist in distributed transactions. Valid values are 0 (disabled) and 1 (enabled). The default is 1 which indicates that sessions are enabled for distributed transaction enlistments.

Sessions enabled for distributed transaction enlistments cannot run statements that use the direct path load and parallel DML capabilities of the Oracle database. Such statements are executed as conventional path serial statements.

I am not sure if this could help, but it is not bad to give a try.

Oracle Attunity Connectors

Instead of using OLEDB Source to read from oracle, it is better to use Oracle Attunity Connectors for SSIS which guarantee higher performance than OLEDB Source:

Attunity's high speed connectors for Oracle and Teradata have been selected by Microsoft to be included with SQL Server Integration Services (SSIS).

Community
  • 1
  • 1
Hadi
  • 36,233
  • 13
  • 65
  • 124