Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

OraOLEDB

Eugen-OracleNov 27 2018 — edited Dec 13 2018

We are experiencing timeouts when transferring data from an Oracle database using SSIS packages into a SQL database.

What could be the case of the following message?

Message
Executed as user: HPC\SQLSrvDB03T. Microsoft (R) SQL Server Execute Package Utility  Version 13.0.5216.0 for 32-bit  Copyright (C) 2016 Microsoft. All rights reserved.    Started:  16:24:44  Error: 2018-11-19 16:25:02.73     Code: 0xC0209302     Source: BulkUpload_1 Connection manager "172.16.40.155/nreg_pri.lisa"     Description: The requested OLE DB provider OraOLEDB.Oracle.1 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  End Error  Error: 2018-11-19 16:25:02.78     Code: 0xC020801C     Source: Load APPLICATIONDETAILS OLE DB Source [345]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "172.16.40.155/nreg_pri.lisa" failed with error code 0xC0209302.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2018-11-19 16:25:02.82     Code: 0xC0047017     Source: Load APPLICATIONDETAILS SSIS.Pipeline     Description: OLE DB Source failed validation and returned error code 0xC020801C.  End Error  Error: 2018-11-19 16:25:02.82     Code: 0xC004700C     Source: Load APPLICATIONDETAILS SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2018-11-19 16:25:02.85     Code: 0xC0024107     Source: Load APPLICATIONDETAILS      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  16:24:44  Finished: 16:25:03  Elapsed:  18.453 seconds.  The package execution failed.  The step failed.

Comments

tlokweng
Answer

クエリトランスフォーメーションのビューマージで２階層のクエリが１階層に置換され、結果として各行でプロシジャが２回づつ呼ばれているということですね。なので単純にプロシジャを呼んでいるインラインビューがマージされないようにすれば解決します。素直にNO_MERGEヒントでOKでしょう。テーブルサイズが大きくなければWITHにしてファクタリングするのもアリですね。

> create table test_table(n) as select 1 from dual union select 2 from dual;

> create function test_func return varchar2 as begin RETURN DBMS_RANDOM.STRING('x', 10); end;

> select n, r r1, r r2 from (select n, test_func r from test_table);

        N R1          R2

--------- ------------ ------------

        1 AJ1LNZ30HD  LAMQV4CQUY

        2 F911DGQDYZ  ID8FDREGKF

> select n, r r1, r r2 from (select /*+ no_merge */ n, test_func r from test_table);

--------- ------------ ------------

        1 K5MPMUR3DQ  K5MPMUR3DQ

        2 DD2Y5NUWPM  DD2Y5NUWPM

> with t as (select /*+ materialize */ n, test_func r from test_table) select n, r r1, r r2 from t;

--------- ------------ ------------

        1 0F2RE7Y8C3  0F2RE7Y8C3

        2 O6C4WCBAFP  O6C4WCBAFP

Marked as Answer by 988825 · Sep 27 2020
988825

tlokweng様、お礼が遅くなってしまい、申し訳ありません。サンプルまでお示しくださいまして、誠にありがとうございます。

クエリトランスフォーメーションがこのようなSQLに対し作用しているとは全く一度も想像すらつきませんでした。

頂戴したサンプルで動作も確認出来ましたので、本来の組み込み先に入れてみようと思います。

ありがとうございました。

1 - 2

Post Details

Added on Nov 27 2018
#oracle-provider-for-ole-db, #windows-and-net
2 comments
3,269 views