If you have been creating SSIS packages in Business Intelligence Development Studio (BIDS) for any length of time, you have likely noticed that SSIS packages run slower in BIDS as opposed to running them in a SQL Server agent job (which uses the command prompt utility DTEXEC) or when in integration services and clicking Run Package (which calls DTEXECUI which creates a command line used by DTEXEC).  My experience is that the packages in BIDS run at least 20% slower.  Why is that?

Well, BIDS is slower than other methods when running a package because of the overhead of debug mode as well as preparing the BIDS interface to trace what happens during execution.  If you run the package in BIDS, you will always be running in debug mode…there is no way around that.  But you can instead deploy and run your package with DTEXEC or DTEXECUI for a performance boost.  For more details on how to run the package with these other methods, check out How to: Run a Package.

More info:

Why does my package run slower in BIDS than DTEXEC?

Always use dtexec.exe to test performance of your dataflows. No exceptions.

For more performance tips on loading data:

We Loaded 1TB in 30 Minutes with SSIS, and So Can You

The Data Loading Performance Guide