Skip to main content

Microsoft Azure Synapse Overview

Orchestration Method

BimlFlex supports Microsoft Azure Synapse across both SQL Server Integration Services (SSIS) and Azure Data Factory (ADF).

SSIS and ADF both have different constraints and do not always have a 1-to-1 correlation when it comes to features. Due to the different constraints imposed on SSIS and ADF the implementation and architectural pattern will differ between the two. Additional concerns enter the picture when dealing with a cloud data source vs an on-premises data source.

This guide was designed to take these varying conditions in mind. Use the tabs below to have a targeted guide on setting up Azure Synapse for the desired orchestration method.

SSIS

This page is currently configured for SQL Server Integration Services (SSIS).

ADF

This page is currently configured for Azure Data Factory (ADF).


Assumptions and Prerequisites

This guide will operate of a few base assumptions which are listed directly below for your selected Orchestration Method.

SSIS

ADF


Configuring an Azure Synapse Connection

Although largely similar, there are a few nuances when configuration a connection for the separate INTEGRATION STAGES.

The below tabs show the available INTEGRATION STAGES for the selection orchestration method. Select a tab to view field descriptions and an example configuration for a Connection using an available INTEGRATION STAGE.

A BimlFlex Connection is configured by logical role. If the same physical database is to participate in multiple Integration Stages then a BimlFlex Connection will need to be configured for each.

When using Azure Synapse as the Target Warehouse Environment it is recommended* that you use the same database for storage and separate the layers via schema and naming conventions, unless specific organizational or architectural concerns are preventing this.

This may result in the Staging Area, Persistent Staging Area, Data Vault and Data Mart all pointing to the same database.

Connection Details

Below is the details further details on a the specific Connection.

The first table will highlight specific configurations details concerning the selected orchestration method. These may be a mandatory settings or an alternate use for a shared field. These focus more on "how" the Connection is used.

tip

For additional details on creating a Connection, please refer to the Connection Editor.

SSIS

This page is currently configured for SQL Server Integration Services (SSIS).

ADF

This page is currently configured for Azure Data Factory (ADF).


SSIS Specific

FieldDescription
Cloudfalse
Connection Type{ OLEDB , ADONET , Script Source }
Connection StringUsed to connect to the database in SSIS, as the deploy target for the PowerShell Scripts and to import metadata.
ThreadsAmount of threads available to create in SSIS. Use 0 for no limit.

ADF Specific

FieldDescription
Cloudtrue
Connection Type*{ OLEDB SQL Based ELT , ADONET SQL Based ELT , Script Source }
Connection String**Only used as the deploy target for the PowerShell Scripts and to import metadata.
ThreadsThis is not used in ADF.
Landing ConnectionOnly used with a Source System. Designates the connection to use for the Landing Area.
danger
  • Connection Type*: When configuration a Source System the non-ELT base types are used.
    • Source System is the only exception to this. All other INTEGRATION STAGES should use the ELT variant.
    • This is also called out in detail on the below configurations.
  • Connection String**: When using ADF the CONNECTION STRING is only used to import the metadata withing the tool.

The following table highlights configurations details concerning the selected INTEGRATION STAGE. These focus on the "what" and "where" of the data source.

Source System (SRC)

Viewing configuration for a Source System.

Landing Area (LND)

Viewing configuration for a Landing Area.

PolyBase Connection (PLY)

Viewing configuration for a PolyBase Connection.

Staging Area (STG)

Viewing configuration for a Staging Area.

Persistent Staging Area (PSA)

Viewing configuration for a Persistent Staging Area.

Data Vault (DV)

Viewing configuration for a Data Vault.

Data Mart (DM)

Viewing configuration for a Data Mart.


Description

FieldDescription
ConnectionName of the Connection. Must be unique.
Integration StageSource System
Connection Type{ OLEDB , ADONET , Script Source }
System TypeAzure Synapse
DescriptionAn optional description for the Connection
Connection StringConnection String to the connect to the Database
CatalogName of the Database
Record SourceValue to use for Objects from this records source. Required for Data Vault.
Persist History*true: store history in the PERSISTENT STAGE CONNECTION; false: store only latest change in the PERSISTENT STAGE CONNECTION
danger

Persist History* requires a Project with a Persistent Stage Connection defined.

tip

When using ADF an additional Landing Area will need to be configured. For additional details on how create a Landing Area Connection refer to the Azure Data Factory Landing Area Example.

Example

FieldDescription
ConnectionAWLT_SRC
Integration StageSource System
Connection TypeOLEDB
System TypeAzure Synapse
DescriptionConnection to an Azure Synapse Source System.
Connection StringData Source=mysqlserver.database.windows.net,1433;Initial Catalog=BFX_DW;Provider=SQLNCLI11.1;User ID=userName;Password=P@$$Word;
CatalogAdventureWorksLT2012
Record Sourceawlt
Persist History*true

Description

FieldDescription
ConnectionName of the Connection. Must be unique.
Integration StageLanding Area
Connection Type{ OLEDB SQL Based ELT , ADONET SQL Based ELT , Script Source }
System TypeAzure Synapse
DescriptionAn optional description for the Connection
Connection StringOnly used to Import Metadata in BimlFlex. Connection String to the connect to the Database.
CatalogName of the Database (Ensure this matches the Initial Catalog in your connection string)
PolyBase ConnectionOptional: When using PolyBase Staged Copy, the Connection holding the Azure Storage Account Linked Service.
tip

When using Azure Synapse this should be configured to the same Database as the primary target warehouse.

This can be configured to be landed directly into an Azure Synapse table or into Blob Storage and then use PolyBase to Stage.

See the PolyBase Connection tab for more details.

Example

FieldDescription
ConnectionBFX_LND
Integration StageLanding Area
Connection TypeOLEDB SQL Based ELT
System TypeAzure Synapse
DescriptionConnection to an Azure Synapse Landing Area
Connection StringData Source=mysqlserver.database.windows.net,1433;Initial Catalog=BFX_DW;Provider=SQLNCLI11.1;User ID=userName;Password=P@$$Word;
CatalogBFX_DW
PolyBase ConnectionBFX_POLY (Configured for PolyBase Staging)

Description

FieldDescription
ConnectionName of the Connection. Must be unique.
Integration StageLanding Area
Connection Type{ Azure Blob Storage , Azure Data Lake Store }
System TypeParquet Format
DescriptionAn optional description for the Connection
Connection StringOnly used to Import Metadata in BimlFlex. Connection String to the connect to the Database.
CatalogName of the Database (Ensure this matches the Initial Catalog in your connection string)
PolyBase Connection{Empty}
tip

This is used in conjunction with a Landing Area to indicate the location to use for a PolyBase load. For additional details on how create a Landing Area Connection please refer to the Azure Data Factory Landing Area Example.

Example

FieldDescription
ConnectionBFX_POLY
Integration StageLanding Area
Connection TypeOLEDB SQL Based ELT
System TypeAzure Synapse
DescriptionConnection for PolyBase Staging
Connection StringData Source=mysqlserver.database.windows.net,1433;Initial Catalog=BFX_DW;Provider=SQLNCLI11.1;User ID=userName;Password=P@$$Word;
CatalogBFX_DW
PolyBase Connection{Empty}

Description

FieldDescription
ConnectionName of the Connection. Must be unique.
Integration StageStaging Area
Connection Type{ OLEDB SQL Based ELT , ADONET SQL Based ELT , Script Source }
System TypeAzure Synapse
DescriptionAn optional description for the Connection
Connection StringOnly used to Import Metadata in BimlFlex. Connection String to the connect to the Database.
CatalogName of the Database (Ensure this matches the Initial Catalog in your connection string)

Example

FieldDescription
ConnectionBFX_STG
Integration StageStaging Area
Connection TypeOLEDB SQL Based ELT
System TypeAzure Synapse
DescriptionConnection to an Azure Synapse Staging Area
Connection StringData Source=mysqlserver.database.windows.net,1433;Initial Catalog=BFX_DW;Provider=SQLNCLI11.1;User ID=userName;Password=P@$$Word;
CatalogBFX_DW

Description

FieldDescription
ConnectionName of the Connection. Must be unique.
Integration StagePersistent Staging Area
Connection Type{ OLEDB SQL Based ELT , ADONET SQL Based ELT , Script Source }
System TypeAzure Synapse
DescriptionAn optional description for the Connection
Connection StringOnly used to Import Metadata in BimlFlex. Connection String to the connect to the Database.
CatalogName of the Database (Ensure this matches the Initial Catalog in your connection string)

Example

FieldDescription
ConnectionBFX_ODS
Integration StagePersistent Staging Area
Connection TypeOLEDB SQL Based ELT
System TypeAzure Synapse
DescriptionConnection to an Azure Synapse Persistent Staging Area
Connection StringData Source=mysqlserver.database.windows.net,1433;Initial Catalog=BFX_DW;Provider=SQLNCLI11.1;User ID=userName;Password=P@$$Word;
CatalogBFX_DW

Description

FieldDescription
ConnectionName of the Connection. Must be unique.
Integration StageData Vault
Connection Type{ OLEDB SQL Based ELT , ADONET SQL Based ELT , Script Source }
System TypeAzure Synapse
DescriptionAn optional description for the Connection
Connection StringOnly used to Import Metadata in BimlFlex. Connection String to the connect to the Database.
CatalogName of the Database (Ensure this matches the Initial Catalog in your connection string)

Example

FieldDescription
ConnectionBFX_DM
Integration StageData Vault
Connection TypeOLEDB SQL Based ELT
System TypeAzure Synapse
DescriptionConnection to an Azure Synapse Data Vault
Connection StringData Source=mysqlserver.database.windows.net,1433;Initial Catalog=BFX_DW;Provider=SQLNCLI11.1;User ID=userName;Password=P@$$Word;
CatalogBFX_DW

Description

FieldDescription
ConnectionName of the Connection. Must be unique.
Integration StageData Mart
Connection Type{ OLEDB SQL Based ELT , ADONET SQL Based ELT , Script Source }
System TypeAzure Synapse
DescriptionAn optional description for the Connection
Connection StringOnly used to Import Metadata in BimlFlex. Connection String to the connect to the Database.
CatalogName of the Database (Ensure this matches the Initial Catalog in your connection string)

Example

FieldDescription
ConnectionBFX_DM
Integration StageData Mart
Connection TypeOLEDB SQL Based ELT
System TypeAzure Synapse
DescriptionConnection to an Azure Synapse Data Mart
Connection StringData Source=mysqlserver.database.windows.net,1433;Initial Catalog=BFX_DW;Provider=SQLNCLI11.1;User ID=userName;Password=P@$$Word;
CatalogBFX_DW

Configuring an Azure Synapse Linked Service

Linked Services are used in Azure Data Factory to define a data source.

To configure a Linked Service select a Connection and enable the CLOUD selector at the top.

SSIS

This page is currently configured for SQL Server Integration Services (SSIS).

ADF

This page is currently configured for Azure Data Factory (ADF).


ADF Linked Service

There are no special caveats when configuring an Azure Synapse Linked Service. For details on configuration, please refer to the links below.

tip

Linked Service: N/A

Linked Services are not configured when using SSIS.

Configuration Notes

When configuring an AKV Secret for Connection String be aware that it is different from the Connection CONNECTION STRING field.

If not already done, the AKV Secret must been manual configured and use syntax slightly different than the CONNECTION STRING field. A pattern and example can be found below.

Pattern: Data Source=<server name>.database.windows.net,<port>;Initial Catalog=<database>;User ID=<user>;Password=<password>;

Example: Data Source=bfxserver.database.windows.net,1433;Initial Catalog=bfx_sqldw;User ID=MyUser;Password=P@$$Word;

danger

Connection Strings for Linked Services DO NOT support the Provider keyword.

tip

If the Linked Service configuration is not showing up, ensure that CLOUD is enabled on the selected Connection.


Configuring Settings

BimlFlex Settings are used for environment details and orchestration configurations.

The below Settings only need to be configured when using Azure Synapse as the Target Warehouse Environment.

SSIS

This page is currently configured for SQL Server Integration Services (SSIS).

ADF

This page is currently configured for Azure Data Factory (ADF).


Mandatory Settings

The following Settings are required when working with Azure Synapse regardless of the orchestration method.

SSIS

  • AzCopy\Paths
    • Configures location of AzCopy and Log Location
  • AzCopy\Settings
    • General AzCopy Settings
  • Azure Storage\Processing
    • Configures the names Accounts, Container Names and Keys/Tokens to be used with the Blob destination for AzCopy
  • SSDT\Synapse SSDT Artifacts
    • Configures the inclusion and configuration of the External Tables, Master Key, Credential, External Data Source and External File Format

ADF

  • Azure\Data Factory
    • Configures the DATA FACTORY NAME, SUBSCRIPTION ID, and RESOURCE GROUP
    • Optional configurations for an existing KEY VAULT or INTEGRATION RUNTIME
  • (OPTIONAL) Azure\Deployment
    • Only required if using an ADF Linked Template Deployment
    • Configures the name of the Account, Container and Key/Token to be used with the Linked Template Deployment
  • Azure Storage\Processing
    • Configures the names Accounts, Container Names and Keys/Tokens to be used with the Blob destination for AzCopy

PolyBase Staging

These below Settings are only required when using PolyBase Staging in ADF.


Building the Project

In order to generate any assets the BimlFlex project will need to be built. This process is identical to the standard build process and can be referenced in the guides below.

Prior to building the project ensure that the following steps is completed:

tip

For additional details on PowerShell deployment refer to the below guide:

Database Deployment

BimlFlex will generate the required SQL scripts for the deployment of all the tables, stored procedures, and the Data Vault default inserts (Ghost Keys). Once generated the scripts can be deployed to the required database.

Prior to deploying the Target Warehouse Environment ensure that the following steps are completed:

SSIS

This page is currently configured for SQL Server Integration Services (SSIS).

ADF

This page is currently configured for Azure Data Factory (ADF).


PowerShell

A part of the Build process, BimlStudio will generate a SQL Server Data Tools (SSDT) project for the Synapse target warehouse platform. By default a SSDT deployment file named ssdt-deploy.<DatabaseName>.ps1 is created and placed in the ...\<Output Folder>\Deploy\ folder for each database in the target warehouse environment.

The SSDT project will have all the required tables, stored procedures and Data Vault default inserts required for the project. Through use of SSDT\Synapse SSDT Artifacts Settings EXTERNAL TABLES can be included or excluded in this deployment file. These files are commonly excluded due to PolyBase requiring a file to exist in the blob storage prior to the creation of the EXTERNAL TABLE.

danger

It is HIGHLY recommended that the Synapse server in created manually in Azure prior to executing the ssdt-deploy PowerShell script. The generated SSDT project does not contain any configuration information for Azure settings and will be created with whatever default Microsoft is currently using for Performance Level.

Aside from the possible inclusion of the EXTERNAL TABLE scripts, the process is identical to a standard SQL Server deployment.

tip

For additional details on PowerShell deployment refer to the below guide:

PowerShell

A part of the Build process, BimlStudio will generate a SQL Server Data Tools (SSDT) project for the Synapse target warehouse platform. By default a SSDT deployment file named ssdt-deploy.<DatabaseName>.ps1 is created and placed in the ...\<Output Folder>\Deploy\ folder for each database in the target warehouse environment.

The SSDT project will have all the required tables, stored procedures and Data Vault default inserts required for the project.

danger

It is HIGHLY recommended that the Synapse server in created manually in Azure prior to executing the ssdt-deploy PowerShell script. The generated SSDT project does not contain any configuration information for Azure settings and will be created with whatever default Microsoft is currently using for Performance Level.

tip

For additional details on PowerShell deployment refer to the guide covering deployment through PowerShell.

Generated DDL

Generating Azure Synapse DDL scripts are similar to a standard SQL Server deployment using Generate Scripts in BimlStudio with a few exceptions. Azure Synapse tables are required to be created by Azure Synapse Table Script instead of the use of Create Table Script. Create Table Script is not needed for a Azure Synapse target warehouse environment. Additionally if using the PolyBase Architecture, the PolyBase External Table Script will need to be ran to generate the CREATE EXTERNAL TABLE scripts.

note

Ensure the below settings are using the PolyBase External Table Script

The below table has been provided as a quick reminder as to when a script should be generated.

ScriptCondition
PolyBase External Table ScriptWhen using PolyBase Architecture
Azure Synapse Table ScriptWhen using Synapse as Target Warehouse Platform
Data Vault Default Insert ScriptWhen using a Data Vault
Data Vault Procedure ScriptWhen using a SQL Based ELT Data Vault
Data Mart Procedure ScriptWhen using a SQL Based ELT Data Mart
Persistent Procedure ScriptWhen using a SQL Based ELT Staging Area w/ PSA
Business Vault Procedure ScriptWhen using PIT or Bridge Tables
tip

For additional details on generating DDL refer to the BimlFlex DDL generation guide.

Once BimlFlex generates the scripts they can be executed against the target database. These can be deployed through copy/paste using a SQL Server Management Studio or by another script execution clients if so desired.

tip

For additional details on installing or using SSMS refer to the below guides:


Deploying Orchestration

BimlFlex automatically generates the orchestration artifacts as part of the standard build process. The actual artifacts generated depends on the method of orchestration that is used. The below sections outline the various artifacts by orchestration methods.

Prior to deploying the orchestration ensure that the following steps are completed:

SSIS

This page is currently configured for SQL Server Integration Services (SSIS).

ADF

This page is currently configured for Azure Data Factory (ADF).


SSIS Project

Deploying SSIS orchestration while using Azure Synapse as a target platform is no different a standard SSIS deployment using BimlFlex. Refer to the below guides for common deployment methods.

tip

For additional details on generating deploying SSIS packages refer to the below guides:

Azure Resources

The process of deploying the Azure Resources itself remains unchanged.

tip

For additional details on generating deploying ADF artifacts refer to the below guides: