Why does my SSIS package run slower in BIDS than DTEXEC?

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

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQLServerPedia Syndication, SSIS. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>