Skip to content
Contact us Go to Customer Portal
  • There are no suggestions because the search field is empty.

Microsoft SQL Server - Stage

Applies to
Platform: Microsoft SQL Server
Architecture: Stage

Microsoft SQL Server - Stage generates a Stage layer for SQL Server as T-SQL scripts with stored procedure loaders. Select the Generator Configuration Microsoft SQL Server Stage when you add a project whose Stage layer reads source databases directly. This article lists what the configuration supports.

For CSV files use Microsoft SQL Server - Stage Files, and for sources that SQL Server reaches with OPENQUERY use Microsoft SQL Server - Stage OpenQuery.

Scope and output

  • Platform: Microsoft SQL Server.
  • Layers: Source and Stage. Dataflow Modeling shows Stage in the Bronze column. See Understand the Layering concept.
  • Output: the layer script SQL\L010_Stage.sql, the load control scripts or an Airflow DAG, and the PowerShell 7 scripts that deploy and load, described in Microsoft SQL Server - Artifacts.

Prerequisites

  • A SQL Server instance and a login that can create the databases, schemas and objects of the layers and the load control. Each layer script creates its database with the recovery model SIMPLE and its schema when they are missing.
  • PowerShell 7 and sqlcmd on the machine that deploys.

The setup is described in Microsoft SQL Server - Target environment. The placeholders for the server, database and schema names are listed in Microsoft SQL Server - Placeholder values.

Sources and linked projects

Stage source views read each source table by its four-part name [<server>].[<database>].[<schema>].[<table>]. The server name stays empty for a source database on the same instance; otherwise it is the name of a linked server. The discovery types are listed in Source Systems per Generator Configurations, and Discover a Microsoft SQL Server database creates source model objects from a SQL Server database.

You can export this project as a linked project. The configuration cannot import linked projects.

Supported model objects

Capabilities and limitations

  • Generated objects per Stage: the table STG_ST_<model object name>, the views _Source and _Result, and the loader _Loader. A deduplication adds the view _Deduplication.
  • Implementation Type Virtual creates only the source and result views, without table and loader.
  • Loader: it truncates the table, then inserts the rows of the source view in a transaction.
  • Keys: Stage tables have no primary key and no hash columns. Increment tables <table name>_Increment have the identity column BG_ID INT IDENTITY.
  • Indexes: Index management offers clustered and nonclustered rowstore and columnstore indexes, unique indexes and Included Columns.
  • Deployment: with the default Deployment Options, each deployment drops and creates the tables again. See Microsoft SQL Server - Deployment.
  • Loading: LoadControl Options offers Native Load Control and Airflow.

Project properties

The configuration offers no Hash Algorithm. It offers these project properties, with their defaults:

Hash Casing, Hash Concatenation Separator, Hash Null Placeholder, Hash Trim and Persist Validity have no effect on Stage objects.

Data types

The Default Terms get these SQL Server data types. DATETIMEOFFSET is written without precision, so SQL Server uses precision 7:

  • Load timestamp, datetimeoffset: BG_LoadTimestamp
  • Text, nvarchar: BG_SourceSystem, BG_DataflowName and BG_DataflowSetName with length 255, BG_ErrorDescription with 4000, BG_IncrementLoadStatus with 20
  • Number: BG_ID int
  • BG_IncrementalFilter, BG_LowerThreshold and BG_UpperThreshold: the data type of the term that you map to BG_IncrementalFilter

Platform differences

These profiles compare their configuration with this one, including data types and properties:

Example

A Stage dataflow_aggregation counts the duplicate IDs per customer of the source table CUSTOMER_DUPLICATES. It generates this table and source view. The excerpt shows the example schema st and the source database BFM with the schema dbo in place of the placeholders, with an empty server name:

CREATE TABLE [st].[STG_ST_dataflow_aggregation] (
     [BG_LoadTimestamp] DATETIMEOFFSET NOT NULL
    ,[BG_SourceSystem] NVARCHAR(255) NULL
    ,[CUSTOMER_ID] INT NOT NULL
    ,[DUPLICATE_CUSTOMER_COUNT] INT NULL
)
;
CREATE VIEW [st].[STG_ST_dataflow_aggregation_Source]
AS
SELECT
     CAST(NULL AS DATETIMEOFFSET) AS [BG_LoadTimestamp]
    ,CAST(NULL AS NVARCHAR(255)) COLLATE DATABASE_DEFAULT
        AS [BG_SourceSystem]
    ,[s1].[CUSTOMER_ID] AS [CUSTOMER_ID]
    ,COUNT_BIG(DISTINCT [s1].[DUPLICATE_CUSTOMER_ID])
        AS [DUPLICATE_CUSTOMER_COUNT]
FROM [BFM].[dbo].[CUSTOMER_DUPLICATES] AS [s1]
GROUP BY
     [s1].[CUSTOMER_ID]
HAVING COUNT(DISTINCT [s1].[DUPLICATE_CUSTOMER_ID]) > 0
;

The loader STG_ST_dataflow_aggregation_Loader fills the table from this view and sets BG_LoadTimestamp to the load timestamp of the load control.

Release differences

Project Settings shows the Generator Version of your project.

  • Generator 2.2: the project properties Persist Validity, Create Result Views, Hash Casing, Hash Concatenation Separator, Hash Null Placeholder and Hash Trim; the model object property Increment Management Type on Stage.

Related articles