English
Macros
macro is a powerful tool used to simplify the transformation definition.
Every macro has the following components:
- Name: The name of the macro.
- Language: The programming language used in the macro.
- Definition Statement: The logic or functionality defined within the macro.
Optionally, a macro can also have a referenced table.
Currently, two languages are supported: T-SQL and SSIS.
- T-SQL Macros: These can be used in transformations, calculated fields, and other database-specific objects.
- SSIS Macros: These can be used in SSIS statements to define import constraints or field transformations.
Below is a typical macro definition:
Macro Statement and Parameters
Every macro statement contains macro parameters, such as :1, :2, :3, and so on.
To call a macro, use the macro name with the @ symbol in front of it, followed by the parameters. For example:
&#xNAN;@Date2ID(T1.BUDAT)
The macro parser processes the macro statement and replaces :1, :2, etc., with the macro parameters. In the above case, it will be replaced with:
sql
CONVERT(bigint, ISNULL(DATEDIFF(DD, '20000101', CONVERT(date, T1.BUDAT)) + 1, 0))
Macro Parameters and NULL Replacement
If the number of macro parameters in a macro call is less than the number of defined parameters, the remaining parameters will be replaced by NULL. For example, the call @Date2ID() will be parsed into:
sql
CONVERT(bigint, ISNULL(DATEDIFF(DD, '20000101', CONVERT(date, NULL)) + 1, 0))
Referenced Table Parameter and Macro Usage
The "Referenced Table" parameter is used to automatically create a reference between the referenced table and the transformation field using a specific macro.
A common way to use macros is as a column definition statement in transformations. For example:
Macro Parsing in the Transformation VIEW
In the transformation VIEW, the macro statement will be parsed as follows:
sql
[AVal] = [T1].[WKGATR],
[FK_Time] = CONVERT(bigint, ISNULL(DATEDIFF(DD, '20000101', CONVERT(date, T1.BUDAT, 112)) + 1, 0))
Macro Definition Changes and Recalculation
If you change the macro definition, all dependent transformations and calculated columns will be recalculated automatically.
⬅ Previous Page | ➡ Next Page
Free Trial
REGISTER NOW AND ACTIVATE YOUR FREE TRIAL