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.
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 would increment 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 TX DWA 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 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, we'll use SELECT GETDATE().
7. Click OK to leave the Script Editor.
8. Click OK to create your variable.
9. Click OK to leave the Project Variable window.
10. Save your project.