Skip to content
  • There are no suggestions because the search field is empty.

Microsoft SQL Server - Target environment

This article outlines a potential setup for target environments for Microsoft SQL Server generators.

Please note that the installation and configuration of this target environment fall outside the scope of biGENIUS support.

We will not offer assistance beyond the provided example in this article.

A Microsoft SQL Server target environment has numerous alternative configurations and installations.

Below is a potential target environment setup for a Microsoft SQL Server generator.

Setup environment

Please install the following tools:

  • SQL Server Management Studio (download available here)
  • Powershell 7 or higher (download available here)
  • Sqlcmd ODBC utility (download available here)
  • SQL Server 2014 or higher

We advise using a Docker image instead of installing a local SQL server.

An example of a Docker image is available here. 

It contains SQL Server 2022 and 2 example source databases: AdventureWorks2019 and CRM_Example.

To use this image:

  • Install Docker Desktop from here
  • Create a network with the following command in Powershell: 
    docker network create training-network

It is mandatory to create a Docker network and use the same one if you want to use Apache Airflow as load control.

  • Create a container with the following command in Powershell: 
    docker run --name training-sqlserver-2022 -p 1460:1433 -d bigeniusx/training-sqlserver:2022-latest

Be careful: using a Flat File load is impossible with a Linux container. 

The following Windows container must be created:

Please adapt the mounted folder

docker run -d --name training-sqlserver-windows-2022 -p 1470:1433 -v C:\shared:C:\setup bigeniusx/training-sqlserver-on-windows:latest powershell -file C:\start.ps1

Reason: we need to execute the command sp_configure 'xp_cmdshell', '1' in the SQL Server container to be able to load data from a file.
But it is not possible with a Linux OS (see the unsupported features in SQL Server 2022 on Linux)

  • Connect to your SQL Server instance with SQL Server Management Studio with the following credentials:
    • Server name: localhost,1460
    • Authentication: SQL Server Authentication
    • Login: sa
    • Password: Admin2022!
  • You are now connected to your Docker SQL Server with the two source databases already restored:

Source data

There are three ways to provide source data to a Microsoft SQL Server generator:

  • From a Microsoft SQL Server database by using a direct Discovery
  • From CSV files by using the Microsoft SQL Server Stage Files generator as a Linked Project
  • From any database accessed through JDBC by using the Microsoft SQL Server Stage Open Query generator as a Linked Project

CSV Files

If your source data are stored in CSV files, please:

  • Create a first Project with the Microsoft SQL Server Stage Files generator
  • In this first Project, discover the CSV files, create the Stage Model Object, generate, deploy, and load data in the target database.
  • Create a second Project with, for example, the Microsoft SQL Server DataVault and Mart generators.
  • In this second Project, use the first Project Stage Model Object as a source by using the Linked Project feature.

 

To load CSV file data, please create the following folder structure:

In the Format folder, copy the XXXX.fmt file from the Format folder of your generated artifacts.

  • In our example:

In the Source folder, copy the file to load.

  • In our example:

Database

If your source data are stored in a database such as Oracle or Postgres (or any database you can access through JDBC), please:

  • Create a first Project with the Microsoft SQL Server Stage OpenQuery generator
  • In this first Project, discover the database tables, create the Stage Model Object, generate, deploy, and load data in the target database.
  • Create a second Project with, for example, the Microsoft SQL Server DataVault and Mart generator
  • In this second Project, use the first Project Stage Model Object as a source by using the Linked Project feature.

 

To load data with OpenQuery, we must first create a Linked Server to access the data from SQL Server.

We will take an example with an Oracle source database:

  • Check the availability of the ORAOLEDB.Oracle provider in your SQL Server installation:

If the Oracle provider is not installed, you must first install the Oracle ODAC components available here.

  • Allow in-process statements for the Oracle provider by executing the following commands: 
    exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1

    exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1
  • Execute the following code to create your Linked Server: 
    EXEC master.dbo.sp_addlinkedserver 
    @server = N'ORACLE_19_DOCKER'
    ,@srvproduct=N'Oracle' 
    ,@provider=N'OraOLEDB.Oracle' --Keep this value
    ,@datasrc=N'//localhost:1521/ORCLPDB1'
    GO

    EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname=N'ORACLE_19_DOCKER',
    @useself=N'False',
    @locallogin=NULL,
    @rmtuser=N'AWDEMO2019',
    @rmtpassword=N'AWDEMO2019'
    GO

Please adapt the @datasrc, @rmtuser, and @rmtpassword  parameter values to your Oracle installation.

  • Check the correct configuration of your Linked Server by selecting some data with an OpenQuery, for example: 
    SELECT * FROM OPENQUERY(ORACLE_19_DOCKER, 'SELECT * FROM AWDEMO2019.CREDITCARD')

 

If you have already discovered your source data, modeled your project, and generated the artifacts, you're now ready to replace the placeholders in your generated artifacts, deploy these artifacts, and subsequently load the data based on the Generator you are using with the following possible load controls: