Loading data from oracle to SQL in multiple tables simultaneously using SSIS

Question

enter image description here

I am creating a clinical data warehouse, so I am testing different scenarios. I am loading the below tables from oracle DB (Attunity connector) to SQL DB (OLE DB):

Table1 1.2 GB(3 million rows) Table2 20 GB(200 million rows) Table3 100 GB(250 million rows) Table4 25 GB(60 million rows)

For my initial load I am planning to use SSIS and just select * from TABLE1/TABLE2/TABLE3/TABLE4

Questions :

Is it ok to have multiple data flow tasks for loading each table in one package. So that they are all running together. i just wanted to improve the speed with that. But somehow it is slower than if I run it individually.

Also for loading complete tables is "select * from table" a good way? It seems pretty slow!!

Answer 1

You can have as many parallel data flow tasks executing as the number of processor cores you have minus one. That is, if you are using an octacore processor, the ideal number of parallel tasks is 7 (8 -1 ). Just put in it different sequence containers(not compulsory,but for the sake of readability) and execute.

You can speed up the data load by adjusting several things like the setting DelayValidation=true and using OPTION ( FAST 10000(or any value,just do some trials)) and also play around with the DefaultBufferSize and DefaultBufferMaxRows until you get the right one. Also, check if the MAXDOP value is not set to 1 int the settings, if you intend to run parallel DFTs.

And, NEVER use SELECT * from table_name. List out the column names, * adds additional overhead and can slow down your query considerably.

Answer 2

Process 1: Using SSMA

You can use SQL Server Migration Assistant (SSMA) for Migration the data from Oracle to Sql Server Databases/Schemas/Tables.

This is Open source tool from microsoft for database migration.

Microsoft SQL Server Migration Assistant (SSMA) is a tool designed to automate database migration to SQL Server from Microsoft Access, DB2, MySQL, Oracle, and SAP ASE.

Process 2: Using SSIS

You can also use SQL Server Integration Services (SSIS) package for Migration.

Create SSIS package from Import/Export wizard and run the package into command line.