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

Microsoft SQL Server - Stage Files

Applies to
Platform: Microsoft SQL Server
Architecture: Stage

Microsoft SQL Server - Stage Files generates a Stage layer for SQL Server that loads CSV files, as T-SQL scripts with stored procedure loaders. Select the Generator Configuration Microsoft SQL Server Stage File when you add a project whose sources are CSV files. This article lists what the configuration supports.

The configuration works with CSV source systems only. For source databases use Microsoft SQL Server - Stage.

Scope and output

  • Platform: Microsoft SQL Server on Windows.
  • 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, one format file per Stage in the folder Format, 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 extended stored procedure xp_cmdshell enabled on the instance.
  • Four folders on the SQL Server host for the CSV files, the format files, the archive and the error files, with the permission Modify for the SQL Server service account.

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

The source system has the type Csv. The CSV options of a Stage, such as File Group Pattern, are described in CSV options (Model Object). The discovery types are listed in Source Systems per Generator Configurations.

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

Supported model objects

Capabilities and limitations

  • Raw data: each Stage gets a table STG_ST_<model object name>_RawData with one NVARCHAR(MAX) column per file column. Its loader _RawDataLoader fills it.
  • Reading files: the loader lists the matching files with xp_cmdshell and reads each file with OPENROWSET(BULK ...), the format file <model object name>.fmt and FORMAT = 'CSV'.
  • Source view: _Source casts the raw columns to the data types of the terms. BG_SourceSystem holds the path of the loaded file.
  • Without xp_cmdshell the loader stops with error 50001 SQL Server instance is not configured for xp_cmdshell.
  • 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

Example

A Stage dataflow_mapped_sourcesystem reads CSV files with invoice data. It generates this raw data table and source view. The excerpt shows the example schema st and the example database DWH in place of the placeholders and leaves out the other file columns:

CREATE TABLE [st].[STG_ST_dataflow_mapped_sourcesystem_RawData] (
     [BG_LoadTimestamp] DATETIMEOFFSET NOT NULL
    ,[BG_SourceSystem] NVARCHAR(255) NULL
    ,[INVOICE_NO] NVARCHAR(MAX) NULL
    ,[CUSTOMER_ID] NVARCHAR(MAX) NULL
    ,[DELIVERY_DATE] NVARCHAR(MAX) NULL
)
;
CREATE VIEW [st].[STG_ST_dataflow_mapped_sourcesystem_Source]
AS
SELECT
     [BG_LoadTimestamp] AS [BG_LoadTimestamp]
    ,ISNULL([BG_SourceSystem], N'Source1') COLLATE DATABASE_DEFAULT
        AS [BG_SourceSystem]
    ,CAST([s1].[INVOICE_NO] AS NVARCHAR(20)) COLLATE DATABASE_DEFAULT
        AS [INVOICE_NO]
    ,CAST([s1].[CUSTOMER_ID] AS INT) AS [CUSTOMER_ID]
    ,CAST([s1].[DELIVERY_DATE] AS DATE) AS [DELIVERY_DATE]
FROM [DWH].[st].[STG_ST_dataflow_mapped_sourcesystem_RawData] AS [s1]
;

The file Format\dataflow_mapped_sourcesystem.fmt of the artifacts describes the file columns for OPENROWSET.

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