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

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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?
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. ;)
"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!!!
Yes, the source. And that too, if you are selecting all the columns, it would not make much difference.
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.