1

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!!

Doodle
  • 481
  • 2
  • 7
  • 20

2 Answers2

3

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.

AswinRajaram
  • 1,519
  • 7
  • 18
  • Thanks AsRa. just a further question. If I am loading 300 million rows should I be loading them together? select whatever column from table in data source.. is there a way to do it efficiently? – Doodle Aug 01 '18 at 06:45
  • 2
    You can use `Balanced Data Distributor` transformation to speed up your data load. Deep Dive about it and see if it helps you. Also, you can use `WITH( NOLOCK)` and `WITH(TABLOCK)`,if required. Hope you have done proper indexing on the tables. You can upvote/accept the answer, if any of those answers your question. Just saying. ;) – AswinRajaram Aug 01 '18 at 07:27
  • "proper indexing" When loading the data we are not supposed to have any indexes on the destination tables right ? Are you talking about the source? I have just been reading on indexing. Sorry if its basic!!! – Doodle Aug 01 '18 at 22:35
  • Yes, the source. And that too, if you are selecting all the columns, it would not make much difference. – AswinRajaram Aug 02 '18 at 05:10
0

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.

Chitt Ranjan Mahto
  • 51
  • 4