Enhancing SSIS Performance for Large Tables

In an SSIS dataflow, when dealing with a large target table (with millions of records or more), performance can become a consideration. Here are some ideas to significantly improve performance:

1. Reconfigure SSIS Settings

• In the "Properties" of the data flow:
• DefaultBufferMaxRows: Increase this number to 10-100 times the original value (somewhere between 100,000 and 1,000,000) and observe which value provides the best performance.
• DefaultBufferSize: Increase this number to 10-100 times the original value (somewhere between 104,857,600 and 1,048,576,000) and observe which value provides the best performance.
• In the "OLE DB Destination Editor":
• Uncheck "Check constraints"
• Change the value of "Maximum insert commit size" to 0

Please note that the effectiveness of these setting changes may vary from server to server, so some testing and adjustment are necessary.

2. Drop All Indexes

If any of the large target tables have indexes (including primary keys), drop them before loading the data and re-create them afterward.

3. Consider Using "SELECT INTO"

Another option is to replace the Data Flow task with an Execute SQL task and use "SELECT INTO" (not "INSERT INTO") to create the target table on-the-fly. Indexes can be created later after the table is fully loaded with data. This approach often results in a significant performance improvement for large tables.

Conclusion

These optimizations, including reconfiguring SSIS settings, dropping and recreating indexes, and considering the use of an Execute SQL task with 'SELECT INTO' can greatly enhance the performance of SSIS dataflows when working with large target tables.