Connection Extension Points

The Connection category has the following available Extension Points defined.

Connection Expression

Configure Expressions for a Connection. This only applies to SSIS deployments.

Parameters

Name	Type	Description
connection	BimlFlexModelWrapper.ConnectionsWrapper	Contains all information related to the connection to which the connection expression will be added.

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="ConnectionExpression" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>


<!-- The below example is configuring an expression to an ODBC connection. Note that we are referencing Project Parameters that also need to be configured.-->
<Expressions>
	<Expression ExternalProperty="ConnectionString">"Dsn=SRC_ODBC;Uid=" + @[$Project::UserName] + ";Pwd=" + @[$Project::UserPassword] + ";"</Expression>
</Expressions>

What It Does

Injects SSIS expression language into a connection's properties at build time, allowing connection strings and other connection attributes to be driven by project parameters or variables at runtime. This is the primary mechanism for making SSIS packages environment-aware — the same package can connect to different servers in dev, test, and production without rebuilding.

Where It Fires

This is the most widely referenced extension point in BimlFlex, consumed by 41 source files. It follows a two-phase pattern:

1. Collection phase (0.00.1-flx-import-environment.biml): During environment import, BimlFlex iterates all connections and caches any ConnectionExpression extension point output in RootNode.ObjectTag under the key {ConnectionName}_ConnectionExpression.
2. Injection phase (90.flx-i-get-pkg-connections.biml, 90.flx-i-get-pkg-batch.biml, and ~38 other package-generation files): When generating SSIS packages, the cached expression text is retrieved and injected into the <Expressions> element of the connection definition.

The extension point fires once per connection per build. The target attribute in the extension point directive determines which connection it applies to.

When to Use It

