Maximum number of data sources in a dataflow task

Question

We are creating some SSIS packages that need to copy a large number of tables from Oracle to SQL - also from Postgres to SQL.
Apart from other considerations - in a data flow - what is the reccomended maximum number of data sources for best performance ?

For exmaple if we need to copy 100 tables, we could create one data flow - with 100 datasource/destinations (one for each table) - so that all the tables are copied in parallel - but unsire if this is a good idea (appreciate that we dont want to overload the source database)

Thanks

Answer 1

Hi @Greg Booth ,

• If you need to copy table by table, i.e. no need for any relationship, it is better to have one single source and destination in a Data Flow Task (DTF).
• It is better to use SSIS Sequence Containers for parallelism by placing multiple DFTs in them. After that DFTs will be executed in parallell manner.
• Degree of parallelism is not unlimited. it is dependent on the number of logical processors on the SSIS run-time server.

Answer 2

Hi @Greg Booth ,

Unless the tables are the same in structure (field names, types) and have exactly the same destination (table, field names), you'll need separate dataflow tasks anyway.

If you just want to copy the 100 Oracle tables to SQL database, I would suggest the Import and Export Wizard.

SQL Server Import and Export Wizard is a simple way to copy data from a source to a destination.

connect-to-an-oracle-data-source-sql-server-import-and-export-wizard

import-and-export-data-with-the-sql-server-import-and-export-wizard

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.