Staging SQL Extension Points

The Staging SQL category has the following available Extension Points defined.

Persistent Staging Post SQL Call Process

Add After the Persistent Staging SQL Call (SSIS Only)

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PsaPostSqlCallProcess" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<#	var sourceObject = new ObjectViewModel(sourceTable, sourceTable.Connection.RelatedItem.IntegrationStage, "SRC");
	var targetObject = new ObjectViewModel(targetTable, targetTable.Connection.RelatedItem.IntegrationStage, "TGT");
#>
<#	CustomOutput.ObjectInherit = true; #>
<ExecuteSQL Name="SQL - PsaPostSqlCallProcess" ConnectionName="<#=targetObject.Connection.Name#>">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<Parameters>
		<Parameter Name="0" VariableName="User.ExecutionID" DataType="Int64" />
	</Parameters>
	<DirectInput>EXEC <#=targetObject.SystemType.StoredProcedureName(sourceObject, targetObject.Schema, targetObject.StartDelimiter, targetObject.EndDelimiter)#> ?</DirectInput>
<# 	CustomOutput.OutputPathName = @"SQL - PsaPostSqlCallProcess.Output"; #>
</ExecuteSQL>

What It Does

Injects an additional SSIS Execute SQL task into the control flow immediately after the Persistent Staging Area (PSA) stored procedure call completes. This lets you run post-load validation, auditing, or cleanup logic against the PSA tables once the standard PSA load has finished. The task runs in the same SSIS package execution context, so it has access to the same connection managers and variables.

Where It Fires

Consumed in SSIS package generation files for PSA loading. The emitted Biml is placed as an Execute SQL task after the PSA stored procedure Execute SQL task in the generated SSIS package control flow. The framework retrieves it during SSIS package build time for each object that has PSA persistence enabled.

When to Use It

1. You need to run a row-count validation query against BFX_ODS after the PSA load for SalesLT.Customer completes, and you want to log mismatches to an audit table. Use this instead of StgPostProcessSql when the logic must run after the PSA procedure specifically, not after the staging insert.
2. A downstream process requires a flag table to be updated once PSA data for SalesLT.SalesOrderHeader has been persisted, signaling that the data is available for consumption.
3. You need to execute a custom stored procedure on BFX_ODS that archives or compresses older PSA records after each incremental load cycle in the EXT_AWLT_SRC batch.
4. Post-load statistics collection or index maintenance must run against the PSA table immediately after the load, before the next object in the batch begins processing.
5. You want to insert a notification or logging step (e.g., writing to a service broker queue) that fires only when the PSA load succeeds.

Prerequisites

• The object must have PSA (Persistent Staging) enabled in BimlFlex metadata.
• SSIS deployment model; this extension point does not apply to ADF or SQL-only ELT builds.
• The target connection (e.g., BFX_ODS) must be accessible from the SSIS execution environment.
• If you are chaining with PsaPreSqlCallProcess, ensure the OutputPathName values align correctly.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to PsaPostSqlCallProcess.
3. Set the target attribute to the specific source object (e.g., SalesLT.Customer) or omit it and use ObjectInherit = true to apply to all objects in the batch.
4. Define the Execute SQL task with the desired post-load logic.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PsaPostSqlCallProcess" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<#  var sourceObject = new ObjectViewModel(sourceTable, sourceTable.Connection.RelatedItem.IntegrationStage, "SRC");
    var targetObject = new ObjectViewModel(targetTable, targetTable.Connection.RelatedItem.IntegrationStage, "TGT"); #>
<ExecuteSQL Name="SQL - Post PSA Audit" ConnectionName="<#=targetObject.Connection.Name#>">
<#  if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
    <PrecedenceConstraints>
        <Inputs>
            <Input OutputPathName="<#=precedenceConstraint #>" />
        </Inputs>
    </PrecedenceConstraints>
<#  } #>
    <DirectInput>EXEC [audit].[LogPsaLoadComplete] '<#=targetObject.Schema#>.<#=targetObject.Name#>'</DirectInput>
<#  CustomOutput.OutputPathName = @"SQL - Post PSA Audit.Output"; #>
</ExecuteSQL>

5. Build the project in BimlStudio and verify the SSIS package now contains the additional Execute SQL task after the PSA stored procedure call.

Example

Before -- the generated SSIS control flow contains only the standard PSA procedure call:

<ExecuteSQL Name="SQL - PSA Load Customer" ConnectionName="BFX_ODS">
    <DirectInput>EXEC [psa].[stp_Customer] ?</DirectInput>
</ExecuteSQL>

After -- with the extension point applied, the audit task appears immediately after:

<ExecuteSQL Name="SQL - PSA Load Customer" ConnectionName="BFX_ODS">
    <DirectInput>EXEC [psa].[stp_Customer] ?</DirectInput>
</ExecuteSQL>
<ExecuteSQL Name="SQL - Post PSA Audit" ConnectionName="BFX_ODS">
    <PrecedenceConstraints>
        <Inputs>
            <Input OutputPathName="SQL - PSA Load Customer.Output" />
        </Inputs>
    </PrecedenceConstraints>
    <DirectInput>EXEC [audit].[LogPsaLoadComplete] 'psa.Customer'</DirectInput>
</ExecuteSQL>

Common Mistakes

Mistake	Symptom	Fix
Forgetting to set CustomOutput.OutputPathName on the last task.	The next task in the SSIS control flow cannot find its precedence constraint input, causing a build error.	Always set CustomOutput.OutputPathName to @"SQL - YourTaskName.Output" on the final task in the extension point.
Using this extension point in an ADF or SQL-only ELT project.	The extension point is silently ignored and no post-PSA logic executes.	For ADF/ELT projects, use StgPostProcessSql to inject SQL into the generated stored procedure instead.
Not checking the precedenceConstraint parameter before emitting <PrecedenceConstraints>.	Build error when the task is the first in the flow and precedenceConstraint is empty.	Always wrap the <PrecedenceConstraints> block in a null/empty check as shown in the template.
Targeting the wrong connection in ConnectionName.	The Execute SQL task fails at runtime because it connects to a database that does not contain the referenced stored procedure or table.	Use targetObject.Connection.Name to ensure the task runs against the same connection as the PSA load.

Related Extension Points

• PsaPreSqlCallProcess -- runs before the PSA stored procedure call; use in combination when you need both setup and teardown logic around the PSA load.
• PsaSqlCallOverride -- replaces the entire PSA stored procedure call; use when you need to completely rewrite the PSA execution logic rather than append to it.
• StgPostProcessSql -- injects SQL at the end of the staging stored procedure body; use this instead when working with ELT/SQL-only patterns rather than SSIS packages.

Persistent Staging Pre SQL Call Process

Add Before the Persistent Staging SQL Call (SSIS Only)

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PsaPreSqlCallProcess" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<#	var sourceObject = new ObjectViewModel(sourceTable, sourceTable.Connection.RelatedItem.IntegrationStage, "SRC");
	var targetObject = new ObjectViewModel(targetTable, targetTable.Connection.RelatedItem.IntegrationStage, "TGT");
#>
<#	CustomOutput.ObjectInherit = true; #>
<ExecuteSQL Name="SQL - PsaPreSqlCallProcess" ConnectionName="<#=targetObject.Connection.Name#>">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<Parameters>
		<Parameter Name="0" VariableName="User.ExecutionID" DataType="Int64" />
	</Parameters>
	<DirectInput>EXEC <#=targetObject.SystemType.StoredProcedureName(sourceObject, targetObject.Schema, targetObject.StartDelimiter, targetObject.EndDelimiter)#> ?</DirectInput>
<# 	CustomOutput.OutputPathName = @"SQL - PsaPreSqlCallProcess.Output"; #>
</ExecuteSQL>

What It Does

Injects an additional SSIS Execute SQL task into the control flow immediately before the Persistent Staging Area (PSA) stored procedure call executes. This enables you to run preparatory logic such as disabling indexes, setting session options, creating temporary work tables, or logging the start of a PSA load cycle. The task executes in the same SSIS package context and has access to all configured connection managers and variables.

Where It Fires

Consumed in SSIS package generation files for PSA loading. The emitted Biml is placed as an Execute SQL task before the PSA stored procedure Execute SQL task in the generated SSIS package control flow. The framework retrieves it during SSIS package build time for each object that has PSA persistence enabled.

When to Use It

1. You need to disable non-clustered indexes on the PSA table in BFX_ODS before loading SalesLT.Customer to improve bulk insert performance, then re-enable them with PsaPostSqlCallProcess. Use this instead of StgPreProcessSql when the optimization must happen at the SSIS package level rather than inside the stored procedure.
2. A pre-load validation must confirm that the source connection AWLT_SRC is reachable and that the SalesLT.SalesOrderHeader table has rows before the PSA procedure runs.
3. You want to insert an audit log entry recording the start time of the PSA load for a specific object in the EXT_AWLT_SRC batch.
4. Session-level SET options (e.g., SET ANSI_WARNINGS OFF) need to be applied on the connection before the PSA procedure executes.
5. A staging-to-PSA handoff requires truncating an intermediate work table before the PSA merge begins.

Prerequisites

• The object must have PSA (Persistent Staging) enabled in BimlFlex metadata.
• SSIS deployment model; this extension point does not apply to ADF or SQL-only ELT builds.
• The target connection (e.g., BFX_ODS) must be accessible from the SSIS execution environment.
• If chaining with PsaPostSqlCallProcess, ensure the OutputPathName values create a valid precedence chain.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to PsaPreSqlCallProcess.
3. Set the target attribute to the specific source object (e.g., SalesLT.Customer) or use ObjectInherit = true for all objects.
4. Define the Execute SQL task with the desired pre-load logic.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PsaPreSqlCallProcess" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<#  var sourceObject = new ObjectViewModel(sourceTable, sourceTable.Connection.RelatedItem.IntegrationStage, "SRC");
    var targetObject = new ObjectViewModel(targetTable, targetTable.Connection.RelatedItem.IntegrationStage, "TGT"); #>
<ExecuteSQL Name="SQL - Pre PSA Disable Indexes" ConnectionName="<#=targetObject.Connection.Name#>">
<#  if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
    <PrecedenceConstraints>
        <Inputs>
            <Input OutputPathName="<#=precedenceConstraint #>" />
        </Inputs>
    </PrecedenceConstraints>
<#  } #>
    <DirectInput>ALTER INDEX ALL ON [psa].[Customer] DISABLE</DirectInput>
<#  CustomOutput.OutputPathName = @"SQL - Pre PSA Disable Indexes.Output"; #>
</ExecuteSQL>

5. Build the project in BimlStudio and verify the SSIS package now contains the Execute SQL task before the PSA stored procedure call.

Example

Before -- the generated SSIS control flow goes straight to the PSA procedure:

<ExecuteSQL Name="SQL - PSA Load Customer" ConnectionName="BFX_ODS">
    <DirectInput>EXEC [psa].[stp_Customer] ?</DirectInput>
</ExecuteSQL>

After -- with the extension point applied, the index-disable task precedes the PSA call:

<ExecuteSQL Name="SQL - Pre PSA Disable Indexes" ConnectionName="BFX_ODS">
    <DirectInput>ALTER INDEX ALL ON [psa].[Customer] DISABLE</DirectInput>
</ExecuteSQL>
<ExecuteSQL Name="SQL - PSA Load Customer" ConnectionName="BFX_ODS">
    <PrecedenceConstraints>
        <Inputs>
            <Input OutputPathName="SQL - Pre PSA Disable Indexes.Output" />
        </Inputs>
    </PrecedenceConstraints>
    <DirectInput>EXEC [psa].[stp_Customer] ?</DirectInput>
</ExecuteSQL>

Common Mistakes

Mistake	Symptom	Fix
Forgetting to set CustomOutput.OutputPathName so the PSA procedure task can chain after it.	Build error: the PSA Execute SQL task cannot resolve its precedence constraint.	Always set CustomOutput.OutputPathName to connect the pre-task to the PSA call.
Running destructive DDL (e.g., disabling indexes) without a matching PsaPostSqlCallProcess to reverse it.	Indexes remain disabled after load, causing severe query performance degradation.	Always pair index-disable logic with a post-process step that rebuilds the indexes.
Using this extension point in an ADF or SQL-only project.	The extension point is silently ignored; no pre-PSA logic runs.	Use StgPreProcessSql for SQL-based injection in ELT projects.
Hardcoding schema or table names instead of using targetObject properties.	The extension point breaks when metadata changes or when applied to different objects via ObjectInherit.	Use <#=targetObject.Schema#> and <#=targetObject.Name#> for dynamic references.

Related Extension Points

• PsaPostSqlCallProcess -- runs after the PSA stored procedure call; use together for setup/teardown patterns like index disable/rebuild.
• PsaSqlCallOverride -- replaces the entire PSA stored procedure call; use when you need full control over what the PSA step executes.
• StgPreProcessSql -- injects SQL at the start of the staging stored procedure body; use this for SQL-level injection in ELT patterns.

Persistent Staging SQL Call Override

Override the Persistent Staging SQL Call(SSIS Only)

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added
precedenceConstraint	String	Contains the Precedence Constraint of the preceding task unless it is the first task

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.
OutputPathName	String	You must add CustomOutput.OutputPathName with the last task to connect it with the next Data Flow task.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PsaSqlCallOverride" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<#	var sourceObject = new ObjectViewModel(sourceTable, sourceTable.Connection.RelatedItem.IntegrationStage, "SRC");
	var targetObject = new ObjectViewModel(targetTable, targetTable.Connection.RelatedItem.IntegrationStage, "TGT");
#>
<#	CustomOutput.ObjectInherit = true; #>
<ExecuteSQL Name="SQL - PsaPreSqlCallProcess" ConnectionName="<#=targetObject.Connection.Name#>">
<#	if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
	<PrecedenceConstraints>
		<Inputs>
			<Input OutputPathName="<#=precedenceConstraint #>" />
		</Inputs>
	</PrecedenceConstraints>
<#	} #>
	<Parameters>
		<Parameter Name="0" VariableName="User.ExecutionID" DataType="Int64" />
	</Parameters>
	<DirectInput>EXEC <#=targetObject.SystemType.StoredProcedureName(sourceObject, targetObject.Schema, targetObject.StartDelimiter, targetObject.EndDelimiter)#> ?</DirectInput>
<# 	CustomOutput.OutputPathName = @"SQL - PsaPreSqlCallProcess.Output"; #>
</ExecuteSQL>

What It Does

Completely replaces the default PSA stored procedure Execute SQL task in the SSIS control flow with your own custom implementation. Instead of appending logic before or after the standard PSA call, this extension point removes the generated task entirely and substitutes your Biml. This is the nuclear option for PSA loading -- use it when the default stored procedure call is fundamentally incompatible with your requirements and you need to define the entire execution step yourself.

Where It Fires

Consumed in four .biml files used for PSA loading during SSIS package generation. The emitted Biml replaces the standard Execute SQL task that would normally call the PSA stored procedure. The framework retrieves it during build time for each object that has PSA persistence enabled.

When to Use It

1. The default PSA stored procedure does not meet your merge/upsert requirements for SalesLT.Customer, and you need to call a completely custom procedure on BFX_ODS with different parameters or logic. Use this instead of PsaPreSqlCallProcess / PsaPostSqlCallProcess when wrapping the default call is insufficient.
2. You need to replace the single stored procedure call with multiple Execute SQL tasks that run in sequence (e.g., a staging-to-PSA copy followed by a deduplication step) for the EXT_AWLT_SRC batch.
3. The PSA load for SalesLT.SalesOrderHeader must use a different execution mechanism, such as calling a CLR stored procedure or running a MERGE statement inline rather than through a generated procedure.
4. You are migrating away from the BimlFlex-generated PSA stored procedures to a custom ETL framework and need to redirect the PSA step to your own orchestration logic.
5. Replace the default PSA call with an Azure blob processing loop. Your TLOG files land in Azure Blob Storage and need iterative processing: copy blobs from a load container to stage, execute a stored procedure per batch of files, then move processed blobs to archive. Use PsaSqlCallOverride to replace the standard SQL call with a ForLoop containing ExecuteProcess tasks (calling a blob helper tool) and ExecuteSQL tasks, looping until no more files remain to process.

Prerequisites

• The object must have PSA (Persistent Staging) enabled in BimlFlex metadata.
• SSIS deployment model; this extension point does not apply to ADF or SQL-only ELT builds.
• You must handle precedenceConstraint input and OutputPathName output correctly to maintain control flow integrity.
• The replacement logic must fully handle the PSA load; the default generated procedure call will not execute.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to PsaSqlCallOverride.
3. Set the target attribute to the specific source object (e.g., SalesLT.Customer).
4. Define the complete replacement Execute SQL task.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="PsaSqlCallOverride" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="precedenceConstraint" type="String" #>

<#  var sourceObject = new ObjectViewModel(sourceTable, sourceTable.Connection.RelatedItem.IntegrationStage, "SRC");
    var targetObject = new ObjectViewModel(targetTable, targetTable.Connection.RelatedItem.IntegrationStage, "TGT"); #>
