SSIS never gets past 50% in Pre-execute phase

  • Michael L John

    One Orange Chip

    Points: 25625

    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 post 1 (of 1 total)

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