1. Parameterise connection strings for SSIS deployment across environments. Your AWLT_SRC connection uses integrated security in dev but SQL authentication in production. Use ConnectionExpression to build the connection string from @[$Project::AWLT_SRC_ServerName] and @[$Project::AWLT_SRC_Database] parameters.
2. Connect to ODBC or OLE DB sources that require runtime credentials. The AWLT_SRC source uses an ODBC DSN and the username/password must come from project parameters rather than being hard-coded in metadata.
3. Override the connection string for a flat-file connection. Your file source connection needs a dynamic file path based on a variable (e.g., today's date folder).
4. Use this instead of editing the connection string directly in BimlFlex metadata when you need expression-based logic (string concatenation, parameter references) that cannot be represented as a static string.
5. Environment-specific catalog connections. Your BimlCatalog connection must point to different servers per environment using @[$Project::BimlCatalog_ServerName] and @[$Project::BimlCatalog_CatalogName].
6. Multi-target batch with shared connection parameters. Multiple batches (e.g., LOAD_PSA_TLOGS_COT, LOAD_PSA_TLOGS_CT) each need the same ConnectionString expression pointing to a shared PSA database. Define one ConnectionExpression targeting all relevant connection names (comma-separated targets) referencing a common @[$Project::PSA_ConnectionString] parameter.
7. Azure SQL Database with runtime password injection. Your cloud-hosted staging connection (BFX_STG) connects to bimlflex.database.windows.net. Use ConnectionExpression to build the connection string with @[$Project::UserPassword] so credentials are never stored in metadata: "Data Source=bimlflex.database.windows.net,1433;Initial Catalog=BimlFlex;Provider=SQLNCLI11.1;User ID=bfx_user;Password=" + @[$Project::UserPassword] + ";".

Prerequisites

• Object types: Applies to any BimlFlex Connection object
• Deployment target: SSIS only — this extension point has no effect on ADF or Databricks deployments
• Required metadata: The project parameters referenced in the expression (e.g., AWLT_SRC_ServerName) must exist. Use the ProjectParameter extension point or configure parameters in BimlFlex metadata
• Related extension points: Often paired with ProjectParameter to define the parameters that the expression references

Implementation Steps

1. In BimlFlex, open the Extension Points editor.
2. Create a new extension point file. Select ConnectionExpression as the type.
3. Set the target attribute to the connection name (e.g., AWLT_SRC).
4. Define the <Expressions> block with one or more <Expression> elements.
5. Reference project parameters using SSIS expression syntax: @[$Project::ParameterName].
6. Build the project to verify the expression is injected into the generated connection.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="ConnectionExpression" target="AWLT_SRC" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>

<Expressions>
	<Expression ExternalProperty="ConnectionString">"Data Source=" + @[$Project::AWLT_SRC_ServerName] + ";Initial Catalog=" + @[$Project::AWLT_SRC_Database] + ";Provider=SQLNCLI11;Integrated Security=SSPI;"</Expression>
</Expressions>

Example

Before (default — static connection string from metadata):

<OleDbConnection Name="AWLT_SRC" ConnectionString="Data Source=localhost;Initial Catalog=AdventureWorksLT;Provider=SQLNCLI11;Integrated Security=SSPI;" />

After (ConnectionExpression applied):

<OleDbConnection Name="AWLT_SRC" ConnectionString="Data Source=localhost;Initial Catalog=AdventureWorksLT;Provider=SQLNCLI11;Integrated Security=SSPI;">
	<Expressions>
		<Expression ExternalProperty="ConnectionString">"Data Source=" + @[$Project::AWLT_SRC_ServerName] + ";Initial Catalog=" + @[$Project::AWLT_SRC_Database] + ";Provider=SQLNCLI11;Integrated Security=SSPI;"</Expression>
	</Expressions>
</OleDbConnection>

Common Mistakes

• Mistake: Forgetting to create the project parameters referenced in the expression. Symptom: SSIS package fails validation with "variable not found" errors. Fix: Add matching parameters via the ProjectParameter extension point or in BimlFlex metadata before building.
• Mistake: Using C# string syntax instead of SSIS expression syntax (e.g., $"Data Source={server}" instead of "Data Source=" + @[$Project::Server]). Symptom: Build error or runtime expression evaluation failure. Fix: Use SSIS expression syntax with + concatenation and @[$Project::Name] references.
• Mistake: Omitting the target attribute, causing the expression to apply to all connections. Symptom: Multiple connections get the same expression, causing packages to connect to the wrong server. Fix: Always set target="ConnectionName" to scope the expression to a single connection.
• Mistake: Using ConnectionExpression for an ADF project. Symptom: The extension point is silently ignored — the ADF pipeline uses the connection string from metadata. Fix: For ADF deployments, use AdfLinkedService or OverrideAdfLinkedService instead.

Related Extension Points

• ProjectParameter — Define the project parameters that ConnectionExpression references
• ConnectionOverride — Replace the entire connection definition (rather than just adding expressions)
• BatchConnection — Add additional connections to a batch-level package

Connection Override

Configure a custom override for a Connection. This only applies to SSIS deployments. Using this Extension Point, a Connection can be (re)configured by using any BimlScript compatible code.

Parameters

Name	Type	Description
connection	BimlFlexModelWrapper.ConnectionsWrapper	Contains all information related to the connection to which the connection expression will be added

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="ConnectionOverride" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>


<!-- The below example is configuring an ODATA connection. -->
<CustomSsisConnection
	Name="Northwind"
	CreationName="ODATA"
	ObjectData="&lt;ODataConnectionManager UserName=&quot;&quot; Url=&quot;https://services.odata.org/V3/Northwind/Northwind.svc&quot; ConnectionString=&quot;Service Document Url=https://services.odata.org/V3/Northwind/Northwind.svc;&quot; MicrosoftOnlineServicesAuth=&quot;False&quot; AuthType=&quot;WindowsAuthentication&quot; /&gt;" />

What It Does

Completely replaces the default connection definition that BimlFlex generates for an SSIS package. Instead of the standard OLE DB or ADO.NET connection, you can inject any SSIS connection type — ODATA, SMTP, MSMQ, or any custom connection manager — using raw Biml markup. This gives full control over the connection XML emitted into the generated SSIS package.

Where It Fires

Registered in bundle.common.manifest under the Connection category with DesignerBimlPath="Biml/Connections". This extension point is not consumed by any framework BimlScript files — it is a pure injection hook. When defined, its output replaces the standard connection element in the generated SSIS package's connection manager section.

When to Use It

1. Connect to an ODATA service. Your AWLT_SRC source is a REST API exposed as ODATA, and BimlFlex's standard connection types do not support ODATA natively. Use ConnectionOverride to inject a CustomSsisConnection with the ODATA connection manager.
2. Connect to a non-standard SSIS data source such as SMTP, FTP, or a third-party connection manager that BimlFlex metadata cannot represent.
3. Override connection manager properties that are not exposed through BimlFlex metadata, such as RetainSameConnection or custom ObjectData attributes.
4. Use this instead of ConnectionExpression when you need to replace the entire connection definition rather than just adding expressions to the existing one. ConnectionExpression adds <Expressions> to the default connection; ConnectionOverride replaces the connection entirely.

Prerequisites

• Object types: Applies to any BimlFlex Connection object
• Deployment target: SSIS only — has no effect on ADF or Databricks deployments
• Required metadata: A connection must be defined in BimlFlex metadata. The override replaces the generated output for that connection
• Related extension points: May be combined with ConnectionExpression if the custom connection also needs dynamic expressions

Implementation Steps

1. In BimlFlex, open the Extension Points editor.
2. Create a new extension point file. Select ConnectionOverride as the type.
3. Set the target attribute to the connection name you want to override.
4. Write the complete connection Biml element (e.g., <CustomSsisConnection>, <OleDbConnection>, or <AdoNetConnection>).
5. Build and inspect the generated SSIS package to verify the connection was replaced.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="ConnectionOverride" target="AWLT_SRC" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>

<CustomSsisConnection
	Name="AWLT_SRC"
	CreationName="ODATA"
	ObjectData="&lt;ODataConnectionManager UserName=&quot;&quot; Url=&quot;https://api.adventureworks.com/odata/v4&quot; ConnectionString=&quot;Service Document Url=https://api.adventureworks.com/odata/v4;&quot; MicrosoftOnlineServicesAuth=&quot;False&quot; AuthType=&quot;WindowsAuthentication&quot; /&gt;" />

Example

Before (default — OLE DB connection from metadata):

<OleDbConnection Name="AWLT_SRC" ConnectionString="Data Source=localhost;Initial Catalog=AdventureWorksLT;Provider=SQLNCLI11;Integrated Security=SSPI;" />

After (ConnectionOverride applied — ODATA connection):

<CustomSsisConnection
	Name="AWLT_SRC"
	CreationName="ODATA"
	ObjectData="&lt;ODataConnectionManager UserName=&quot;&quot; Url=&quot;https://api.adventureworks.com/odata/v4&quot; ..." />

Common Mistakes

• Mistake: Using ConnectionOverride when you only need to parameterise an existing connection. Symptom: The entire connection definition is replaced, losing default settings like RetainSameConnection or connection pooling. Fix: Use ConnectionExpression to add expressions to the default connection instead.
• Mistake: Forgetting to HTML-encode the ObjectData attribute value. Symptom: Build error due to malformed XML. Fix: Replace " with " and < with < inside ObjectData.
• Mistake: Setting the Name in the override to a different value than the connection name in metadata. Symptom: Packages reference a connection that does not exist, causing validation failures. Fix: Ensure the Name attribute matches the BimlFlex connection name exactly.

Related Extension Points

• ConnectionExpression — Add expressions to the default connection (less invasive than a full override)
• ProjectParameter — Define parameters that the overridden connection might reference
• PackageAddConnection — Add an additional connection to a specific package rather than overriding an existing one

Database Metadata

Configure database-level metadata such as filegroups, partition functions, and partition schemes for a database connection. This extension point injects structural database properties into the generated SSDT project or database creation scripts.

Parameters

Name	Type	Description
connection	BimlFlexModelWrapper.ConnectionsWrapper	Contains all information related to the connection to which the database metadata will be added

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DatabaseMetadata" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>

<FileGroups>
	<FileGroup Name="FG_Current">
		<Files>
			<File Name="<#=connection.Name#>_FG_Current" FilePath="D:\SQLData\<#=connection.Name#>_FG_Current.ndf" />
		</Files>
	</FileGroup>
	<FileGroup Name="FG_Archive">
		<Files>
			<File Name="<#=connection.Name#>_FG_Archive" FilePath="D:\SQLData\<#=connection.Name#>_FG_Archive.ndf" />
		</Files>
	</FileGroup>
</FileGroups>
<PartitionFunctions>
	<PartitionFunction Name="PF_FlexRowEffectiveDate" DataType="DateTime" BoundarySide="Right">
		<BoundaryValues>
			<BoundaryValue>2024-01-01</BoundaryValue>
			<BoundaryValue>2025-01-01</BoundaryValue>
		</BoundaryValues>
	</PartitionFunction>
</PartitionFunctions>
<PartitionSchemes>
	<PartitionScheme Name="PS_FlexRowEffectiveDate" PartitionFunctionName="PF_FlexRowEffectiveDate">
		<Filegroups>
			<Filegroup>FG_Current</Filegroup>
			<Filegroup>FG_Archive</Filegroup>
		</Filegroups>
	</PartitionScheme>
</PartitionSchemes>

What It Does

Adds database-level structural metadata — filegroups, partition functions, and partition schemes — to the database definition generated by BimlFlex. This controls how SQL Server physically organises data files and distributes table data across partitions. The output is included in the SSDT project or database creation script for the targeted connection.

Where It Fires

Registered in bundle.common.manifest under the Connection category. A reference implementation exists at BimlScripts/ExtensionPoints/BFX_ODS_DatabaseMetadata.biml, which targets the BFX_ODS (Persistent Staging Area) connection and defines year-based partitioning with filegroups for 2021-2025 plus an archive partition.

When to Use It

1. Partition the Persistent Staging Area by date. Your BFX_ODS database stores years of historical data in satellite tables. Use DatabaseMetadata to define a partition function on FlexRowEffectiveDate and map yearly filegroups, so queries against recent data scan fewer partitions.
2. Add filegroups to the Data Vault database. Your BFX_DV database needs separate filegroups for Hubs, Links, and Satellites to spread I/O across different disk arrays.
3. Configure partitioning for large fact tables in the Data Mart. Your BFX_DM database has a fact.SalesOrder table that grows by millions of rows per month. Define a monthly partition scheme to enable partition switching for archive and load operations.
4. Use this instead of DatabaseProperties when you need to define filegroups and partitions. DatabaseProperties controls database-level settings (collation, ANSI). DatabaseMetadata controls physical storage structure (filegroups, partitions).
5. Add FILESTREAM filegroups. Your staging database needs a FILESTREAM filegroup for large binary objects arriving from the source system.

Prerequisites

• Object types: Applies to any BimlFlex Connection that represents a SQL Server database
• Deployment target: SQL Server / SSDT projects. Not applicable to Snowflake, Databricks, or Azure Synapse serverless
• Required metadata: The connection must exist in BimlFlex metadata. Tables that should use the partition scheme need their partition column and scheme configured separately
• Related extension points: Often used alongside DatabaseProperties (for collation/ANSI settings) and DatabasePreDeployment / DatabasePostDeployment (for migration scripts when adding or modifying partitions)

Implementation Steps

1. In BimlFlex, open the Extension Points editor.
2. Create a new extension point file. Select DatabaseMetadata as the type.
3. Set the target attribute to the connection name (e.g., BFX_ODS).
4. Define <FileGroups> with one <FileGroup> per partition plus any special-purpose groups.
5. Define <PartitionFunctions> with boundary values matching your partitioning strategy.
6. Define <PartitionSchemes> mapping the partition function to the filegroups.
7. Use <#=connection.Name#> to dynamically incorporate the connection name into file paths.
8. Build and verify the generated SSDT project includes the filegroups and partition definitions.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DatabaseMetadata" target="BFX_ODS" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>

<FileGroups>
	<FileGroup Name="FG_2024">
		<Files>
			<File Name="<#=connection.Name#>_FG_2024" FilePath="D:\SQLData\<#=connection.Name#>_FG_2024.ndf" />
		</Files>
	</FileGroup>
	<FileGroup Name="FG_2025">
		<Files>
			<File Name="<#=connection.Name#>_FG_2025" FilePath="D:\SQLData\<#=connection.Name#>_FG_2025.ndf" />
		</Files>
	</FileGroup>
	<FileGroup Name="FG_Archive">
		<Files>
			<File Name="<#=connection.Name#>_FG_Archive" FilePath="D:\SQLData\<#=connection.Name#>_FG_Archive.ndf" />
		</Files>
	</FileGroup>
</FileGroups>
<PartitionFunctions>
	<PartitionFunction Name="PF_FlexRowEffectiveDate" DataType="DateTime" BoundarySide="Right">
		<BoundaryValues>
			<BoundaryValue>2024-01-01</BoundaryValue>
			<BoundaryValue>2025-01-01</BoundaryValue>
		</BoundaryValues>
	</PartitionFunction>
</PartitionFunctions>
<PartitionSchemes>
	<PartitionScheme Name="PS_FlexRowEffectiveDate" PartitionFunctionName="PF_FlexRowEffectiveDate">
		<Filegroups>
			<Filegroup>FG_2024</Filegroup>
			<Filegroup>FG_2025</Filegroup>
			<Filegroup>FG_Archive</Filegroup>
		</Filegroups>
	</PartitionScheme>
</PartitionSchemes>

Example

Before (default — no partition metadata):

CREATE DATABASE [BFX_ODS]
GO

After (DatabaseMetadata applied):

CREATE DATABASE [BFX_ODS]
GO
ALTER DATABASE [BFX_ODS] ADD FILEGROUP [FG_2024]
ALTER DATABASE [BFX_ODS] ADD FILEGROUP [FG_2025]
ALTER DATABASE [BFX_ODS] ADD FILEGROUP [FG_Archive]
ALTER DATABASE [BFX_ODS] ADD FILE (NAME = N'BFX_ODS_FG_2024', FILENAME = N'D:\SQLData\BFX_ODS_FG_2024.ndf') TO FILEGROUP [FG_2024]
-- ...
CREATE PARTITION FUNCTION [PF_FlexRowEffectiveDate](datetime) AS RANGE RIGHT FOR VALUES ('2024-01-01', '2025-01-01')
CREATE PARTITION SCHEME [PS_FlexRowEffectiveDate] AS PARTITION [PF_FlexRowEffectiveDate] TO ([FG_2024], [FG_2025], [FG_Archive])

Common Mistakes

• Mistake: Number of filegroups does not match the partition function boundary count + 1. Symptom: SQL Server error: "The number of filegroups does not match the number of partitions." Fix: For N boundary values, you need N+1 filegroups in the partition scheme (the extra one holds values beyond the last boundary).
• Mistake: Using the same file path for multiple filegroups. Symptom: SQL Server error on file creation. Fix: Ensure each <File> element has a unique FilePath. Use <#=connection.Name#> with the filegroup name to generate unique paths.
• Mistake: Applying this extension point to a non-SQL Server connection (e.g., Snowflake, Azure Synapse). Symptom: Build succeeds but the generated output is ignored or causes errors. Fix: Only use DatabaseMetadata for SQL Server / SSDT targets.
• Mistake: Changing partition boundaries without a migration plan. Symptom: Existing data must be moved between partitions, which can cause downtime. Fix: Use DatabasePreDeployment to script partition boundary changes and data movement before applying the new scheme.

Related Extension Points

• DatabaseProperties — Configure database-level settings (collation, ANSI) alongside the structural metadata
• DatabasePreDeployment — Run SQL scripts before deployment (useful for partition migration)
• DatabasePostDeployment — Run SQL scripts after deployment (useful for data validation after partition changes)

Database Post Deployment

Add Post Deployment Script to SSDT Project.

Parameters

Name	Type	Description
connection	BimlFlexModelWrapper.ConnectionsWrapper	Contains all information related to the connection to which the connection expression will be added

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DatabasePostDeployment" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>

SET NOCOUNT ON;

What It Does

Injects a SQL script into the SSDT project's post-deployment script for the targeted database connection. The script executes after all schema objects (tables, views, stored procedures) have been deployed. This is the standard mechanism for seeding reference data, applying data fixes, granting permissions, or running any SQL that depends on the schema being in its final state.

Where It Fires

Registered in bundle.common.manifest under the Connection category. The output is included as a post-deployment script in the generated SSDT project for the targeted connection. It executes after all CREATE/ALTER statements have been applied during a database deployment.

When to Use It

1. Seed reference data after deployment. After deploying the BFX_DM data mart schema, insert default dimension rows (e.g., unknown member records) into dim.Customer and dim.Product.
2. Grant database permissions. After deploying BFX_STG, grant SELECT and INSERT permissions to the ETL service account on all staging tables.
3. Update statistics on heavily loaded tables. After deploying BFX_ODS, run UPDATE STATISTICS on the tables most queried by the Data Vault load process.
4. Use this instead of manual post-deploy scripts in SSDT when you want the script to be managed through BimlFlex and generated alongside the rest of the database project, keeping all deployment logic in one place.
5. Run data migration scripts. After adding a new column to awlt.Customer in BFX_STG, backfill the column with default values.

Prerequisites

• Object types: Applies to any BimlFlex Connection that generates an SSDT project
• Deployment target: SQL Server / SSDT projects only
• Required metadata: The connection must exist in BimlFlex metadata
• Related extension points: Pair with DatabasePreDeployment when you need scripts to run both before and after schema changes. Use DatabaseProperties if you also need to set database-level settings

Implementation Steps

1. In BimlFlex, open the Extension Points editor.
2. Create a new extension point file. Select DatabasePostDeployment as the type.
3. Set the target attribute to the connection name (e.g., BFX_DM).
4. Write the SQL script that should execute after deployment.
5. Build and inspect the SSDT project to verify the post-deployment script is included.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DatabasePostDeployment" target="BFX_DM" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>

SET NOCOUNT ON;

-- Seed unknown member records for dimensions
IF NOT EXISTS (SELECT 1 FROM [dim].[Customer] WHERE [Customer_BK] = N'UNKNOWN')
BEGIN
	INSERT INTO [dim].[Customer] ([Customer_BK], [CustomerName], [FlexRowEffectiveFromDate])
	VALUES (N'UNKNOWN', N'Unknown Customer', '1900-01-01');
END

IF NOT EXISTS (SELECT 1 FROM [dim].[Product] WHERE [Product_BK] = N'UNKNOWN')
BEGIN
	INSERT INTO [dim].[Product] ([Product_BK], [ProductName], [FlexRowEffectiveFromDate])
	VALUES (N'UNKNOWN', N'Unknown Product', '1900-01-01');
END

Example

Before (default — no post-deployment script):

The SSDT project contains no post-deployment script. Deployment applies schema changes only.

After (DatabasePostDeployment applied):

-- Post-Deployment Script: BFX_DM
SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM [dim].[Customer] WHERE [Customer_BK] = N'UNKNOWN')
BEGIN
	INSERT INTO [dim].[Customer] ([Customer_BK], [CustomerName], [FlexRowEffectiveFromDate])
	VALUES (N'UNKNOWN', N'Unknown Customer', '1900-01-01');