<ExecuteSQL Name="SQL - Custom PSA Load" ConnectionName="<#=targetObject.Connection.Name#>">
<#  if (!string.IsNullOrEmpty(precedenceConstraint)) {#>
    <PrecedenceConstraints>
        <Inputs>
            <Input OutputPathName="<#=precedenceConstraint #>" />
        </Inputs>
    </PrecedenceConstraints>
<#  } #>
    <Parameters>
        <Parameter Name="0" VariableName="User.ExecutionID" DataType="Int64" />
    </Parameters>
    <DirectInput>EXEC [psa].[stp_Custom_Customer] ?</DirectInput>
<#  CustomOutput.OutputPathName = @"SQL - Custom PSA Load.Output"; #>
</ExecuteSQL>

5. Build the project in BimlStudio and verify the SSIS package shows your custom task in place of the default PSA procedure call.

Example

Before -- the generated SSIS package calls the standard BimlFlex PSA stored procedure:

<ExecuteSQL Name="SQL - PSA Load Customer" ConnectionName="BFX_ODS">
    <Parameters>
        <Parameter Name="0" VariableName="User.ExecutionID" DataType="Int64" />
    </Parameters>
    <DirectInput>EXEC [psa].[stp_Customer] ?</DirectInput>
</ExecuteSQL>

After -- with the override applied, the entire task is replaced:

<ExecuteSQL Name="SQL - Custom PSA Load" ConnectionName="BFX_ODS">
    <Parameters>
        <Parameter Name="0" VariableName="User.ExecutionID" DataType="Int64" />
    </Parameters>
    <DirectInput>EXEC [psa].[stp_Custom_Customer] ?</DirectInput>
</ExecuteSQL>

Common Mistakes

Mistake	Symptom	Fix
Forgetting to set CustomOutput.OutputPathName, breaking the control flow chain.	Downstream tasks in the SSIS package cannot find their precedence constraint, causing build errors.	Always set CustomOutput.OutputPathName on the last task emitted by the override.
Using this override when PsaPreSqlCallProcess or PsaPostSqlCallProcess would suffice.	You take on full responsibility for the PSA load logic, increasing maintenance burden and risk of drift from framework updates.	Only use the override when the default procedure call is fundamentally incompatible; prefer pre/post extension points for additive changes.
Not passing the ExecutionID parameter to the replacement procedure.	The BimlFlex audit framework cannot track the PSA load execution, leaving gaps in the execution log.	Include the <Parameter> for User.ExecutionID and ensure your custom procedure accepts it.
Setting ObjectInherit = true without realizing it replaces the PSA call for every object in the batch.	All objects lose their default PSA load logic, likely causing failures for objects whose PSA procedures differ.	Target specific objects with the target attribute unless you truly need a batch-wide override.

Related Extension Points

• PsaPreSqlCallProcess / PsaPostSqlCallProcess -- preferred for additive logic before/after the PSA call; use the override only when these are insufficient.
• StgPreProcessSql / StgPostProcessSql -- for SQL-level injection inside the staging stored procedure body in ELT patterns.

Staging Post Source SQL

Configure SQL to be injected after the Source Query

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPostSourceSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
/* Add your SQL fragment here */

What It Does

Injects a SQL fragment into the generated staging stored procedure immediately after the source table join clause. This allows you to append additional filtering, sorting, or supplementary SQL logic that executes right after the source data has been read and joined. The injected SQL becomes part of the stored procedure body, so it runs within the same transaction context as the rest of the staging load.

Where It Fires

Consumed in Code/SqlTypeProvider.cs via GetStagingSqlExtensionPoints(). The SQL fragment is injected after the source table join in the generated staging stored procedure SQL. It operates at the stored procedure generation level, not at the SSIS package level.

When to Use It

1. You need to add an additional WHERE clause filter after the source join for SalesLT.Customer to exclude test accounts that should not flow into the BFX_STG staging layer. Use this instead of StgPreSourceSql when the filter must appear after the join rather than before it.
2. A UNION ALL with a secondary source query needs to be appended after the primary source join for SalesLT.SalesOrderHeader, combining data from two source tables into one staging target.
3. You want to add an ORDER BY hint or query hint (e.g., OPTION (MAXDOP 1)) after the source query to control execution plan behavior on AWLT_SRC.
4. Debugging requires a temporary SELECT into a log table to capture the intermediate result set after the source join before further staging operations proceed.
5. You need to inject a CROSS APPLY or additional join that enriches the source data with lookup values not modeled as a BimlFlex source object.

Prerequisites

• The source object (e.g., SalesLT.Customer) must exist in BimlFlex metadata with a staging target on BFX_STG.
• ELT/SQL-based staging pattern must be in use (stored procedures are generated).
• Familiarity with the generated stored procedure structure is recommended; preview the SQL output in BimlStudio before adding fragments.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to StgPostSourceSql.
3. Set the target attribute to the specific source object.
4. Write the SQL fragment to inject.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPostSourceSql" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#  CustomOutput.ObjectInherit = false; #>
WHERE src.[ModifiedDate] >= DATEADD(DAY, -7, GETDATE())

5. Build the project and review the generated stored procedure to confirm the fragment appears in the expected position.

Example

Before -- the generated stored procedure source query:

SELECT
    src.[CustomerID],
    src.[FirstName],
    src.[LastName],
    src.[ModifiedDate]
FROM [SalesLT].[Customer] src

After -- with the extension point applied, the filter is appended:

SELECT
    src.[CustomerID],
    src.[FirstName],
    src.[LastName],
    src.[ModifiedDate]
FROM [SalesLT].[Customer] src
WHERE src.[ModifiedDate] >= DATEADD(DAY, -7, GETDATE())

Common Mistakes

Mistake	Symptom	Fix
Adding a complete SELECT statement instead of a SQL fragment.	Syntax error in the generated stored procedure because two SELECT statements collide.	Provide only the SQL fragment (e.g., WHERE, ORDER BY, or a join clause) that appends to the existing query.
Using ObjectInherit = true unintentionally.	The filter is applied to every object in the batch, removing valid rows from unrelated tables.	Set CustomOutput.ObjectInherit = false and use the target attribute for specific objects.
Referencing column aliases that do not exist in the source query.	Runtime SQL error: "Invalid column name."	Check the generated stored procedure for the actual column aliases (typically the src alias) before writing the fragment.
Confusing this with StgPreSourceSql and placing the logic in the wrong position.	The SQL fragment appears before the source join when it should appear after, or vice versa, causing unexpected query results.	Use StgPostSourceSql for logic after the source join and StgPreSourceSql for logic before it.

Related Extension Points

• StgPreSourceSql -- injects SQL before the source join; use when your logic must precede the source query (e.g., setting variables or creating temp tables).
• StgPostStageSql -- injects SQL after the staging table operations; use for post-insert logic rather than post-source logic.
• StgPreProcessSql / StgPostProcessSql -- wrap the entire insert delta logic in the stored procedure; use for broader pre/post logic.

Staging Post Stage SQL

Configure SQL to be injected after the Stage Query

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPostStageSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
/* Add your SQL fragment here */

What It Does

Injects a SQL fragment into the generated staging stored procedure immediately after the staging table insert or merge operations complete. This lets you run post-staging logic such as updating row counts, running data quality checks against the newly staged data, or performing additional transformations on the staging table itself. The injected SQL executes within the same stored procedure transaction as the staging load.

Where It Fires

Consumed in Code/SqlTypeProvider.cs via GetStagingSqlExtensionPoints(). The SQL fragment is injected after the staging table operations (INSERT, MERGE, or UPDATE) in the generated staging stored procedure. It operates at the stored procedure generation level, not at the SSIS package level.

When to Use It

1. You need to update a data quality flag column on awlt.Customer in BFX_STG after rows have been staged, marking records that fail a business rule validation. Use this instead of StgPostProcessSql when the logic must run immediately after the stage operation but before the procedure's final cleanup steps.
2. A post-staging deduplication step must run on awlt.SalesOrderHeader in BFX_STG to remove duplicate rows introduced by overlapping extraction windows in the EXT_AWLT_SRC batch.
3. You want to collect and log row counts from the staging table into an audit table on BFX_STG immediately after the insert completes.
4. An index rebuild or statistics update on the staging table is needed right after bulk data has been loaded.
5. You need to apply a secondary transformation (e.g., trimming whitespace or standardizing phone number formats) on the staging table data that cannot be expressed through BimlFlex column mappings.

Prerequisites

• The source object (e.g., SalesLT.Customer) must exist in BimlFlex metadata with a staging target on BFX_STG.
• ELT/SQL-based staging pattern must be in use (stored procedures are generated).
• The staging table (e.g., awlt.Customer) must already exist; this extension point runs after data is written to it.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to StgPostStageSql.
3. Set the target attribute to the specific source object.
4. Write the SQL fragment to inject.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPostStageSql" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#  CustomOutput.ObjectInherit = false; #>
UPDATE awlt.Customer
SET [Phone] = REPLACE(REPLACE(REPLACE([Phone], '(', ''), ')', ''), '-', '')
WHERE [Phone] IS NOT NULL;

5. Build the project and review the generated stored procedure to confirm the fragment appears after the staging insert.

Example

Before -- the generated stored procedure ends the staging section with the insert:

INSERT INTO [awlt].[Customer] ([CustomerID], [FirstName], [LastName], [Phone])
SELECT [CustomerID], [FirstName], [LastName], [Phone]
FROM #Customer_tmp;

After -- with the extension point applied, the phone normalization runs immediately after:

INSERT INTO [awlt].[Customer] ([CustomerID], [FirstName], [LastName], [Phone])
SELECT [CustomerID], [FirstName], [LastName], [Phone]
FROM #Customer_tmp;

UPDATE awlt.Customer
SET [Phone] = REPLACE(REPLACE(REPLACE([Phone], '(', ''), ')', ''), '-', '')
WHERE [Phone] IS NOT NULL;

Common Mistakes

Mistake	Symptom	Fix
Referencing columns that do not exist on the staging table.	Runtime SQL error: "Invalid column name" when the stored procedure executes.	Check the staging table DDL or BimlFlex column metadata before writing the fragment.
Accidentally running the post-stage logic before the insert by using StgPreStageSql instead.	The UPDATE or validation runs against an empty or stale staging table.	Use StgPostStageSql (post) for logic that depends on newly staged data.
Using ObjectInherit = true when the SQL references table-specific columns.	The SQL fragment is applied to all objects, causing failures for tables that lack the referenced columns.	Set ObjectInherit = false and target specific objects, or use dynamic column references via BimlScript.
Writing SQL that conflicts with the stored procedure's transaction isolation.	Deadlocks or blocking during concurrent batch execution.	Ensure the fragment uses compatible locking hints and consider the transaction scope of the staging procedure.

Related Extension Points

• StgPreStageSql -- injects SQL before the staging insert/merge; use for pre-insert setup like truncation or temp table preparation.
• StgPostSourceSql -- injects SQL after the source join; use for source-level filtering rather than post-staging transformations.
• StgPostProcessSql -- injects SQL at the end of the entire staging stored procedure; use for final cleanup or notification logic.

Staging Pre Source SQL

Configure SQL to be injected before the Source Query

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPreSourceSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
/* Add your SQL fragment here */

What It Does

Injects a SQL fragment into the generated staging stored procedure immediately before the source table join clause. This allows you to insert preparatory SQL such as variable declarations, temporary table creation, SET options, or logging statements that must execute before the source data is queried. The injected SQL becomes part of the stored procedure body and runs within the same transaction context.

Where It Fires

Consumed in Code/SqlTypeProvider.cs via GetStagingSqlExtensionPoints(). The SQL fragment is injected before the source table join in the generated staging stored procedure SQL. It operates at the stored procedure generation level, not at the SSIS package level.

When to Use It

1. You need to declare a variable or create a temporary lookup table that will be referenced in the source query join for SalesLT.Customer on AWLT_SRC. Use this instead of StgPreProcessSql when the setup must appear immediately before the source query rather than at the very start of the procedure.
2. A SET NOCOUNT ON or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement needs to be applied just before the source read for SalesLT.SalesOrderHeader to avoid locking on the source database.
3. You need to populate a temp table with filter keys from BFX_ODS that will be used in a subsequent join within the source query of the EXT_AWLT_SRC batch.
4. A debug logging statement must capture the current timestamp and execution context immediately before the source query runs.
5. You want to insert a WAITFOR DELAY (for testing) or a conditional IF block that guards the source query based on a configuration value.

Prerequisites

• The source object (e.g., SalesLT.Customer) must exist in BimlFlex metadata with a staging target on BFX_STG.
• ELT/SQL-based staging pattern must be in use (stored procedures are generated).
• Familiarity with the generated stored procedure structure is recommended to ensure the fragment is syntactically compatible with the surrounding SQL.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to StgPreSourceSql.
3. Set the target attribute to the specific source object.
4. Write the SQL fragment to inject.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPreSourceSql" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#  CustomOutput.ObjectInherit = false; #>
DECLARE @CutoffDate DATETIME2 = DATEADD(DAY, -30, GETDATE());

5. Build the project and review the generated stored procedure to confirm the fragment appears before the source query.

Example

Before -- the generated stored procedure goes directly to the source query:

-- Source query
SELECT
    src.[CustomerID],
    src.[FirstName],
    src.[LastName]
FROM [SalesLT].[Customer] src

After -- with the extension point applied, the variable declaration precedes the source query:

DECLARE @CutoffDate DATETIME2 = DATEADD(DAY, -30, GETDATE());

-- Source query
SELECT
    src.[CustomerID],
    src.[FirstName],
    src.[LastName]
FROM [SalesLT].[Customer] src

Common Mistakes

Mistake	Symptom	Fix
Including a GO batch separator in the SQL fragment.	The stored procedure fails to compile because GO is not valid inside a procedure body.	Remove all GO statements; the fragment must be valid T-SQL within a stored procedure.
Declaring a variable here but referencing it in a StgPostSourceSql fragment that is out of scope.	Runtime error: "Must declare the scalar variable."	Ensure variables declared in the pre-source fragment are referenced only within the same scope of the generated procedure.
Confusing this with StgPreStageSql, which fires before the staging insert rather than before the source read.	The variable or temp table is created in the wrong position, and the source query cannot reference it.	Use StgPreSourceSql for pre-source-read logic and StgPreStageSql for pre-staging-insert logic.
Injecting SQL that changes the connection context (e.g., USE [OtherDB]).	The rest of the stored procedure operates against the wrong database, causing object-not-found errors.	Never change the database context; use fully qualified three-part names instead.

Related Extension Points

• StgPostSourceSql -- injects SQL after the source join; use together when you need both a variable declaration before and a filter clause after the source query.
• StgPreStageSql -- injects SQL before the staging insert/merge; use when the logic relates to the staging table rather than the source query.
• StgPreProcessSql -- injects SQL at the very start of the stored procedure; use for procedure-wide setup logic.

Staging Pre Stage SQL

Configure SQL to be injected before the Stage Query

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPreStageSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
/* Add your SQL fragment here */

What It Does

Injects a SQL fragment into the generated staging stored procedure immediately before the staging table insert or merge operations begin. This allows you to perform preparatory actions on the staging table such as truncating it, disabling triggers, creating temporary indexes, or setting up intermediate tables that the staging insert will depend on. The injected SQL runs within the stored procedure's transaction context, just before data moves from the source read into the staging target.

Where It Fires

Consumed in Code/SqlTypeProvider.cs via GetStagingSqlExtensionPoints(). The SQL fragment is injected before the staging table operations (INSERT, MERGE, or UPDATE) in the generated staging stored procedure. It operates at the stored procedure generation level, not at the SSIS package level.

When to Use It

1. You need to truncate the staging table awlt.Customer in BFX_STG before loading fresh data from SalesLT.Customer, bypassing the default incremental merge logic. Use this instead of StgPreSourceSql when the logic relates to the staging table rather than the source query.
2. A trigger on awlt.SalesOrderHeader in BFX_STG must be disabled before the bulk insert to avoid firing on every row during the staging load in the EXT_AWLT_SRC batch.
3. You want to create a temporary index on the staging table to improve merge performance for large delta loads.
4. An intermediate calculation table must be populated from BFX_ODS before the staging insert can reference it in a computed column expression.
5. A conditional check must verify that the staging table is in a clean state (e.g., no orphan records from a failed previous run) before the new insert begins.

Prerequisites

• The source object (e.g., SalesLT.Customer) must exist in BimlFlex metadata with a staging target on BFX_STG.
• ELT/SQL-based staging pattern must be in use (stored procedures are generated).
• The staging table (e.g., awlt.Customer) must already exist in the target database.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to StgPreStageSql.
3. Set the target attribute to the specific source object.
4. Write the SQL fragment to inject.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPreStageSql" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#  CustomOutput.ObjectInherit = false; #>
DISABLE TRIGGER [trg_Customer_Audit] ON [awlt].[Customer];

5. Build the project and review the generated stored procedure to confirm the fragment appears before the staging insert.

Example

Before -- the generated stored procedure moves directly from source read to staging insert:

-- (source query populates temp table above)

INSERT INTO [awlt].[Customer] ([CustomerID], [FirstName], [LastName])
SELECT [CustomerID], [FirstName], [LastName]
FROM #Customer_tmp;

After -- with the extension point applied, the trigger is disabled before the insert:

-- (source query populates temp table above)

DISABLE TRIGGER [trg_Customer_Audit] ON [awlt].[Customer];

INSERT INTO [awlt].[Customer] ([CustomerID], [FirstName], [LastName])
SELECT [CustomerID], [FirstName], [LastName]
FROM #Customer_tmp;

Common Mistakes

Mistake	Symptom	Fix
Disabling a trigger without re-enabling it in StgPostStageSql.	The trigger remains disabled after the load, missing audit or referential integrity events for subsequent operations.	Always pair DISABLE TRIGGER with a matching ENABLE TRIGGER in StgPostStageSql.
Truncating the staging table when the procedure uses a MERGE pattern.	The MERGE finds no target rows and converts all operations to INSERTs, potentially causing duplicate key violations on subsequent runs.	Only truncate when you are certain the load pattern is full-refresh INSERT, not incremental MERGE.
Confusing this with StgPreSourceSql, which fires before the source read.	Setup logic intended for the staging table runs too early, before the source data is available.	Use StgPreStageSql for staging-table preparation and StgPreSourceSql for source-query preparation.
Using ObjectInherit = true with table-specific DDL commands.	The DDL runs against every object's staging table, causing errors for tables that lack the referenced trigger or index.	Target specific objects with the target attribute.

Related Extension Points

• StgPostStageSql -- injects SQL after the staging insert/merge; use together for enable/disable trigger pairs or pre/post index management.
• StgPreSourceSql -- injects SQL before the source query; use when the preparation relates to the source read rather than the staging target.
• StgPreProcessSql -- injects SQL at the very start of the stored procedure; use for procedure-wide initialization.

Staging Temporary Table Post Source SQL

Configure SQL to be injected after the Source Temporary Table Query.

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgTempTablePostSourceSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
/* Add your SQL fragment here */

What It Does

Injects a SQL fragment into the temporary table SQL generation logic immediately after the source data has been loaded into the temporary table. This allows you to transform, cleanse, or augment data in the temp table before it is merged or inserted into the final staging target. The extension point supports token replacement: @@this resolves to the source/file table name, @@stage to the staging table name, and @@target to the target table name.

Where It Fires

Consumed in Code/StagingViewModel.cs via GetStagingObjectExtensionPoint(). The SQL fragment is injected after the source data has been loaded into the temporary table during the ELT temp table staging process. Token replacement is applied before the SQL is emitted into the stored procedure.

When to Use It

1. You need to apply data cleansing transformations (e.g., trimming whitespace, converting nulls to defaults) on the temporary table @@this after data from SalesLT.Customer on AWLT_SRC has been loaded into it but before it reaches awlt.Customer in BFX_STG. Use this instead of StgPostStageSql when the transformation must happen on the temp table rather than the final staging table.
2. A derived column calculation (e.g., concatenating first and last name) needs to be applied to the temp table holding SalesLT.SalesOrderHeader data before the merge into the staging target.
3. You want to delete rows from the temp table that fail business validation rules for the EXT_AWLT_SRC batch, preventing bad data from reaching the staging layer.
4. An additional lookup-based enrichment must be applied to the temp table (e.g., joining to a reference table on BFX_ODS to populate a region code column).
5. Row deduplication logic needs to run on the temp table before the staging insert to handle source systems that produce duplicate extraction records.

Prerequisites

• The staging pattern must use temporary tables (ELT temp table pattern enabled in BimlFlex settings).
• The source object (e.g., SalesLT.Customer) must exist in BimlFlex metadata with a staging target on BFX_STG.
• Token replacement (@@this, @@stage, @@target) is available; use these tokens for portable SQL that adapts to each object.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to StgTempTablePostSourceSql.
3. Set the target attribute to the specific source object.
4. Write the SQL fragment using token replacement where appropriate.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgTempTablePostSourceSql" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#  CustomOutput.ObjectInherit = false; #>
-- Trim whitespace from name columns in the temp table
UPDATE @@this
SET [FirstName] = LTRIM(RTRIM([FirstName])),
    [LastName]  = LTRIM(RTRIM([LastName]))
WHERE [FirstName] <> LTRIM(RTRIM([FirstName]))
   OR [LastName]  <> LTRIM(RTRIM([LastName]));

5. Build the project and review the generated stored procedure to confirm the fragment appears after the temp table load.

Example

Before -- the generated stored procedure loads the temp table and immediately merges into staging:

INSERT INTO #Customer_tmp ([CustomerID], [FirstName], [LastName])
SELECT [CustomerID], [FirstName], [LastName]
FROM [SalesLT].[Customer];

-- Merge into staging
MERGE [awlt].[Customer] AS tgt ...

After -- with the extension point applied, the cleansing step runs on the temp table:

INSERT INTO #Customer_tmp ([CustomerID], [FirstName], [LastName])
SELECT [CustomerID], [FirstName], [LastName]
FROM [SalesLT].[Customer];

-- Trim whitespace from name columns in the temp table
UPDATE #Customer_tmp
SET [FirstName] = LTRIM(RTRIM([FirstName])),
    [LastName]  = LTRIM(RTRIM([LastName]))
WHERE [FirstName] <> LTRIM(RTRIM([FirstName]))
   OR [LastName]  <> LTRIM(RTRIM([LastName]));

-- Merge into staging
MERGE [awlt].[Customer] AS tgt ...

Common Mistakes

Mistake	Symptom	Fix
Using the actual temp table name (e.g., #Customer_tmp) instead of the @@this token.	The SQL fragment works for one object but fails when ObjectInherit = true is used, because other objects have different temp table names.	Use @@this to let the framework resolve the correct temp table name for each object.
Referencing columns that do not exist in the temp table.	Runtime error: "Invalid column name" during stored procedure execution.	Verify the temp table schema matches the source object columns defined in BimlFlex metadata.
Running expensive transformations without a WHERE clause filter.	Full table scans on the temp table, significantly slowing down the staging load.	Add a WHERE clause to limit the update to rows that actually need transformation.
Confusing this with StgPostStageSql, which fires after the final staging insert.	Transformations run on the staging table instead of the temp table, affecting previously loaded data.	Use StgTempTablePostSourceSql for temp table transformations and StgPostStageSql for staging table transformations.

Related Extension Points

• StgTempTablePreSourceSql -- injects SQL before the temp table source load; use for setup logic like creating helper temp tables or declaring variables.
• StgTempTableSourceJoinSql -- injects a join clause into the temp table source query; use when you need to enrich the source read rather than transform after it.
• StgTempTableTargetJoinSql -- injects a join clause into the temp table target query; use for target-side join enrichment.
• StgTempTableTruncateSql -- overrides the temp table truncation statement; use when you need custom cleanup logic before the temp table is reloaded.

Staging Temporary Table Pre Source SQL

Configure SQL to be injected before the Source Temporary Table Query.

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgTempTablePreSourceSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
/* Add your SQL fragment here */

What It Does

Injects a SQL fragment into the temporary table SQL generation logic immediately before the source data is loaded into the temporary table. This allows you to run preparatory statements such as creating helper temp tables, declaring variables, setting session options, or inserting reference data that the subsequent source query will use. The extension point supports token replacement: @@this resolves to the source/file table name, @@stage to the staging table name, and @@target to the target table name.

Where It Fires

Consumed in Code/StagingViewModel.cs via GetStagingObjectExtensionPoint(). The SQL fragment is injected before the source data load into the temporary table during the ELT temp table staging process. Token replacement is applied before the SQL is emitted into the stored procedure.

When to Use It

1. You need to create a helper temporary table populated with filter keys from BFX_ODS that will be used in the subsequent temp table source query for SalesLT.Customer. Use this instead of StgPreSourceSql when you are working with the temp table ELT pattern specifically.
2. A session-level SET option (e.g., SET ANSI_WARNINGS OFF) must be applied before the source data from AWLT_SRC is loaded into the temp table for SalesLT.SalesOrderHeader.
3. A variable declaration is needed to hold a high-watermark date fetched from BFX_STG that the source query will reference as a filter in the EXT_AWLT_SRC batch.
4. You want to log the start of the temp table load to an audit table on BFX_ODS for monitoring purposes.
5. A conditional check must determine whether the temp table load should proceed at all, based on a flag or row count from a control table.

Prerequisites

• The staging pattern must use temporary tables (ELT temp table pattern enabled in BimlFlex settings).
• The source object (e.g., SalesLT.Customer) must exist in BimlFlex metadata with a staging target on BFX_STG.
• Token replacement (@@this, @@stage, @@target) is available; use these tokens for portable SQL.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to StgTempTablePreSourceSql.
3. Set the target attribute to the specific source object.
4. Write the SQL fragment using token replacement where appropriate.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgTempTablePreSourceSql" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#  CustomOutput.ObjectInherit = false; #>
-- Create a filter temp table with active customer IDs
SELECT [CustomerID] INTO #ActiveCustomers
FROM [BFX_ODS].[dbo].[ActiveCustomerList]
WHERE [IsActive] = 1;

5. Build the project and review the generated stored procedure to confirm the fragment appears before the temp table source load.

Example

Before -- the generated stored procedure loads the temp table directly from source:

TRUNCATE TABLE #Customer_tmp;

INSERT INTO #Customer_tmp ([CustomerID], [FirstName], [LastName])
SELECT [CustomerID], [FirstName], [LastName]
FROM [SalesLT].[Customer];

After -- with the extension point applied, the helper table is created first:

TRUNCATE TABLE #Customer_tmp;

-- Create a filter temp table with active customer IDs
SELECT [CustomerID] INTO #ActiveCustomers
FROM [BFX_ODS].[dbo].[ActiveCustomerList]
WHERE [IsActive] = 1;

INSERT INTO #Customer_tmp ([CustomerID], [FirstName], [LastName])
SELECT [CustomerID], [FirstName], [LastName]
FROM [SalesLT].[Customer];

Common Mistakes

Mistake	Symptom	Fix
Creating a temp table here but forgetting to drop it, causing conflicts on subsequent executions within the same session.	Runtime error: "There is already an object named '#ActiveCustomers'."	Add IF OBJECT_ID('tempdb..#ActiveCustomers') IS NOT NULL DROP TABLE #ActiveCustomers; before the SELECT INTO.
Using @@this in the pre-source context when it has not yet been populated.	The token resolves correctly (it is the table name, not the data), but the logic referencing data in @@this returns no rows because the temp table is still empty.	Remember that @@this is the table name token, not a data reference. At this point the temp table exists but has no data; use it only in DDL or metadata references.
Confusing this with StgTempTablePostSourceSql, which fires after the temp table is loaded.	Setup logic runs after the data is already in the temp table, missing the opportunity to prepare for the load.	Use StgTempTablePreSourceSql for pre-load setup and StgTempTablePostSourceSql for post-load transformations.
Including a GO batch separator.	Stored procedure compilation fails because GO is not valid inside a procedure body.	Remove all GO statements; the fragment must be valid T-SQL within a stored procedure.

Related Extension Points

• StgTempTablePostSourceSql -- injects SQL after the temp table source load; use together when you need both setup and post-load transformation.
• StgTempTableSourceJoinSql -- injects a join clause into the temp table source query; use when you need to enrich the source read with additional joins.
• StgTempTableTruncateSql -- overrides the temp table truncation statement; use when you need custom cleanup instead of the default TRUNCATE.

Staging Temporary Table Source Join SQL

Configure SQL to be injected as a Join clause into the first Source Temporary Table Source Query.

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgTempTableSourceJoinSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
/* Add your SQL fragment here */

What It Does

Injects a SQL join clause into the source query that populates the temporary table during ELT staging. This allows you to enrich the source data with additional columns from lookup or reference tables by adding JOIN clauses to the query that reads from the source system. The extension point supports token replacement: @@this resolves to the source/file table name, @@stage to the staging table name, and @@target to the target table name. The injected SQL is appended as a join clause to the first source temporary table source query.

Where It Fires

Consumed in Code/StagingViewModel.cs via GetStagingObjectExtensionPoint(). The SQL fragment is injected as a JOIN clause into the source query that populates the temporary table. Token replacement is applied before the SQL is emitted into the stored procedure.

When to Use It

1. You need to join SalesLT.Customer from AWLT_SRC with a reference table (e.g., SalesLT.CustomerAddress) during the source read to bring additional address columns into the temp table before it reaches awlt.Customer in BFX_STG. Use this instead of StgTempTablePostSourceSql when the enrichment should happen at query time rather than as a post-load UPDATE.
2. A lookup join against a code table on AWLT_SRC is needed to decode status codes for SalesLT.SalesOrderHeader during extraction in the EXT_AWLT_SRC batch.
3. You want to filter source rows by joining to an inclusion list table (e.g., only load customers that exist in an active-accounts table) at the source query level for better performance.
4. A slowly changing dimension lookup on BFX_ODS must be joined into the source query to resolve surrogate keys during staging.
5. An anti-join (LEFT JOIN ... WHERE ... IS NULL) is needed to exclude rows that already exist in the target, implementing custom delta detection logic.

Prerequisites

• The staging pattern must use temporary tables (ELT temp table pattern enabled in BimlFlex settings).
• The source object (e.g., SalesLT.Customer) must exist in BimlFlex metadata with a staging target on BFX_STG.
• The table being joined must be accessible from the connection used by the source query.
• Token replacement (@@this, @@stage, @@target) is available.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to StgTempTableSourceJoinSql.
3. Set the target attribute to the specific source object.
4. Write the JOIN clause to inject.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgTempTableSourceJoinSql" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#  CustomOutput.ObjectInherit = false; #>
INNER JOIN [SalesLT].[CustomerAddress] ca
    ON src.[CustomerID] = ca.[CustomerID]

5. Build the project and review the generated stored procedure to confirm the join clause appears in the temp table source query.

Example

Before -- the generated source query reads from a single table:

INSERT INTO #Customer_tmp ([CustomerID], [FirstName], [LastName])
SELECT src.[CustomerID], src.[FirstName], src.[LastName]
FROM [SalesLT].[Customer] src

After -- with the extension point applied, the join clause is added:

INSERT INTO #Customer_tmp ([CustomerID], [FirstName], [LastName], [AddressType])
SELECT src.[CustomerID], src.[FirstName], src.[LastName], ca.[AddressType]
FROM [SalesLT].[Customer] src
INNER JOIN [SalesLT].[CustomerAddress] ca
    ON src.[CustomerID] = ca.[CustomerID]

Common Mistakes

Mistake	Symptom	Fix
Writing a full SELECT statement instead of just a JOIN clause.	Syntax error in the generated stored procedure because two SELECT statements collide.	Provide only the JOIN ... ON ... clause that appends to the existing FROM clause.
Joining to a table that creates a one-to-many relationship, duplicating source rows.	The temp table contains more rows than expected, causing duplicate key violations during the staging merge.	Use INNER JOIN with a unique key, or use a subquery/CROSS APPLY with TOP 1 to prevent row multiplication.
Referencing the joined table's columns in the SELECT list without also adding them to the temp table DDL.	Runtime error: columns from the joined table have no target in the INSERT.	Ensure that any new columns from the join are also added to the temp table definition via column metadata or another extension point.
Using @@this where the source alias src is needed.	@@this resolves to the table name, not the alias, causing ambiguous column references.	Use the src alias for source table columns and the joined table's alias for its columns.

Related Extension Points

• StgTempTableTargetJoinSql -- injects a join clause into the temp table target query; use when the enrichment is needed on the target side rather than the source side.
• StgTempTablePreSourceSql -- injects SQL before the temp table source load; use when you need to create helper tables that the join will reference.
• StgTempTablePostSourceSql -- injects SQL after the temp table source load; use for post-load transformations when a query-time join is not feasible.

Staging Temporary Table Target Join SQL

Configure SQL to be injected as a Join clause into the first Source Temporary Table Target Query.

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgTempTableTargetJoinSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
/* Add your SQL fragment here */

What It Does

Injects a SQL join clause into the target query used during the temporary table staging process. This allows you to add additional JOIN clauses to the query that reads from the target (staging or destination) table, enabling scenarios such as conditional merges, lookups against the existing target data, or complex change detection logic. The extension point supports token replacement: @@this resolves to the source/file table name, @@stage to the staging table name, and @@target to the target table name.

Where It Fires

Consumed in Code/StagingViewModel.cs via GetStagingObjectExtensionPoint(). The SQL fragment is injected as a JOIN clause into the target query of the temporary table staging process. Token replacement is applied before the SQL is emitted into the stored procedure.

When to Use It

1. You need to join the target table query for awlt.Customer in BFX_STG with a reference table on BFX_ODS to implement custom change detection logic that goes beyond the default hash comparison. Use this instead of StgTempTableSourceJoinSql when the join must appear in the target-side query rather than the source-side query.
2. A lookup against a dimension table on BFX_ODS must be included in the target query for awlt.SalesOrderHeader to resolve surrogate keys during the merge operation in the EXT_AWLT_SRC batch.
3. You want to add an anti-join against an exclusion table to prevent certain existing target rows from participating in the merge.
4. A soft-delete pattern requires joining to an audit table to determine which target rows should be marked as deleted rather than physically removed.
5. Custom business key matching logic requires joining the target table to a mapping table that defines alternate key relationships.

Prerequisites

• The staging pattern must use temporary tables (ELT temp table pattern enabled in BimlFlex settings).
• The source object (e.g., SalesLT.Customer) must exist in BimlFlex metadata with a staging target on BFX_STG.
• The table being joined must be accessible from the target connection.
• Token replacement (@@this, @@stage, @@target) is available.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to StgTempTableTargetJoinSql.
3. Set the target attribute to the specific source object.
4. Write the JOIN clause to inject.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgTempTableTargetJoinSql" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#  CustomOutput.ObjectInherit = false; #>
LEFT JOIN [BFX_ODS].[dbo].[CustomerExclusion] excl
    ON tgt.[CustomerID] = excl.[CustomerID]

5. Build the project and review the generated stored procedure to confirm the join clause appears in the target query.

Example

Before -- the generated target query reads the staging table directly:

SELECT tgt.[CustomerID], tgt.[FirstName], tgt.[LastName], tgt.[FlexRowHash]
FROM [awlt].[Customer] tgt

After -- with the extension point applied, the exclusion join is added:

SELECT tgt.[CustomerID], tgt.[FirstName], tgt.[LastName], tgt.[FlexRowHash]
FROM [awlt].[Customer] tgt
LEFT JOIN [BFX_ODS].[dbo].[CustomerExclusion] excl
    ON tgt.[CustomerID] = excl.[CustomerID]

Common Mistakes

Mistake	Symptom	Fix
Writing a full SELECT or WHERE clause instead of just a JOIN clause.	Syntax error in the generated stored procedure.	Provide only the JOIN ... ON ... clause that appends to the existing FROM clause.
Using the wrong alias for the target table.	Runtime error: "Invalid column name" or ambiguous column reference.	Check the generated stored procedure for the actual target table alias (typically tgt) before writing the join.
Joining to a table on a different server without a linked server or cross-database reference.	Connection error at runtime.	Ensure the joined table is accessible from the target connection, using fully qualified names or linked server syntax as needed.
Confusing this with StgTempTableSourceJoinSql, which injects into the source query.	The join appears in the source read instead of the target query, producing incorrect results.	Use StgTempTableTargetJoinSql for target-side joins and StgTempTableSourceJoinSql for source-side joins.

Related Extension Points

• StgTempTableSourceJoinSql -- injects a join clause into the source query; use when enrichment should happen during the source read rather than the target read.
• StgTempTablePostSourceSql -- injects SQL after the temp table source load; use for post-load transformations that cannot be expressed as a join.
• StgTempTablePreSourceSql -- injects SQL before the temp table source load; use for setup logic needed before either source or target queries run.

Staging Temporary Table Truncate SQL

Specify a SQL statement that will override the TRUNCATE used in the Source Temporary Table Query for a given target Object.

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the (source) object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the (target) object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgTempTableTruncateSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
/* Add your SQL fragment here */

What It Does

Overrides the default TRUNCATE TABLE statement that clears the temporary table before it is reloaded with source data. By default, BimlFlex generates a TRUNCATE TABLE for the temp table at the start of each load cycle. This extension point lets you replace that truncation with custom cleanup logic, such as a conditional delete, a filtered truncation, or an alternative reset strategy. The extension point supports token replacement: @@this resolves to the source/file table name, @@stage to the staging table name, and @@target to the target table name.

Where It Fires

Consumed in Code/StagingViewModel.cs via GetStagingObjectExtensionPoint(). The SQL fragment replaces the default TRUNCATE TABLE statement for the temporary table in the generated stored procedure. Token replacement is applied before the SQL is emitted.

When to Use It

1. You need to replace the default TRUNCATE TABLE for awlt.Customer temp table in BFX_STG with a DELETE statement that preserves certain rows (e.g., rows flagged for reprocessing) across load cycles. Use this instead of StgTempTablePreSourceSql when you specifically need to override the truncation rather than add logic before the source load.
2. The temp table for SalesLT.SalesOrderHeader has foreign key constraints that prevent truncation, requiring a DELETE FROM instead in the EXT_AWLT_SRC batch.
3. You want to archive the current temp table contents to a history table on BFX_ODS before clearing it, implementing a rolling snapshot pattern.
4. A conditional cleanup is needed: only truncate the temp table if a control flag in a configuration table is set, otherwise preserve the existing data for debugging.
5. The temp table cleanup requires additional steps such as resetting identity columns or rebuilding indexes after the truncation.

Prerequisites

• The staging pattern must use temporary tables (ELT temp table pattern enabled in BimlFlex settings).
• The source object (e.g., SalesLT.Customer) must exist in BimlFlex metadata with a staging target on BFX_STG.
• Token replacement (@@this, @@stage, @@target) is available.
• This extension point is an override, not an additive injection. The default TRUNCATE TABLE will not execute when this extension point is active.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to StgTempTableTruncateSql.
3. Set the target attribute to the specific source object.
4. Write the replacement SQL.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgTempTableTruncateSql" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#  CustomOutput.ObjectInherit = false; #>
-- Archive before clearing
INSERT INTO [audit].[Customer_TempSnapshot] SELECT * FROM @@this;
DELETE FROM @@this;

5. Build the project and review the generated stored procedure to confirm the default TRUNCATE has been replaced with your custom logic.

Example

Before -- the generated stored procedure uses the default truncation:

TRUNCATE TABLE #Customer_tmp;

After -- with the extension point applied, the archive-and-delete pattern replaces the truncation:

-- Archive before clearing
INSERT INTO [audit].[Customer_TempSnapshot] SELECT * FROM #Customer_tmp;
DELETE FROM #Customer_tmp;

Common Mistakes

Mistake	Symptom	Fix
Forgetting that this is an override, and expecting the default TRUNCATE to still run.	The temp table is not cleared, and old data mixes with new data, producing duplicate or stale rows in staging.	Ensure your replacement SQL actually clears the temp table (e.g., DELETE FROM @@this).
Using TRUNCATE TABLE on a temp table that has foreign key constraints.	Runtime error: "Cannot truncate table because it is being referenced by a FOREIGN KEY constraint."	Use DELETE FROM @@this instead of TRUNCATE TABLE @@this when constraints exist.
Hardcoding the temp table name instead of using @@this.	The override works for one object but fails when applied to others via ObjectInherit = true.	Use the @@this token to dynamically resolve the correct temp table name.
Omitting the cleanup entirely (empty extension point body).	The temp table retains data from the previous load, causing the staging merge to process stale rows.	Always include a DELETE or TRUNCATE statement to clear the temp table.

Related Extension Points

• StgTempTablePreSourceSql -- injects SQL before the temp table source load (after truncation); use together when you need both custom truncation and pre-load setup.
• StgTempTablePostSourceSql -- injects SQL after the temp table source load; use for post-load transformations after the table has been repopulated.
• StgTempTableSourceJoinSql / StgTempTableTargetJoinSql -- inject join clauses into the source or target queries; these fire during the load itself, after the truncation has completed.

Staging Pre Process SQL

Configure SQL to be injected at the start of the Staging stored procedure

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPreProcessSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
/* Add your SQL fragment here */

What It Does

Injects a SQL fragment at the very beginning of the staging stored procedure body, before any source reading or staging operations occur. This is the earliest injection point within the stored procedure and is ideal for procedure-wide initialization such as setting transaction isolation levels, declaring variables, configuring session options, or logging the start of execution. The injected SQL wraps around the insert delta logic along with StgPostProcessSql, forming the outermost SQL injection layer in the stored procedure.

Where It Fires

Consumed in Code/SqlTypeProvider.cs via GetObjectExtensionPoint(). The SQL fragment is injected at the start of the staging stored procedure body, before the insert delta logic begins. It operates at the stored procedure generation level, wrapping around all source and staging operations.

When to Use It

1. You need to set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the start of the staging procedure for SalesLT.Customer to avoid locking the source database AWLT_SRC during extraction. Use this instead of StgPreSourceSql when the setting must apply to the entire procedure, not just the source query section.
2. A procedure-level variable must be declared at the top of the staging procedure for SalesLT.SalesOrderHeader that will be referenced in multiple downstream sections (source query, staging insert, and post-processing).
3. You want to log the start of the staging procedure execution to an audit table on BFX_ODS for the EXT_AWLT_SRC batch, recording the execution ID and start timestamp.
4. A BEGIN TRY ... BEGIN CATCH wrapper must start at the top of the procedure to implement custom error handling around all staging operations.
5. Session options like SET NOCOUNT ON or SET XACT_ABORT ON need to be applied before any data operations begin.

Prerequisites

• The source object (e.g., SalesLT.Customer) must exist in BimlFlex metadata with a staging target on BFX_STG.
• ELT/SQL-based staging pattern must be in use (stored procedures are generated).
• If using a BEGIN TRY block, ensure StgPostProcessSql contains the matching END TRY ... BEGIN CATCH ... END CATCH.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to StgPreProcessSql.
3. Set the target attribute to the specific source object, or use ObjectInherit = true for all objects.
4. Write the SQL fragment to inject at the start of the procedure.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPreProcessSql" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#  CustomOutput.ObjectInherit = false; #>
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;

INSERT INTO [audit].[ProcedureLog] ([ProcedureName], [StartTime], [ExecutionID])
VALUES ('stp_Customer', GETDATE(), @ExecutionID);

5. Build the project and review the generated stored procedure to confirm the fragment appears at the very start of the procedure body.

Example

Before -- the generated stored procedure begins directly with delta detection logic:

CREATE PROCEDURE [awlt].[stp_Customer] @ExecutionID BIGINT
AS
BEGIN
    -- Delta detection and source query
    SELECT src.[CustomerID], src.[FirstName], src.[LastName]
    FROM [SalesLT].[Customer] src
    ...

After -- with the extension point applied, initialization logic precedes all operations:

CREATE PROCEDURE [awlt].[stp_Customer] @ExecutionID BIGINT
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET NOCOUNT ON;

    INSERT INTO [audit].[ProcedureLog] ([ProcedureName], [StartTime], [ExecutionID])
    VALUES ('stp_Customer', GETDATE(), @ExecutionID);

    -- Delta detection and source query
    SELECT src.[CustomerID], src.[FirstName], src.[LastName]
    FROM [SalesLT].[Customer] src
    ...

Common Mistakes

Mistake	Symptom	Fix
Opening a BEGIN TRY block without a matching END TRY ... BEGIN CATCH in StgPostProcessSql.	Stored procedure compilation fails with a syntax error about unmatched TRY/CATCH.	Always pair BEGIN TRY in StgPreProcessSql with END TRY BEGIN CATCH ... END CATCH in StgPostProcessSql.
Setting isolation level to SERIALIZABLE without understanding the locking implications.	Deadlocks and blocking across concurrent staging loads.	Use READ UNCOMMITTED or SNAPSHOT for read-heavy staging loads; reserve SERIALIZABLE for rare cases requiring strict consistency.
Including a GO batch separator in the SQL fragment.	Stored procedure compilation fails because GO is not valid inside a procedure body.	Remove all GO statements.
Confusing this with StgPreSourceSql or StgPreStageSql, which fire at more granular positions.	Initialization logic fires at the wrong point in the procedure.	Use StgPreProcessSql for procedure-wide initialization, StgPreSourceSql for pre-source-query logic, and StgPreStageSql for pre-staging-insert logic.

Related Extension Points

• StgPostProcessSql -- injects SQL at the end of the staging stored procedure; use as the closing counterpart to wrap all operations (e.g., TRY/CATCH pairs, final audit logging).
• StgPreSourceSql -- injects SQL before the source query specifically; use for source-scoped preparation rather than procedure-wide initialization.
• StgPreStageSql -- injects SQL before the staging insert/merge; use for staging-table-scoped preparation.

Staging Post Process SQL

Configure SQL to be injected at the end of the Staging stored procedure

Parameters

Name	Type	Description
sourceTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the object to which the pipeline will be added
targetTable	BimlFlexModelWrapper.ObjectsWrapper	Contains all information related to the target object to which the pipeline will be added

Outputs

Name	Type	Description
ObjectInherit	Boolean	If CustomOutput.ObjectInherit = true then the Extension Point will be applied to all the Objects associated with the batch.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPostProcessSql" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>


<# 	CustomOutput.ObjectInherit = false; #>
/* Add your SQL fragment here */

What It Does

Injects a SQL fragment at the very end of the staging stored procedure body, after all source reading, staging, and delta processing operations have completed. This is the latest injection point within the stored procedure and is ideal for final cleanup, audit logging, notification logic, or closing a TRY/CATCH block opened by StgPreProcessSql. The injected SQL wraps around the insert delta logic along with StgPreProcessSql, forming the outermost SQL injection layer in the stored procedure.

Where It Fires

Consumed in Code/SqlTypeProvider.cs via GetObjectExtensionPoint(). The SQL fragment is injected at the end of the staging stored procedure body, after the insert delta logic completes. It operates at the stored procedure generation level, following all source and staging operations.

When to Use It

1. You need to log the completion time and row count of the staging load for SalesLT.Customer into an audit table on BFX_ODS, recording the final status of the EXT_AWLT_SRC batch execution. Use this instead of StgPostStageSql when the logic must run after all staging operations, not just after the staging insert.
2. A END TRY BEGIN CATCH ... END CATCH block must close the error-handling wrapper that was opened by StgPreProcessSql at the start of the procedure.
3. You want to send a notification (e.g., insert into a Service Broker queue or write to an event log table) indicating that the staging procedure for SalesLT.SalesOrderHeader has completed successfully.
4. Session-level cleanup such as resetting the transaction isolation level or re-enabling ANSI_WARNINGS is needed at the end of the procedure.
5. A final data quality summary must be calculated and written to a reporting table on BFX_STG, counting the number of inserted, updated, and rejected rows.

Prerequisites

• The source object (e.g., SalesLT.Customer) must exist in BimlFlex metadata with a staging target on BFX_STG.
• ELT/SQL-based staging pattern must be in use (stored procedures are generated).
• If closing a TRY/CATCH block, ensure StgPreProcessSql contains the matching BEGIN TRY.

Implementation Steps

1. Open the BimlFlex App or BimlStudio and navigate to the Extension Points editor.
2. Create a new Extension Point and set its type to StgPostProcessSql.
3. Set the target attribute to the specific source object, or use ObjectInherit = true for all objects.
4. Write the SQL fragment to inject at the end of the procedure.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="StgPostProcessSql" target="SalesLT.Customer" #>
<#@ property name="sourceTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>
<#@ property name="targetTable" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<#  CustomOutput.ObjectInherit = false; #>
UPDATE [audit].[ProcedureLog]
SET [EndTime] = GETDATE(),
    [RowCount] = @@ROWCOUNT,
    [Status] = 'Success'
WHERE [ProcedureName] = 'stp_Customer'
  AND [ExecutionID] = @ExecutionID;

5. Build the project and review the generated stored procedure to confirm the fragment appears at the end of the procedure body.

Example

Before -- the generated stored procedure ends after the staging operations:

    ...
    INSERT INTO [awlt].[Customer] ([CustomerID], [FirstName], [LastName])
    SELECT [CustomerID], [FirstName], [LastName]
    FROM #Customer_tmp;
END

After -- with the extension point applied, audit logging appears at the end:

    ...
    INSERT INTO [awlt].[Customer] ([CustomerID], [FirstName], [LastName])
    SELECT [CustomerID], [FirstName], [LastName]
    FROM #Customer_tmp;

    UPDATE [audit].[ProcedureLog]
    SET [EndTime] = GETDATE(),
        [RowCount] = @@ROWCOUNT,
        [Status] = 'Success'
    WHERE [ProcedureName] = 'stp_Customer'
      AND [ExecutionID] = @ExecutionID;
END

Common Mistakes

Mistake	Symptom	Fix
Closing a CATCH block without a matching BEGIN TRY in StgPreProcessSql.	Stored procedure compilation fails with a syntax error about unmatched TRY/CATCH.	Always pair END TRY BEGIN CATCH ... END CATCH here with BEGIN TRY in StgPreProcessSql.
Using @@ROWCOUNT after multiple statements, losing the original row count from the staging insert.	The audit log records the row count from the most recent statement (the UPDATE itself), not the staging insert.	Capture @@ROWCOUNT into a variable immediately after the staging insert using StgPostStageSql, then reference that variable here.
Including a GO batch separator.	Stored procedure compilation fails.	Remove all GO statements; the fragment must be valid T-SQL within a stored procedure body.
Raising an error in the post-process SQL that rolls back the entire staging transaction.	Successfully staged data is lost because the error causes a transaction rollback.	Use BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH within the fragment to handle errors gracefully without affecting the staging transaction.
Confusing this with PsaPostSqlCallProcess, which fires after the PSA procedure call in SSIS.	The logic runs inside the stored procedure rather than as a separate SSIS task, or vice versa.	Use StgPostProcessSql for SQL-level injection in the stored procedure and PsaPostSqlCallProcess for SSIS package-level tasks.

Related Extension Points

• StgPreProcessSql -- injects SQL at the start of the staging stored procedure; use as the opening counterpart for TRY/CATCH wrappers or initialization logic.
• StgPostStageSql -- injects SQL immediately after the staging insert/merge; use for post-insert logic that must run before the procedure's final steps.
• StgPostSourceSql -- injects SQL after the source query; use for source-scoped logic rather than end-of-procedure logic.