We have a new community! Please visit support.timextender.com

Avoid starting a scheduled execution when one is running

Running only one schedule at a time

If you run an execution package several times a day, sometimes one of the runs arent done when the next starts. This makes both executions run slower.

Therefore i have made a guide using Dynamic Variables to avoid this.

Dynamic Project Variables

Project Variables is a feature that can be used to give you certain information. The types are:

Fixed Is just a specific value that you state
System Can be Machine Name, Environment Name, User Name and User Domain Name
Source Scope Can give you all sorts of information of the Data Sources
Destination Scope Can give you all sorts of information of the Destination Databases
Contextual Scope The same as Destination Scope, but you have to state the context. Like DWH and similar.

Has a Resolve type, that states when it is run. They can be: Every Time, One Time and Each Batch. Each Batch is for when you start a new session.

It also have Context. Where you can choose to which area the statement will apply. Like DWH, STAGE and OLAP.

Lastly it requires you to write a script, for your statement


Setting up the Dynamic Variables

If you wanted to use a text string, it would sometimes be necessary to create two dynamic variables.
One with the script and the other calling the variable. This is just to put ' marks around the text.


In this case though we want it to end up becoming a number and in that case you can use just one.

The script

We will be looking into the repository database and use the following tables ExecutionPackageLogs and Projects. Also i found that you cant use parameters. So all tables need to be specified to the fullest.

ExecutionPackageLogs contains all the info about executions. Like run time, the name of the execution package and the status.

Projects contains all the information about the project and is necessary to specify what project you want to look at.

The setup is like this. I do a count on the current running executions that is equal to the project, the execution package, is ValidTo 99999999 and that has the status NULL.

If a project is running the count will be 1 and 0 if it is not.

FROM [TxRepository].[dbo].[ExecutionPackageLogs]
JOIN [TxRepository].[dbo].[Projects]
ON [TxRepository].[dbo].[ExecutionPackageLogs].[ProjectID] = [TxRepository].[dbo].[Projects].[ProjectId] AND [TxRepository].[dbo].[Projects].[ValidTo] = 99999999
WHERE [TxRepository].[dbo].[ExecutionPackageLogs].[ExecutionPackageName] = 'Incremental'
AND [TxRepository].[dbo].[Projects].[Name] = 'Test'
AND [TxRepository].[dbo].[ExecutionPackageLogs].[EndStatus] IS NULL

To change it to your project all you need is to state the repository name in TxRepository, change the ExecutionPackageName from Incremental and change the Name from Test.

This is what it will give when no projects is running.


Using the Dynamic Variable

This is pretty simple. All you do is right click on the execution package and choose Add Usage Condition.

Point to the Variable we just made and set it to Less Than or Equal to 1. Save the project as deployed.


Test it out

I made a video showcasing how to test it. Running the script


If you want it to be a general rule, you can remove the line where ExecutionPackageName = 'Incremental'. Then it will not start if any of the execution packages in this project is running.

If you remove the Projects.Name = 'Test', then it is all the projects currently running in this repository.

Was this article helpful?
0 out of 0 found this helpful


  • 0
    Anders T. Grønlund

    I have set up three variables, that I use on an execution package. It works fine, and I get see the message of criteria not met, package skipped.

    But, when I set the same variables with the same criteria on another execution package, I doesn't work. The package starts even though it was expected not to.

    Is it a problem to use the same variables on more than one execution package?

  • 0
    Thomas Lind

    Hi Anders

    There is a method. You create a overarching execution package.

    Then you add the steps into that. Then the whole package will be skipped, if it doesn't follow the condition. Remember that merge steps need to be unchecked.

    Edited by Thomas Lind
Please sign in to leave a comment.