END

Common Mistakes

• Mistake: Referencing tables or columns that do not exist yet. Symptom: Deployment fails with "Invalid object name" because the post-deployment script runs before the schema is fully applied in some edge cases. Fix: Wrap references in IF OBJECT_ID('schema.table') IS NOT NULL guards.
• Mistake: Using GO batch separators inside the script. Symptom: Build error — BimlFlex treats the entire extension point output as a single batch. Fix: Use EXEC sp_executesql to run statements that require their own batch, or split logic into separate IF blocks.
• Mistake: Writing non-idempotent scripts (e.g., INSERT without an existence check). Symptom: Duplicate rows after re-deployment. Fix: Always use IF NOT EXISTS or MERGE patterns so the script can run multiple times safely.

Related Extension Points

• DatabasePreDeployment — Run scripts before schema changes (useful for dropping constraints or backing up data)
• DatabaseProperties — Configure database-level settings in the same SSDT project
• DatabaseMetadata — Add filegroups and partition definitions to the same database

Database Pre Deployment

Add Pre Deployment Script to SSDT Project.

Parameters

Name	Type	Description
connection	BimlFlexModelWrapper.ConnectionsWrapper	Contains all information related to the connection to which the connection expression will be added

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DatabasePreDeployment" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>

SET NOCOUNT ON;

