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 |
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.
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.
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
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.
2 Comments