Load Parameters
BimlFlex Parameters can be used in many ways. For example, for managing data load windows, filtering and adding default values.
Parameters are most commonly used to define a 'high water mark' or define a filter for what is returned from a source system.
Parameter Management
Once a parameter is created in BimlFlex, the values for the parameter will be managed in the BimlCatalog is used to manage them.
Note
Parameter values are persisted in the [bfx].[ConfigVariable]
table, and parameter history is logged to the [bfx].[AuditConfigVariable]
table.
Click a tab for example or detailed column descriptions.
Data Type Override
[DataTypeOverride]
is not a column that is populated by default and is only required in limited edge case scenarios.
This column should be ignored unless you are experiencing issues with the parameter updating as a result of one of the below scenarios.
The parameter will fail to update in the [bfx].[ConfigVariable]
if all the below are true:
- Parameter is an
int
ordecimal
datatype - Parameter already has an entry in
[bfx].[ConfigVariable]
- The current
[VariableValue]
can be evaluated as a date, specifically ifISDATE( [VariableValue] ) = 1
on SQL Server - The value being loaded does can not be evaluated as a date, specifically if
ISDATE( [VariableValue] ) = 0
on SQL Server
Note
[DataTypeOverride]
currently only supports the int
and decimal
values.
Updating Data Type Override
The [DataTypeOverride]
can only be accessed/viewed on the [bfx].[ConfigVariable]
table in the BimlCatalog.
To populate this column use SQL UPDATE statement as outlined in the template below.
UPDATE
[bfx].[ConfigVariable]
SET
[DataTypeOverride] = [ 'int' | 'decimal' ]
WHERE
[ConfigVariableID] = `{ ConfigVariableID }`
Click a tab for example or detailed column descriptions.
Parameter Usage
Below demonstrates how a data parameter has been incorporated into a source to target loading package. Note that this load starts and ends with either a sequence containers (SSIS) or activities (ADF) that are specific for managing parameters.
Each time a package or pipeline is ran, BimlFlex will store the last value and update it as it changes over time.
The process starts before the main container (SEQC - Main
).
The initial Sequence Container (SEQC - Get Parameters
) will have a Execute SQL Task to look up any relevant parameters.
These task (SQL - Get Parameter
) are used to check the BimlCatalog for an existing parameter value for the PARAMETER.
The retrieved value will be injected in to the source query filter clause using the specified logic from the metadata.
The final Sequence Container (SEQC- Set Parameters
) after the main logic then handles setting the parameters.
There is a Execute SQL Task to grab the latest value (SQL - Get Current Parameter
) from the staged data.
A following Execute SQL Task (SQL - Set Parameter
) is then used to persist the value retrieved into the BimlCatalog.
Creating Parameters in BimlFlex
Parameters can be added in BimlFlex through the Parameter Editor, accessible from the BimlFlex main menu.
To add a new parameters, click the +
button in the Treeview menu.
Base Parameter
The Base Parameter settings are the core fields that are required to define a parameter in BimlFlex. With the exception of PARAMETER SQL, these are Source System agnostic. Configuration of the base Parameter itself remains the same across Source Systems.
Note
*
: It is recommended that you useString
for dates as SSIS sometimes finds it easier to deal with string representations.**
: See Parameter SQL for more examples
Important
Execute SQL On Source
, along with Parameter To Name
and Parameter To Operator
, are required fields when configuring a Parameter for ADF.
Default
The parameter load value to use the first time the load happens, when there is no existing parameter value stored in the BimlCatalog database. For high water mark scenarios this is commonly a valid value guaranteed to be lower than any existing data, such as 1900-01-01
for dates. When adding dates in Excel, use the text-defining prefix so the data is kept as text rather than transformed into Excels internal date format, '1900-01-01
.
Parameter SQL
Although the field is named PARAMETER SQL, it is easier to this of this field as a Parameter QUERY.
In a SQL based Source System this will correlate to a SQL Statement that is appended to the the WHERE
clause.
In Dynamics, this will be an XML based statement for a <attribute/>
under the <fetch/entity/>
node or a complete replacement of the generated <fetch/>
statement.
Important
Ensure this value is configured to align with how EXECUTE SQL ON SOURCE is configured.
When true
this should mirror the syntax needed on the Source System.
When false
this should mirror the syntax needed on the Target System.
- T-SQL (Microsoft)
- SnowSQL
- Oracle
- PostgresSQL
- MySQL
- Dynamics - Attribute
- Dynamics - Blank
- Dynamics - Fetch
Field | Description |
---|---|
Parameter SQL* |
CONVERT(VARCHAR(27),MAX(@@this),121) |
Note
*
: All SQL based Source Systems will have similar pattern but differ in syntax.**
: When using Dynamics the first characters determine logic.- If the PARAMETER SQL starts with
<fetch
then it will override the entire fetch statement with. - If not, it will use the
Dynamics - Attribute
pattern. - If blank it will use the
Dynamics - Blank
pattern.
- If the PARAMETER SQL starts with
Window Parameters
Window Parameters are configured to load between two points.
Useful when it is not possible to derive the new parameter from the destination, such as a Blob file. This allows the from and to to be derived and applied in the source query using only the source data.
These get configured by defining the below fields.
Field | Description |
---|---|
Parameter To Name | Name of the parameter ending window parameter. Cannot be same as Parameter Name. |
Parameter To Operator | Operator to use again the PARAMETER TO NAME when applied to the source Column. |
Important
These, along with Execute SQL On Source
, are required fields when configuring a Parameter for ADF.
BimlFlex Pattern for Window Parameters:
`{Existing WHERE Clause}` `{AND if Needed}`
`{Column}` `{Operator}` `Parameter`
AND `{Column}` `{Parameter To Operator}` `{Parameter To Name}`
Example:
WHERE
[SalesLT].[ModifiedDate] > `{LastLoadDate Value}`
AND [SalesLT].[ModifiedDate] <= `{NextLoadDate Value}`
Parameter Overrides
These are provided to either apply alternate workarounds or replace sections of the the automated code generation.
Field | Description |
---|---|
Column Expression | Used to override the column part of the WHERE clause in the source query. |
Parameter SQL Expression | Used to override the entire WHERE clause in the source query. |
Parameter Override | Used to override the parameter part of the WHERE clause in the source query. |
Column Expression
Used to override the column part of the WHERE clause in the source query to tweak the column expression. Useful when extra logic is required for the filter.
Example expression
COALESCE(ModifiedDate, CreatedDate)
Parameter Override
Used to override the parameter part of the WHERE clause in the source query to tweak the column expression. Useful when extra logic is required for the filter.
Example expression
DATEADD(dd, -3, ?)
Parameters in Extension Points
Extension Point-based parameters are available for more complex scenarios where the logic needed for the flow and parameter isn't easily injected through the normal parameter process.
It also supports specifying Project level Parameters that are commonly available in all packages in the project.
Extension Point Project Parameters (SSIS)
Add Extension Points in BimlStudio.
More information: BimlFlex Extension Points
The newly created file contains some sample scripts:
<#@ extension bundle="BimlFlex.bimlb" extensionpoint="ProjectParameter" target="<ProjectName>" #>
<Parameter Name="ServerName" DataType="String" IsRequired="true">localhost</Parameter>
<Parameter Name="UserName" DataType="String" IsRequired="true">varigence</Parameter>
<Parameter Name="UserPassword" DataType="String" IsRequired="true">P@ssw0rd!</Parameter>
The directives are required for the Extension Point. An additional attribute for the target of the Extension Point needs to be specified.
For a project parameter, this is the name of the project it targets.
Once the Project Parameter is defined through the Extension Point it can be reused throughout the project in either metadata or additional Extension Points as needed.
Extension Point Package Parameters (SSIS)
Package Parameters only affect the individual package it targets.
Package parameters can be used when a single package requires bespoke logic that doesn't fit the existing parameter logic.
These parameters can be used for any logic and might not need to be persisted in the BimlCatalog database.
Add Extension Points in BimlStudio
The newly created file contains some sample scripts:
<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PackageVariable" target="<ObjectName>"#>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#* CustomOutput.ObjectInherit = true; *#>
<Variable Name="TenantCode" DataType="String">UNK</Variable>
<Variable Name="CurrentModifiedDate" DataType="String" Namespace="User">1900-01-01</Variable>
The directives are required for the Extension Point.
An additional attribute for the target of the Extension Point needs to be specified.
For a package parameter, this is the name of the package it targets.
Once the Package Parameter is defined through the Extension Point it can be reused throughout the Package in either metadata or additional Extension Points as needed.