In the last chapter of this tutorial weíll look at a couple of performance optimizations you can implement in your SSIS packages. After all, you want to move data around as quickly as possible.
There is typically not much you can do in the control flow to improve performance. There are two key points however:
Most performance issues are related to the data flow. As with the control flow, think if SSIS or transformations in SQL will be faster. Try to visualize the data flow as a pipeline with data flowing through. You want to maximize the flow rate to get data to the destination as quickly as possible. There are some important properties you can set to influence the memory buffers.
The actual buffer size will be determined by which of the two properties is reached first. You can set AutoAdjustBufferSize to True to make sure that the specified number of rows in DefaultBufferMaxRows is always met.
Here we have some best practices as well:
Writing data is typically the slowest part of the process. Here are some tips to optimize the process: