Fixing SSIS Data Flow Task Performance Problems

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.

PDC 2009 in less than an Hour

If you’re like me than you’re probably also catching up all the interesting stuff that was presented at the PDC this year. You also probably agree that it is way to much and it is taking way to long to view all the interesting sessions.

Luckily I got an email from Ideablade.com with a link to a presentation which gives an overview of the most important news in less than an hour.

Click here to go to the PDC 2009 Tour by Ideablade.com.

Also a PDF with links to all the sessions are available. You can get them here.

The tour is from a business application developers perspective and according to the presenter the following technologies should be watched closely:

  • Silverlight 4 (New Visual Studio ‘Cider’ for Silverlight development)
  • WCF Data Services (for exposing data)
  • WCF RIA Services (for building applications)
  • Entity Framework
  • OData
  • Parallelism
  • F#

The recommended sessions are:

  • CL01 – Silverlight 4 overview
  • CL21 – Amazing Bus Apps with RIA Services
  • CL19 – Building LOB Apps
  • CL20 – Trusted Apps Out of Browser
  • CL07 – Mastering RIA Services
  • CL06 – Networking and Web Services in Silverlight 4
  • FT24 – Extensible RIA with MEF
  • CL22 – Building Larget-Scale Apps with Silverlight 4
  • FT10 – Evolving EF in .NET 4
  • Manycore and the Microsoft .NET Framework 4 (Stephen Toub)
  • PLINQ: LINQ, but Faster! (Igor Ostrovsky)
  • Rx: Reactive Extensions for .NET (Erik Meijer)
  • F# for Parallel and Asynchronous Programming (Luke Hoban)

After the hour during overview I certainly need more time to see more sessions!