CODEWITHSUNDEEP
oracle-databasessis
surendranath reddy
surendranath reddy

Reputation: 1

SSIS 2017 package successfully completed without all data at ole db oracle destination from ole dB oracle source

I have an SSIS package having ole dB source (oracle 11g) having tens of millions of records and the same data I am trying to insert into an oracle 12c ole dB destination with fast load option. Package is completed successfully with no errors but destination table is not having any data and package is showing around 4 million records has inserted and yellow spin is still spinning.

I am using sql server 2017 and windows server 2016 with visual studio 2017. one of my observations is package is tuning until the temp buffer is full and then it is not.

Upvotes: 0

Views: 156

Answers (1)

EdStevens
EdStevens

Reputation: 3872

The only thing I know about SSIS is that it is a pig when it comes to performance. It fetches each individual row, row-by-row, slow-by-slow, across the network. I was once presented with an issue where the dev was trying to use SSIS to load about a million rows from an oracle db to an mssql db. It was taking over four hours. Took me 30 minuets to get a good trace and observe the slow-by-slow activity, and another 15 for me to write a PL/SQL procedure in the source oracle dbd. That procedure ran the load in about 4 minutes --- vs over 4 hours for the SSIS.

To do this you first need to create a db link in the database that will drive the process. Here I will assume the source db.

CREATE DATABASE LINK tgt_db
   CONNECT TO hr IDENTIFIED BY password
   USING 'tgt_db';

The USING 'tgt_db' is telling it to use the tnsames entry 'tgt_db' to resolve the network connection to the target db.

Then it is as simple as

insert into target_table@tgt_db 
     (col1, 
      col2, 
      col3) 
values (select col1, 
               col2, 
               col3 
        from local_table
        );

Of course you can put that INSERT into a PL/SQL procedure that can also include any logic for deleting from the target etc, as well as then scheduling the procedure in dbms_scheduler. Or you apply the same principle with the target datbase pulling from the source.

Upvotes: 0

Related Questions