Earlier this evening I posted a blog post entitled Investigation: Can different combinations of components effect Dataflow performance? where I compared the performance of three different dataflows all working to the same overall goal. I wanted to make one last point related to the results but I thought it warranted a blog post all of its own.
Here is a screenshot of one of the dataflows that I was testing:
Pretty complicated I’m sure you’ll agree. Now, when I executed this dataflow in the test it was executing in ~19seconds however in that case I was executing using the command-line tool dtexec. I also tried executing inside the BIDS development environment and in that case it took much longer – 139seconds. That’s more than seven times as long.
The point I want to make is very simple. If you are testing your dataflows for performance please use dtexec. Nothing else will suffice.
@Jamiet
UPDATE: Matt Masson from the SSIS team has posted a great blog post explaining exactly why dtexec is quicker. Go read it at Why does my package run slower in BIDS than DTEXEC?