Skip to main content

How to create a dynamic project variable in TimeXtender Classic


Thomas Lind
Community Manager
Forum|alt.badge.img+5

Dynamic project variables allow you to create a SQL script that returns a single value, then use that value anywhere in your project, from data selection rules to execution package conditions.  In this example, we will create a variable that returns the current date.

Set up a dynamic variable

  1. To create a dynamic project variable, right-click on the top-level project node and select Project Variable.
  2. In the Project Variable window, click Add
  3. Give your variable a friendly name and select Dynamic from the Type dropdown.
  4. Choose when to resolve the variable from the Resolve Type dropdown menu.  There are three types:
    • Every time: The variable will be recalculated every time it is called.  This is the default value.
    • One Time: The variable will be calculated once, the first time it is called.  That value will be used in all subsequent calls until the project is closed. 
    • Each Batch: Every time the DW_Batch value increments in the project, the variable will be recalculated. This is usually once per execution.
  5. Your SQL statement will need to run in the context of one of your TimeXtender databases.  Choose this database from the Context dropdown.  If you need to query tables primarily from one database, it's best to choose that one for your context.  If you do not need to query any tables, it doesn't matter as much which database you choose.  Our query to get the current date doesn't need to reference any table, so we will just choose the data warehouse.
  6. All that's left now is to write the SQL that your variable will use. Unlike custom transformations, you will need to use SELECT in your variable script.  To get the query to return the current date without the time, we'll use SELECT CONVERT(char(10), GetDate(),126)
  7. To see what result the script returns, you can click on the Show Value Translation node. This is a good way to test that your script works correctly.

     

  8. Click OK to leave the Script Editor. 

  9. Click OK to create your variable.

  10. Click OK to leave the Project Variable window. 

  11. Save your project.

Your dynamic project variable will now be available all across your current project.

How to use your variable

Once you have the dynamic variable, there are some things to consider when you want to apply it.

A use case could be that you would want a rolling 30-day filter on some large tables for your development environment.

How to make an environment variable is explained here.

The query we want to run is the following.

SELECT CONVERT(char(10), DateAdd(DAY, -30, GetDate()),126)

The result of this on the following date, 2025-06-11, would be the date 2025-05-12.

There are many ways to achieve this.

Create a Fixed GetDate() variable

I have created a variable like so.

I called it Now, the type is set to Fixed and the Value is set to GetDate().

If I apply this in a dynamic variable like so.

The result will be this.

Add a variable in another dynamic variable script

I could then make a Today variable that used this Now variable.

It runs the same query from before, but have the Now variable as a parameter. 

SELECT CONVERT(char(10), [Now], 126)

Use the Value option

You can see that there is a Value field with a pencil icon in the list of options.

You can add content to the Object Name/Value field of the parameter.

For example you could add CONVERT(char(10), DateAdd(DAY, -30, GetDate()),126) to the parameters value field.

Then it will behave like this.

You can change the parameters variant to be a Variable instead of a Value like so.

I drag the value into the editor area and then adds the Today variable to the Value field, it automatically changes to be Dynamic and I then apply apostrophes around it, so it returns the correct value.

Apply the variable as a data selection rule

You have the variable ready.

 

Create a Data Selection rule on the field where you want to apply this variable. 

It should be a Custom one.

Then you add it like so.

It should be like this [OrderDate]>'[30DaysAgo]' the apostrophes are necessary for this to work.

Once it has been added, you can apply the Usage Condition variable.

Click on Add Usage Condition, click on the Environment variable, make sure the Operator is set to Equal and that the Value is equal to the environment (Development) you want this rule to apply to.

Once this is done the rule is applied and will affect the Development environments execution of the Orders table.

 

Did this topic help you find an answer to your question?

0 replies

Be the first to reply!

Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings