While working on my current SSIS assignment I ran into some strange performance problems. The package ran fine but it took a lot of time to get a certain Data Flow Task to finish.
The strange thing was that when executing the package in Visual Studio, all elements in the Data Flow Task were ‘green’ but the Data Flow Task itself remained ‘yellow’ for a long time. Eventually the flow finished ok, but it took about 5 seconds to execute all elements in the flow, but the total time of the package was 2 minutes.
The ‘Execution Results’ tab showed that the package seem to spend a lot of time in the ‘Post Execute phase’ and ‘Cleanup phase’.
At first I thought it was something in the Data Flow Task itself, so I removed some items. This didn’t solve anything. The I thought it was something with a table being locked or so, but the SQL monitoring showed no locks during execution.
With such behavior it was hard to find the solution using a search engine, but luckily I found the solution here.
This post saved my life because it suggests to increase the Data Flow Tasks DefaultBufferSize setting. By default it is 10485760 and I increased it with a factor 4. After doing that the package ran in 10 seconds instead of 2 minutes. Great!
If this doesn’t solve your problem, take a look at the Ole Db Destination, if you’re using one. If you use the ‘Table or view – fast load’ make sure unnecessary settings are turned off. For example only check constraints if it is really necessary.