This is a fairly straightforward SSIS package that builds a set of "summary" tables used by a group for reporting.
There are 18 tables. The package truncates the destination tables with an Execute SQL task, and then in a data flow task it run a SQL query in an OLE Data Source, and inserts the rows into an OLE Data Destination.
A couple of these have transforms to convert to unicode tasks in them.
One set of the truncate/load tasks will never get past "Pre-Execute - 50 percent complete" in the progress.
The query itself runs in less than a second in SSMS. It returns approximately 11k rows.
If i disable this step, the entire package completes in 4-6 minutes.
Googling for this error, these are the various settings I have changed which made absolutely no difference.
- Delay Validation = true
- Made the buffer size 80971520, and set AutoAdjustBufferSize to true
- Ran it with DefaultBufferMaxRows set to 100, 1000, 5000, 10000, 25000 with no change.
- Tried setting the Isolation Level to unspecified or read uncommitted, and setting it in the T-SQL. No difference.
I have deleted the data flow task and re-created it. I have set it to 32 bit and back. The only thing that made a difference was to create a view and select against the view. Unfortunately, that is not an option once we move to production. And, this is the same behavior regardless of which environment I connect to for the data source, dev, qa, production all take forever to run.
Right now, it's been running for 2 hours.
The databases are SQL 2016. I am using Visual Studio 2017.