What It Does

Injects a SQL script into the SSDT project's pre-deployment script for the targeted database connection. The script executes before any schema changes (CREATE, ALTER, DROP) are applied during deployment. This is the mechanism for preparing the database for structural changes — dropping constraints, backing up data, disabling triggers, or any SQL that must run while the old schema is still in place.

Where It Fires

Registered in bundle.common.manifest under the Connection category. The output is included as a pre-deployment script in the generated SSDT project for the targeted connection. It executes before SSDT begins applying schema changes.

When to Use It

1. Drop foreign keys before a column type change. The BFX_STG staging database has foreign key constraints between awlt.SalesOrderHeader and awlt.SalesOrderDetail. A metadata change widens a key column, and SSDT cannot alter it with the constraint in place. Use DatabasePreDeployment to drop the constraint before deployment.
2. Disable triggers during schema migration. Your BFX_ODS database has audit triggers on satellite tables. Disable them before deployment to avoid spurious audit records during the ALTER TABLE operations.
3. Back up data before a destructive change. You are dropping a column from awlt.Customer in BFX_STG. Use the pre-deployment script to copy the data to a backup table before SSDT removes the column.
4. Use this instead of DatabasePostDeployment when the SQL must execute while the old schema is still intact. Post-deployment runs after changes; pre-deployment runs before.

Prerequisites

• Object types: Applies to any BimlFlex Connection that generates an SSDT project
• Deployment target: SQL Server / SSDT projects only
• Required metadata: The connection must exist in BimlFlex metadata
• Related extension points: Pair with DatabasePostDeployment for complementary post-deploy actions. Use DatabaseMetadata if you also need to add filegroups or partition schemes

Implementation Steps

1. In BimlFlex, open the Extension Points editor.
2. Create a new extension point file. Select DatabasePreDeployment as the type.
3. Set the target attribute to the connection name (e.g., BFX_STG).
4. Write the SQL script that should execute before deployment.
5. Build and inspect the SSDT project to verify the pre-deployment script is included.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DatabasePreDeployment" target="BFX_STG" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>

SET NOCOUNT ON;

-- Drop FK before column type change
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_SalesOrderDetail_SalesOrderHeader')
BEGIN
	ALTER TABLE [awlt].[SalesOrderDetail] DROP CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader];
END

Example

Before (default — no pre-deployment script):

SSDT deployment begins applying schema changes immediately. If a constraint blocks an ALTER TABLE, deployment fails.

After (DatabasePreDeployment applied):

-- Pre-Deployment Script: BFX_STG
SET NOCOUNT ON;

IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_SalesOrderDetail_SalesOrderHeader')
BEGIN
	ALTER TABLE [awlt].[SalesOrderDetail] DROP CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader];
END
-- SSDT schema changes proceed after this script completes

Common Mistakes

• Mistake: Dropping objects that SSDT expects to exist for its diff calculation. Symptom: SSDT generates redundant CREATE statements or skips expected ALTER statements. Fix: Only drop objects that interfere with the deployment (constraints, triggers). Let SSDT manage the tables and columns.
• Mistake: Forgetting to re-create constraints in a corresponding DatabasePostDeployment. Symptom: The constraint is permanently removed after deployment. Fix: Pair every pre-deployment DROP with a post-deployment CREATE to restore the constraint.
• Mistake: Writing scripts that are not idempotent. Symptom: Re-running deployment fails because the object was already dropped. Fix: Always wrap DROP statements in IF EXISTS guards.

Related Extension Points

• DatabasePostDeployment — Run scripts after schema changes (re-create constraints, seed data)
• DatabaseProperties — Configure database-level settings in the same SSDT project
• DatabaseMetadata — Add filegroups and partition definitions to the same database

Database Properties

Add Database Properties to SSDT Project.

Parameters

Name	Type	Description
connection	BimlFlexModelWrapper.ConnectionsWrapper	Contains all information related to the connection to which the connection expression will be added

Template

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DatabaseProperties" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>


<ModelCollation>1033,CS</ModelCollation>
<AnsiWarnings>False</AnsiWarnings>
<AnsiPadding>False</AnsiPadding>

What It Does

Sets database-level properties — collation, ANSI settings, compatibility level, and other database options — in the SSDT project generated for the targeted connection. These properties control how the SQL Server database engine handles string comparisons, null handling, padding, and other fundamental behaviours that affect query results and data storage.

Where It Fires

Registered in bundle.common.manifest under the Connection category with DesignerBimlPath="Biml/Connections". The output is injected into the database properties section of the generated SSDT project for the targeted connection.

When to Use It

1. Enforce case-sensitive collation for the Data Vault. Your BFX_DV database must use case-sensitive collation (1033,CS) to ensure business keys from different source systems are compared accurately. The default BimlFlex database collation may be case-insensitive.
2. Set ANSI compliance options. Your organisation's DBA standards require ANSI_WARNINGS ON and ANSI_PADDING ON for all databases. Use DatabaseProperties to enforce these settings in the generated SSDT project for BFX_STG, BFX_ODS, BFX_DV, and BFX_DM.
3. Configure compatibility level. Your BFX_DM database must run at compatibility level 150 (SQL Server 2019) to use batch mode on rowstore indexes for better query performance on large fact tables.
4. Use this instead of DatabaseMetadata when you need to control database settings (collation, ANSI options) rather than physical storage structure (filegroups, partitions). DatabaseProperties sets how the database behaves; DatabaseMetadata defines where data is stored.

Prerequisites

• Object types: Applies to any BimlFlex Connection that generates an SSDT project
• Deployment target: SQL Server / SSDT projects only
• Required metadata: The connection must exist in BimlFlex metadata
• Related extension points: Often used alongside DatabaseMetadata (for filegroups/partitions) and DatabasePreDeployment / DatabasePostDeployment (for migration scripts)

Implementation Steps

1. In BimlFlex, open the Extension Points editor.
2. Create a new extension point file. Select DatabaseProperties as the type.
3. Set the target attribute to the connection name (e.g., BFX_DV).
4. Add the database property elements using Biml database property syntax.
5. Build and inspect the SSDT project to verify the properties are applied.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="DatabaseProperties" target="BFX_DV" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>

<ModelCollation>1033,CS</ModelCollation>
<CompatibilityLevel>150</CompatibilityLevel>
<AnsiWarnings>True</AnsiWarnings>
<AnsiPadding>True</AnsiPadding>
<AnsiNulls>True</AnsiNulls>

Example

Before (default — no explicit database properties):

<Database Name="BFX_DV" />

After (DatabaseProperties applied):

<Database Name="BFX_DV">
	<ModelCollation>1033,CS</ModelCollation>
	<CompatibilityLevel>150</CompatibilityLevel>
	<AnsiWarnings>True</AnsiWarnings>
	<AnsiPadding>True</AnsiPadding>
	<AnsiNulls>True</AnsiNulls>
</Database>

Common Mistakes

• Mistake: Changing collation on an existing database with data. Symptom: Deployment fails because SQL Server cannot change collation on a database that contains user objects. Fix: Changing collation requires recreating the database. Plan this as a migration, not a property change. For existing databases, use column-level collation overrides instead.
• Mistake: Confusing DatabaseProperties with DatabaseMetadata. Symptom: Attempting to add filegroups via DatabaseProperties results in invalid Biml. Fix: Use DatabaseMetadata for filegroups and partition definitions. Use DatabaseProperties for settings like collation and ANSI options.
• Mistake: Setting ANSI options that conflict with the ETL process. Symptom: Stored procedures generated by BimlFlex fail at runtime with ANSI violation errors. Fix: Test ANSI settings against the BimlFlex-generated stored procedures before deploying to production. In general, ANSI_WARNINGS ON and ANSI_PADDING ON are safe.

Related Extension Points

• DatabaseMetadata — Define filegroups and partition schemes (physical storage, not settings)
• DatabasePreDeployment — Run SQL before schema changes
• DatabasePostDeployment — Run SQL after schema changes