Empower supports publishing datasets to on-premises or cloud-hosted SQL Server databases via the Self-Hosted Integration Runtime (SHIR). Data is automatically transferred from your Empower delta lake when the underlying data is updated.

Prerequisites

1. A SQL Server instance accessible from the Self-Hosted Integration Runtime (SHIR).
2. A Self-Hosted Integration Runtime installed and registered in your Azure Data Factory environment.
3. SQL Server credentials (server, database, user ID, password) stored as a connection string in Azure Key Vault.
4. Data in your Empower delta lake to publish.
5. Advanced Options is toggled on for your account.

Steps

1. Add the SQL Server as a Connection

1. In the Empower sidebar, click Connect.
2. Click + to add a new connection, navigate to SQL Server or use the search bar.
3. Fill in the required fields:
   • Server (e.g. tcp:myserver.database.windows.net,1433 or myonpremserver)
   • Initial Catalog (database name)
   • User ID
   • Password
4. Click Save and Connect to save the new SQL Server connection.

icon="⚙️" theme="default">

Self-Hosted Integration Runtime (SHIR)

SQL Server publishing uses the SHIR to connect to databases that are not publicly accessible. Ensure the SHIR is installed on a machine that has network access to the target SQL Server instance and is registered with your Azure Data Factory. The linked service (EMPOWER_SQLSERVER_LS) must be configured to use the SHIR.

2. Create a Publish Task

1. In the Empower sidebar, click Publish.
2. Click the + button to open the New Publish Task panel.
3. Fill in the following fields:
   • Publish Task Name: enter a descriptive name for the task.
   • Publish Method: select Publish to Target.
   • Connection: select the SQL Server connection you created in the previous step.
   • Description (optional): add a short description (up to 100 characters).
4. Click Create to create the publish task.

3. Configure Publish Entities

Once the Publish Task is created, navigate to its Configuration tab to add the entities (tables) you want to publish:

1. Open the Publish Task you just created.
2. Click + New Record to add an entity.
3. Fill in the following fields:
   • Target Entity Name: the name of the table as it will be created/written in the target SQL Server database (e.g. my_target_table).
   • Source Schema: the source table's schema.
   • Source Entity Name: the source table's name.
   • Source Entity Filter (optional): adds a WHERE condition to filter the source data (e.g. account_type='Debit').
     1. The filter must be valid SQL (everything that could be part of a SQL WHERE clause, just without the preceding WHERE).
     2. Make sure to use fields that exist in the source table you are specifying.
     3. When a source filter is provided, the pipeline runs a Databricks notebook (Publish_Filter_To_Parquet) to write the filtered data as Parquet to a temporary ADLS path before the copy activity picks it up.
   • Source Catalog (optional): the source table's Unity Catalog. Only required if the source table is in a specific catalog.
4. Click Create to save the entity record.

ℹ️ New records default to inactive. You must activate the entity for it to be included in publish runs.

Entity Options

After creating an entity record, you can configure Entity Options to control how data is written to the target table. Options are stored as name / value pairs on each entity. To add an option, create an entry with the option name and its value for that entity.

Available Options:

Option Name	Values	Default	Description
sql_table_mode	overwrite / append	overwrite	Controls write mode. overwrite replaces the data in the target table on each publish run. append adds new rows to the existing table without clearing it.
sql_table_truncate	true / false	true	Controls how overwrite is performed. Only applies when sql_table_mode is overwrite. When true, the existing table is truncated (schema preserved) before inserting new data. When false, the existing table is dropped and recreated from scratch before inserting.

Example — set an entity to append mode:

Name	Value
sql_table_mode	append

Example — set an entity to drop-and-recreate on each run:

Name	Value
sql_table_mode	overwrite
sql_table_truncate	false

How the options work together:

sql_table_mode	sql_table_truncate	What Happens
overwrite (default)	true (default)	Truncate the existing table, then insert data. Table structure is preserved.
overwrite	false	Drop the existing table, recreate it, then insert data. Useful if the schema has changed.
append	(ignored)	Insert data into the existing table as-is. No truncation or drop occurs.

Default behavior (no options set): The target table is truncated and reloaded on each publish run. If the target table does not exist, it is automatically created (autoCreate).

icon="📌" theme="default">

Schema Auto-Creation

SQL Server publishing uses ADF's autoCreate table option. If the target table does not exist in the target schema, it will be automatically created based on the Parquet schema from the source data. No manual DDL is required.

How It Works (Pipeline Flow)

The SQL Server publish pipeline follows this execution path:

PUBLISH_MAIN_PL (Orchestrator)
    ↓
PUBLISH_TO_TARGET_PL (Target Router — routes by target_type)
    ↓ (target_type = "sql_server" or default → Spark-based)
PUBLISH_SQLSERVER_PL_ENTITY (SQL Server Handler)
    ↓
ForEach Entity (batch count: 6, parallel)
    ├── GET_KEYVAULT_SECRET → retrieve connection string from Key Vault
    ├── Parse credentials (SET_SERVER_NAME, SET_DATABASE_NAME, SET_USERNAME, SET_PASSWORD)
    ├── IF_HAS_SOURCE_FILTER → optionally run Publish_Filter_To_Parquet notebook
    └── CPY_PUBLISH_SQLSERVER_DATA → ADF Copy from ADLS Parquet to SQL Server table

Data Source Path

The copy activity reads Parquet data from one of two ADLS locations depending on whether a source filter is set:

Scenario	Source Path
No filter	DELTA/{catalog}/{schema}/{table}/dl_iscurrent=true
With filter	RAW/PUBLISH/FILTERED/{publish_entity_id}

Key Configuration

Setting	Value
Write Batch Size	10,000 rows
Timeout	2 hours 30 minutes
Retry	0 (no automatic retries)
Write Behavior	Insert
Table Lock	Disabled
Type Conversion	Enabled (with data truncation allowed)

Troubleshooting

Issue	Resolution
Connection timeout	Ensure the SHIR machine can reach the SQL Server instance. Verify firewall rules and port access (default 1433).
Authentication failure	Verify the Key Vault secret contains the correct connection string format (Server=...;Initial Catalog=...;User ID=...;Password=...).
Table not created	The autoCreate option handles table creation automatically. Ensure the user account has CREATE TABLE permissions in the target schema.
Data truncation errors	Check if source column lengths exceed SQL Server column limits. The pipeline has allowDataTruncation: true but very large values may still fail.
Filter not applied	Ensure the Source Entity Filter field contains valid SQL syntax without the WHERE keyword prefix.