Chain of execution packages are not able to run concurrently

Hi @luuk.bouman 

Do you have a screenshot of the error message?

Can you please review the following articles, to evaluate whether the execution is still running, and if so what activity is occurring before/after the task finishes (e.g. using SQL Server profiler or the below Execution Details SQL Query)?

Is my scheduled execution running

Scheduled Execution issues - Did it not start, did it fail, or is my execution still running?

How does the scheduler service work?

Execution Details SQL Query

Hi Christian,

Thank you for answering!

I'm familiar with the first two articles and read up with the third one before posting the question.

The issue is happening on a regular basis when processes/operations are running otherwise normally.

We will use the Executions Details query and the activity monitor to observe what is still running after the execution ends. We will do so tomorrow and see if we can isolate the problem. First time through though, it seems that the process mentioned in the logging just takes time to end after all the steps of the execution are finished and no SQL activity is present anymore. 

Eventviewer log:

LogEngine, Version=20.10.25.64, Culture=neutral, PublicKeyToken='removed’ Execution has been skipped for execution package 'Uurlijkse verversing. The project 'Projectname' is currently executing the package in the process ‘timeXtender’ with process id 7456. - Scheduler, Version=20.10.25.64, Culture=neutral, PublicKeyToken='removed’

Hi again

OK please let me know your findings. Please also consider the workarounds below.

It is possible to schedule the second package to run after the first package completes.

You can configure multiple packages to run concurrently (Edit Execution Package and go to Concurrent packages tab). For concurrent packages, ensure those packages are not operating on the same tables.

 @Christian Hauggaard 

Second time through the observation is the same. The execution package finished at 15:00, at 15:06 and 15:08 we recieve logs that the next scheduled execution is skipped because TX is still executing the package.

Now extra information that I didn't originally add is that after the execution package finishes in the post execution options it is set to run the package that contains the semantic models. Both these packages are set to be able to run concurrently. The original package runs all the layers in TX, the package that is set to run as a next step only runs semantic models.
 

Was just writing this as I saw your comment coming in. For clarity, the execution package the executes the project in TX is set to run at hourly interval, it takes about 50-55 minutes, as a post execution step it will trigger the package with the semantic models. Both these packages are set to be able to run concurrent. The issue is that the execution package the executes the project in TX skips at the next hourly interval, even though the previous run is already finished. 

Would you know if a conflict might arise from the fact that the package with the semantic models operates on the MDW layer, while the project execution inside TX also needs those tables to operate on? We've been having the described set up for quite a while already and the two packages were able to run concurrently in the past.

Checked it again and it I have to come back on what I stated before. The two packages don't seem to be able to run concurrently. If the package with the semantic models is still running the project package won't start. Package with the semantic models has grown and is taking longer now than it used to, causing the conflict to arise more often, and catching our attention.

Would you say that it is not possible to start a new execution of the project if the execution of semantic models is also operating on it?

Please review the FAQ on concurrency and parallel processing

Hi Syed,

I've reviewed the page you've mentioned and did not find any references to concurrent executions of the MDW layer and the semantic models. The question whether conflict would arise from this and if there are possible setups to avoid said conflicts remains.

Hi @luuk.bouman 

» Would you know if a conflict might arise from the fact that the package with the semantic models operates on the MDW layer, while the project execution inside TX also needs those tables to operate on?

Yes, the article says:

“Even if packages from separate projects are running at the same time (or Concurrent packages set to run in one project), ensure those packages are not operating on the same tables.” 

Have you implemented the workaround Christian suggested above, to sequence the packages one after the other?

If your concurrent packages involve the same objects you may get locking issues, which is why there is a warning on the feature. In this case the scheduler is skipping the execution despite the concurrency.

Thank you Syed, I've should've read more carefully.

Last week we've changed the setup of the packages. We created three separate packages. One that runs the ODX and DSA layers, one that runs the MDW layer, and one that runs the semantic models. Now, the ODX-DSA package is scheduled to run at the full hour. This package triggers the MDW package as a post execution step. The MDW package, at its turn, triggers the semantic model package as a post execution step. The ODX-DSA and semantic model packages are set up to be able to run concurrently and we made sure that these two packages do not operate on the same tables. Now we noticed two important things:

• Splitting up the packages as described does not make any difference. The next scheduled ODX-DSA package will not start if the semantic model package is still running. Note that this is usually well after the previous ODX-DSA and MDW loads have finished.
• We are seeing a difference in the event log message. If the ODX-DSA load skips because an otherwise unrelated package is still executing we receive a message that names this package, e.g. "The project 'TXProject' is currently executing the execution package 'Full Load' in the process ‘timeXtender’". However, if the ODX-DSA load skips because of the previous chain, we receive a message that doesn't state the involved package, i.e. "The project 'TXProject' is currently executing the package in the process ‘timeXtender’".

We've looked at it from all the angles we could, but can't find the reason why this happens. The packages are not operating on the same tables, do not include or exclude steps that overlap, yet they are not able to run at the same time. @Christian Hauggaard or @Syed Yousuf could it be that because these packages are triggered in a chain that TX somehow holds the package of the first step in the chain until the chain is finished? Or do you have any other angles to this issue?

Hi @luuk.bouman 

For more insight into the steps executed, timings and dependencies, you may use the following:

1. Tracing to Event Viewer or Text file logs

2. Execution Details SQL Query

Hi Syed,

We took those steps. With the SQL query we are not seeing any other SQL tasks being carried out than those semantic model loads, when the ODX-DSA execution skips. And we are monitoring and reviewing the event logs very carefully. This is where we saw that at those skips the event log doesn't contain the package that is causing it. Other than this we are not seeing causes or pointers in those logs.

If we, together, are not making any more progess on this issue, I will raise a ticket at the dedicated support channel, to take a detailed look at the situation.

Hi @luuk.bouman I will open a support ticket, so we can arrange a screenshare.