SSIS Slow - Ole DB Data Flow Optimazation

Do you build SSIS (SQL Server Integration Services) Packages?

Are you constantly running into Visual Studio acting sluggish or giving you "Microsoft Visual Studio is Busy"?

In my case, I use views a lot when I'm querying my source data (for reasons of maintainability). When you use views in an OLE DB Source, anytime you change the scope of what you are working on, e.g. jumping back to the control flow and then back into the Data Flow, Visual Studio rebuilds its meta-data and does validation checks to see if source or destination attributes have changed.

Resolution

Set AccessMode to "SQL Command"

And set SqlCommand to a Select query of your view.

Example:

SELECT * FROM v_MyView

Why does this work?

"...it calls OpenRowset in the validation phase to retrieve column metadata, and later in the execution phase to read out the data.

Testing has shown that using a SELECT statement can be at least an order of magnitude faster, because the adapter issues the specified command directly through the provider and fetches the data using sp_prepare without executing the command, avoiding the extra roundtrip and a possibly inappropriate cached query plan."

If you have any questions... Just ask, I'll be happy to assist where I can.