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.
