Package Stuck on "Pre-Execute phase is beginning."

Hi all,

I have a monster SQL query (SQL Server connection) that I'm trying to export to flat file. My package appears to just stick on the "Pre-Execute phase is beginning" stage. (I left it running overnight, and it's still going... no error message, I think it's just hung. If I include a "Top 10" in the my source query it completes without a problem. I'm wondering if it's an out-of-memory issue, but when I've run into this in the past there have been error messages generated. I've tried using both a Data Reader and an OLEDB Datasource. Does anyone have any ideas of what's going on and how I might fix it?

Thanks!!!

JessYou should run the query in management studio and look at its explain plan... Chances are that it has nothing to do with SSIS. Perhaps you have locks in place that are blocking parts of this query such that it never completes?

You could always wrap the query into a view and use the view as the source to your package. That might help the pre-execute phase.

Also, try setting your DelayValidation property to TRUE for the connection manager that's being used for this query.|||

Thanks for your response. I finally got it working. Our off-shore team actually wrote the query for this project and it was fairly convoluted. I just have the pleasure of actually trying to RUN it.

Anyhow, there was no descernable blocking from sp_who2 'active.' While switching the source query over to a view (per your suggestion), I discovered that the source sql inserted a number of records into a (non-indexed) table variable via a cursor. Placing this data into a permanent, indexed table seems to be the change that got the package working.

I've done some more testing, and whenever I use a table variable in the source query I seem to get stuck at "Pre-Execute." Is this by-design?

Thanks again for your input.

Jess

|||

Hi Jessica,

It is never by design to get stuck.

Pre-execute is the time when your queries get prepared and all the problems in them are likely to show up at that point. Looking at the SQL Profiler might help you figure out what exactly is causing issues while queries are processed.

HTH.