Maximum number of data sources in a dataflow task

Greg Booth 421 Reputation points
2021-11-05T11:06:02.757+00:00

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

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,862 questions
No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 20,101 Reputation points
    2021-11-05T12:37:09.5+00:00

    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.
    No comments

  2. ZoeHui-MSFT 18,976 Reputation points
    2021-11-08T03:04:27.617+00:00

    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.

    No comments