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.

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

Assumptions and Prerequisites

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

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.

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

Field Description
Cloud false
Connection Type { OLEDB , ADONET , Script Source }
Connection String Used to connect to the database in SSIS, as the deploy target for the PowerShell Scripts and to import metadata.
Threads Amount of threads available to create in SSIS. Use 0 for no limit.

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

Viewing configuration for a Source System.

Field Description
Connection Name of the Connection. Must be unique.
Integration Stage Source System
Connection Type { OLEDB , ADONET , Script Source }
System Type Azure Synapse
Description An optional description for the Connection
Connection String Connection String to the connect to the Database
Catalog Name of the Database
Record Source Value 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
Important

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.

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.

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

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

For additional details on creating a Linked Service refer to the below guides:

Linked Services are not configured when using SSIS.

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.

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

Mandatory Settings

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

  • 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

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:

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

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.

Important

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:

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:

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

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: