Dynamic SSIS package to load N tables from Oracle to SQL

Question

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.

Answer 1

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.