SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


No comments.

Leave a Comment

Please register or log in to leave a comment.