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.

• [bfx].[ConfigVariable] Details
• Example
• Column Definitions

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 or decimal datatype
• Parameter already has an entry in [bfx].[ConfigVariable]
• The current [VariableValue] can be evaluated as a date, specifically if ISDATE( [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.

• SQL Script Template
• SQL Script Example

UPDATE
    [bfx].[ConfigVariable]
SET
    [DataTypeOverride] = [ 'int' | 'decimal' ]
WHERE
    [ConfigVariableID] = `{ ConfigVariableID }`

• [bfx].[AuditConfigVariable] Details
• Examples
• Column Definitions

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.

• SSIS Architecture
• ADF Architecture

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 use String 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.

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.

• Description
• Example

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.

• Description
• Example

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.