---
title: Microsoft SQL Server - Stage Files
description: Microsoft SQL Server - Stage Files generates a Stage layer for SQL Server that loads CSV files, as T-SQL scripts with stored procedure loaders.
---

[Skip to content](https://knowledge.bigenius-x.com/microsoft-sql-server-stage-files-2.0#main-content)

[Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en)

[![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/)

Open main navigation

Close main navigation

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new)
- [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal)

 How can we help you?

- There are no suggestions because the search field is empty.

1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en)
2. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en)
3. [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server)

# 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](https://knowledge.bigenius-x.com/microsoft-sql-server-stage-2.0?hsLang=en).

### 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](https://knowledge.bigenius-x.com/understand-the-layering-concept?hsLang=en).
- 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](https://knowledge.bigenius-x.com/microsoft-sql-server-artifacts-2.0?hsLang=en).

### 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](https://knowledge.bigenius-x.com/microsoft-sql-server-target-environment?hsLang=en). The placeholders for the server, database and schema names are listed in [Microsoft SQL Server - Placeholder values](https://knowledge.bigenius-x.com/microsoft-sql-server-placeholder-values-2.0?hsLang=en).

### Sources and linked projects

The source system has the type `Csv`. The CSV options of a Stage, such as [File Group Pattern](https://knowledge.bigenius-x.com/file-group-pattern-model-object?hsLang=en), are described in [CSV options (Model Object)](https://knowledge.bigenius-x.com/csv-options-model-object?hsLang=en). The discovery types are listed in [Source Systems per Generator Configurations](https://knowledge.bigenius-x.com/source-systems-per-generator-configurations?hsLang=en).

You can [export this project as a linked project](https://knowledge.bigenius-x.com/export-a-project-to-be-used-as-linked-project?hsLang=en). The configuration cannot import linked projects.

### Supported model objects

- [Source](https://knowledge.bigenius-x.com/create-a-source?hsLang=en)
- [Stage](https://knowledge.bigenius-x.com/create-a-stage?hsLang=en)

### 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](https://knowledge.bigenius-x.com/add-an-index?hsLang=en) offers clustered and nonclustered rowstore and columnstore indexes, unique indexes and Included Columns.
- Deployment: with the default [Deployment Options](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en), each deployment drops and creates the tables again. See [Microsoft SQL Server - Deployment](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment-2.0?hsLang=en).
- Loading: [LoadControl Options](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en) offers `Native Load Control` and `Airflow`.

### Project properties

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

- [Hash Casing](https://knowledge.bigenius-x.com/hash-casing-project?hsLang=en): `None`; also `Upper` and `Lower`
- [Hash Concatenation Separator](https://knowledge.bigenius-x.com/hash-concatenation-separator-project?hsLang=en): `_`
- [Hash Null Placeholder](https://knowledge.bigenius-x.com/hash-null-placeholder-project?hsLang=en): `_NULL_`
- [Hash Trim](https://knowledge.bigenius-x.com/hash-trim-project?hsLang=en): off
- [Begin of Universe](https://knowledge.bigenius-x.com/begin-of-universe-project?hsLang=en): `19000101`
- [Persist Validity](https://knowledge.bigenius-x.com/persist-validity-project?hsLang=en): off
- [Create Result Views](https://knowledge.bigenius-x.com/create-result-views-project?hsLang=en): on
- [Target Object Comment Type](https://knowledge.bigenius-x.com/target-object-comment-type-project?hsLang=en): `None`; also `Tables` and `Tables and Views`
- [Artifact Output Mode](https://knowledge.bigenius-x.com/artifact-output-mode-project?hsLang=en): `Layer Composites`; also `Deployment Objects`
- [Deployment Options](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en): `Generate Drop Table Statements`
- [Configure LoadControl](https://knowledge.bigenius-x.com/configure-loadcontrol-project?hsLang=en): on
- [Deploy LoadControl](https://knowledge.bigenius-x.com/deploy-loadcontrol-project?hsLang=en): on
- [LoadControl Options](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en): `Native Load Control`; also `Airflow`

### 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

- Previous: 
    - Microsoft SQL Server: [Target environment](https://knowledge.bigenius-x.com/microsoft-sql-server-target-environment?hsLang=en)
- Next: [Generate artifacts](https://knowledge.bigenius-x.com/generate-artifacts?hsLang=en), [Replace the placeholders in the artifacts](https://knowledge.bigenius-x.com/replace-the-placeholders-in-the-artifacts?hsLang=en) 
    - Microsoft SQL Server: [Artifacts](https://knowledge.bigenius-x.com/microsoft-sql-server-artifacts-2.0?hsLang=en), [Deployment](https://knowledge.bigenius-x.com/microsoft-sql-server-deployment-2.0?hsLang=en), [Load data - Native Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-native-load-control-2.0?hsLang=en), [Load data - Apache Airflow Load Control](https://knowledge.bigenius-x.com/microsoft-sql-server-load-data-apache-airflow-load-control-2.0?hsLang=en)
- Reference: [CSV options (Model Object)](https://knowledge.bigenius-x.com/csv-options-model-object?hsLang=en), [File Group Pattern (Model Object)](https://knowledge.bigenius-x.com/file-group-pattern-model-object?hsLang=en), [Artifact Output Mode (Project)](https://knowledge.bigenius-x.com/artifact-output-mode-project?hsLang=en), [Deployment Options (Project)](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en), [LoadControl Options (Project)](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en) 
    - Microsoft SQL Server: [Placeholder values](https://knowledge.bigenius-x.com/microsoft-sql-server-placeholder-values-2.0?hsLang=en)
- See also: [Available Generator Configurations](https://knowledge.bigenius-x.com/available-generator-configurations?hsLang=en), [Understand the Generator Configuration](https://knowledge.bigenius-x.com/understand-the-generator-configuration?hsLang=en), [Source Systems per Generator Configurations](https://knowledge.bigenius-x.com/source-systems-per-generator-configurations?hsLang=en) 
    - Microsoft SQL Server: [Stage](https://knowledge.bigenius-x.com/microsoft-sql-server-stage-2.0?hsLang=en), [Stage OpenQuery](https://knowledge.bigenius-x.com/microsoft-sql-server-stage-openquery-2.0?hsLang=en), [Data Store](https://knowledge.bigenius-x.com/microsoft-sql-server-data-store-2.0?hsLang=en), [DataVault](https://knowledge.bigenius-x.com/microsoft-sql-server-datavault-2.0?hsLang=en), [Dimensional](https://knowledge.bigenius-x.com/microsoft-sql-server-dimensional-2.0?hsLang=en)

- [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content)

    - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines)
    - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working)
    - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account)
- [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en)
- [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en)
- [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content)

    - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration)
    - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions)
    - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features)
    - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects)
    - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches)
    - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections)
    - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview)
    - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps)
    - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms)
    - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping)
    - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships)
    - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality)
    - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes)
    - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling)
    - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts)
    - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings)
    - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace)
- [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content)

    - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse)
    - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse)
    - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks)
    - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake)
    - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server)
    - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle)
    - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts)
    - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders)
    - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment)
    - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment)
    - [Deployment with an Azuze DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azuze-devops-pipeline)
    - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment)
    - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment)
    - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control)
    - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow)
    - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type)
    - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties)
    - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms)
- [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content)

    - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations)
- [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content)

    - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches)
    - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases)
    - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules)
    - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules)
- [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en)
- [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content)

    - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application)
    - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application)
    - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators)
- [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content)

    - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs)
    - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits)

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en)
- [Customer Portal](https://knowledge.bigenius-x.com/customer-portal)
- [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login)

[![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com)

<https://www.linkedin.com/company/bigenius> <https://www.youtube.com/@biGENIUS_DWA>

All rights reserved © 2025, biGENIUS AG