SSIS never gets past 50% in Pre-execute phase

Question

Post reply

SSIS never gets past 50% in Pre-execute phase

Michael L John

One Orange Chip

Points: 27038
More actions
May 15, 2019 at 4:48 pm

#3643968
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.
1. Delay Validation = true
2. Made the buffer size 80971520, and set AutoAdjustBufferSize to true
3. Ran it with DefaultBufferMaxRows set to 100, 1000, 5000, 10000, 25000 with no change.
4. 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.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

TangoVictor SSChampion Points: 10969 More actions · Answer 1

You running this interactively?

Can you run it interactively?

I've had some very odd behavior, where the agent appeared to start the job but nothing would happen.

I usually confirm i can run it in VS then delete the entire deployment and re-deploy. It's pretty seldom but i can tell when something just isn't right and not worth the time to troubleshoot, just rebuild it. Or even deploy to a different SSIS server and see what it does there.

next i would look closely at the proxy account used to run the SSIS job steps to make sure they have security as needed.

Next run profiler or just activity monitor and filter by your proxy to get an idea of what's its doing and if you see any sort of locking.

But pre-execute doesn't "do" anything its just checking connections and meta data.

I guess my first step is to rule out the actual ETL as the problem.

Michael L John One Orange Chip Points: 27038 More actions · Answer 2

It was the same behavior running it in Visual Studio or deployed to the server as a package.

Permissions are correct, it wouldn't have been able to do other things if they we not.

I "solved" it by creating a proc or a view of the query. It then completed in seconds.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/