Pre-Execute Phase

What happens during the pre-execute phase?

Is there anything I can do to optimize it's execution?

I have a package that takes data from 2 SQL Server sources, unions them, and writes to a sql table.

The pre-execute takes 20 minutes, and the execution takes 45 seconds.

Thanks

BobP

Are you sure there are no other components in your data flow (Lookups?). Do you have another data flow in the package?

Give us a picture about the used metadata; how many columns? How many rows do get transferred?

Thanks.

|||

This is happening on several packages:

1. The example above takes 2 sql server source, with queries returning ~5,000 rows each, unioning them and writing them to a sql table, with no lookups. The pre-execute takes 20 minutes.

2. Every once in a while, a job that I have that runs every night, will freeze on the pre-execute. No errors, the job appears to be still running, but it is not doing anything, no processor activity, etc.

What I am looking for is a "list" of what the pre-execute phase is doing? I have no way of troubleshooting the #2 issue above.

Does anyone have any ideas, or is anyone else experiencing this?

Thanks!

BobP

|||

This is starting to become a big problem, as this pre-execute task is now locking up at least once a week.

Does anybody have any similar experiences?

Thanks!

BobP

|||

Well, I have found the answer.

The Source Query actually executes during the pre-execute phase.

What was happening was I was getting never ending cxpacket waits.

I adjusted my MAXDOP and this seems to fix it.

Pre-Execute Phase

What happens during the pre-execute phase?

Is there anything I can do to optimize it's execution?

I have a package that takes data from 2 SQL Server sources, unions them, and writes to a sql table.

The pre-execute takes 20 minutes, and the execution takes 45 seconds.

Thanks

BobP

Are you sure there are no other components in your data flow (Lookups?). Do you have another data flow in the package?

Give us a picture about the used metadata; how many columns? How many rows do get transferred?

Thanks.

|||

This is happening on several packages:

1. The example above takes 2 sql server source, with queries returning ~5,000 rows each, unioning them and writing them to a sql table, with no lookups. The pre-execute takes 20 minutes.

2. Every once in a while, a job that I have that runs every night, will freeze on the pre-execute. No errors, the job appears to be still running, but it is not doing anything, no processor activity, etc.

What I am looking for is a "list" of what the pre-execute phase is doing? I have no way of troubleshooting the #2 issue above.

Does anyone have any ideas, or is anyone else experiencing this?

Thanks!

BobP

|||

This is starting to become a big problem, as this pre-execute task is now locking up at least once a week.

Does anybody have any similar experiences?

Thanks!

BobP

|||

Well, I have found the answer.

The Source Query actually executes during the pre-execute phase.

What was happening was I was getting never ending cxpacket waits.

I adjusted my MAXDOP and this seems to fix it.

pre-execute failure

I just started getting this error.

[DTS.Pipeline] Error: component "User Type" (377) failed the pre-execute phase and returned error code 0x8007000E.

It wasn't happening before. Does anyone know what it means?

Hi Jim,
Without more information it is probably impossible to say.

What type of component is it?
What are you using it for?
How have you configured it?
When do you get the error - when the package starts or when the data-flow starts?
What inputs does it take?

etc...etc...

Regards
Jamie|||

Excelent questions.
This s a dataflow component. All of it's input is from a table. It seems like just rearranging the dataflow components in the work flow eliminates the problem. Since it isn't happening any longer, I can't do a better job answering your questions.

I don't understand it.

|||Incidentally, that error is Out Of Memory, so there could have been some transient cause.... Please let us know if you do come across a repro.sql