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.

Package dies after about 10000 seconds.

I have written a package that archives off old orders over night, it appears that this package is failing after about 10000 second every time it is run. I don't think it is memory as I am running it and checking for memory leaks.

Basic run down of package is

EXEcute SQL task to get orders to delete

If a for loop, loop each ordernumber

within the for loop there are 2 dataflow

dataflow 1

find related records in child tables (oldb connection using query)

using a mutli split first

check (with lookup) for records already in archive database

only copy on a fail from the look up

second

delete related records

dataflow 2

do the same but for the parent table

SP1 CTP is installed on server.

Any ideas?

You need to identify where the bottleneck is. Your log file should give some clues as to which task is taking a long time..

-Jamie

|||This might sound like a dumb question but were is my log file?|||

Not a dumb question at all.

You need to configure logging for your package. Right-click on the control-flow surface and select "Logging..."

-Jamie

|||If you have any connection to remote db's, you also want to check that. Some connection get lost or drop after runn continues for a couple of min, hour etc. (i happened to me once because the vendor from which i was downloading the data from had a batch process at their end that always interrupted my download)
Also, you can try doing batch insert to your achieve table by setting the Row per batch to a decent number. That may help it yours inserts are very very larg.

But first, check your loggs as jamei said

Overriding repeating totals from non-referenced slicer dimension

I've run across what appears to be a change in the default behavior of Analysis Services from 2000 to 2005. My question is how to get the default behavior to revert to the AS 2000 method.

If you have a cube with multiple measure groups or multiple linked cubes, queries that try to slice a measure using a dimension that is not related to the measure result in the total amount for any other slicers being repeated across the "invalid" slicer.

For example, the following query slices the reseller amount by calendar year and employee department. The results are as expected. Only the Sales department has any totals for the measure and all other departments return null.

SELECT { [Employee].[Employee Department].[All Employees].CHILDREN } ON COLUMNS ,{ DESCENDANTS( [Date].[Calendar].[All Periods], [Date].[Calendar].[Calendar Year] ) } ON ROWS FROM [Adventure Works] WHERE ( [Measures].[Reseller Sales Amount] );

The next query is identical, except it using the Internet Sales Amount measure, which comes from a measure group that does not have a link to the Employee dimension. The result is the total amount for internet sales is displayed across all employee departments. In AS 2000, the results would have been null or empty.

SELECT { [Employee].[Employee Department].[All Employees].CHILDREN } ON COLUMNS ,{ DESCENDANTS( [Date].[Calendar].[All Periods], [Date].[Calendar].[Calendar Year] ) } ON ROWS FROM [Adventure Works] WHERE ( [Measures].[Internet Sales Amount] );

At one level I can agree with the change, but non-the-less, it is a change in the default behavior, and users hate it. Is ther any way to revert this behavior back to the AS 2000 approach?

Thanks!

What you are running into is the fact that AS2K5 now applies ValidMeasure() automatically. You can revert back to AS2K behavior by setting the "IgnoreUnrelatedDimensions" property associated with your measure group to "False".

HTH,

- Steve

|||

Steve,

Many thanks! I knew it would be something simple, but was getting tired of looking...