Follow

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.
Dynamic

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

DynVar01.PNGDynVar02.PNGDynVar03.PNGDynVar04.PNG

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.

DynVar05.PNGDynVar06.PNG

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 naming. 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.

SELECT COUNT(*)
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.

DynVar07.PNGDynVar08.PNG

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.

DynVar09.PNG

Test it out

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

Variation

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
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.