0

We have N tables on Oracle server and we wanted to load all those tables from Oracle to SQL server. We are creating dynamic SSIS packages for same which will take the Oracle ServerName, DB name, schema name, tables list etc. and will load all these tables to SQL server. We have added Link Server on SQL Server (SSMS) for Oracle.

But we are not getting the efficient way to do the same. How we can achieve this in a single SSIS package. How we can handle metadata of Oracle tables and creating the same on SQL server ? This SSIS package should create tables dynamically on SQL server as well , for this we tried Temp table in SSIS package.

Ajit Medhekar
  • 1,018
  • 1
  • 10
  • 39
  • Maybe [Oracle linked server shows all tables from all schemas in sql server management studio](https://dba.stackexchange.com/q/229416/167152) can get you started on retrieving the schema information from a linked server. – Filburt Sep 08 '20 at 13:17
  • My experience is SSIS is inherently inefficient at copying from oracle to mssql. I was once presented one that was taking over 4 hours to copy just under 1 million rows. I put an oracle trace on the process and found it was working row-by-row, 'slow-by-slow'. Wrote a PL/sQL procedure using oracle db_link to simply 'insert into mssql_tble .. select .... from ora_tbl;' (a single sql statement) and it completes in four minutes. – EdStevens Sep 08 '20 at 13:53
  • @EdStevens : but after using db_link, how the same table gets created dynamically on SQL with the required columns metadata. – Ajit Medhekar Sep 08 '20 at 15:57
  • @Filburt : Yes, we have added a linked server, and we can check the schema for Oracle tables. But the same need to be converted to SQL tables dynamically, and also tables should get created dynamically on SQL as there are 500+ tables. So we can not create separate SSIS package to create SQL table for Oracle table and then load the data. – Ajit Medhekar Sep 08 '20 at 15:59
  • see my proposed answer .. coming up in a minute – EdStevens Sep 08 '20 at 16:23

1 Answers1

0

Since you have to do it with a large number of tables, I'd write a pl/sql procedure something, built around something like this:

declare v_sql varchar2(1024);

begin
for x in (select owner, table_name from dba_tables where .....)
  v_sql := 'created table '||
           table_name ||
           '@mssql a select * from '||
           x.owner || '.' || x.table_name || ';';
  exec immediate v_sql;
end loop;
end;
/

or, if you want to look it over before launching, use sql to write sql. In sqlplus:

set echo off feedback off verify off trimsp on pages 0
spool doit.sql
select 'create table '||
        table_name ||
        '@mssql as select * from '||
        owner || '.' || table_name || ';'
from dba_tables
where .....
;
spool off

then check the spooled sql file for any issues before running.

All code above is off the top of my head. There may be minor syntax issues.

EdStevens
  • 3,708
  • 2
  • 10
  • 18