Hello everyone,

In a DTS package I have a datapump that results in a Time way : 7 minutes
to transfer 957 rows !
(about 30 fields, varchars and decimals fields, nothing specially heavy).


The source is a quite simple SELECT on several tables with inner joins
against an Oracle Database (through OLE DB),
the destination is a unique table with no index, no trigger.
No activeX transformations ...
I tried to increase the fetch buffer .... with no effect ...

The source query runs in 2 secs without the transfer

May I missed something ?
Thanks in advance for your help.
Stéphane.

Stephane,

Can you change data pump to execute sql task with insert/select?

Ilya

"stephane" <stephaned (AT) softhome (DOT) net> wrote

Hi Ilya,

I tried to put the Select statement in an ExecuteSQL task,
but it seems, that ExecuteSQL doesn't wait the results to fire Success ....
but just syntax validation.(Am I wrong ?)
I currently investigate on the Oracle query's joins that seem to not to be
as simple as it was at first look ....

thanks,
Stéphane

"Ilya Margolin" <ilya_no_spam_ (AT) unapen (DOT) com> a écrit dans le message de
news:%239$sK7VeEHA.2852 (AT) tk2msftngp13 (DOT) phx.gbl...

Stephane,

ExecuteSQL task waits till the statement is done running. I was suggesting
something like this to have ExecuteSQL task run:

insert into <your destination table> (<column list>)
select <output list>
from <your Oracle tables>
where..........
group by ........
having..........

, not just select part.

Ilya

"stephane" <stephaned (AT) softhome (DOT) net> wrote

Hello Ilya,

Sorry, I didn't catch you .. I didn't know that it was a possible way of
operations.
But, how to specify the SQLSrv destination table (as the connection for
ExecuteSQL should be the Oracle one) ?

I tried with

INSERT INTO SQLSERVERNAME.DATABASENAME.OWNER.TABLENAME(column1 , ...,
column-n)
SELECT (column1 , ..., column-n) FROM OracleTable

and a Unexpected Error raise. Will I need a "temp table" on the Oracle Side
?

Anyway, a join was the reason for the lag ... so I decided to handle it with
two steps, the join is done through SQLSrv Temp table, and it seems ok now
(about 1 minute to Oracle Req1, Oracle Req2 and Join and tranfer in SQL).


"Ilya Margolin" <ilya_no_spam_ (AT) unapen (DOT) com> a écrit dans le message de
news:emTOD5YeEHA.4092 (AT) TK2MSFTNGP10 (DOT) phx.gbl...

Stephane,

To specify an Oracle connection you can create a linked server or use one of
OPENROWSET, OPENQUERY, OPENDATASOURCE. Read BOL on the topics.

Ilya

"stephane" <stephaned (AT) softhome (DOT) net> wrote