MS SQL Server

Connect to your MS SQL Server databases.

Getting Started

Prerequisites to create a MS SQL based workflow. You will need

1. A source MS SQL connection.

2. (optional) A list of tables OR SQL queries.

3. (optional) A destination MS SQL connection.

For the source database connection, we recommend using a backup or clone with read-only permissions, instead of connecting directly to your production database.

Do not use your input database connection as an output connector. This action can result in the unintended overwriting of existing data.

Create a Connection

A mssql connection is created using the following parameters:

Connection Creation Parameters

name	Display name of your choosing used to identify your connection within Gretel.	my-mssql-connection
username	Unique identifier associated with specific account authorized to access database.	john
password	Security credential to authenticate username.	...
host	Fully qualified domain name (FQDN) used to establish connection to database server.	myserver.example.com
port	Port number; If left empty, the default value - 1403- will be used.	1403
database	Name of database to connect to.	mydatabase
(optional) schema	Optional Name of specific schema	dbo
(optional) params	Optional JDBC URL parameters that can be used for advanced configuration.	TrustServerCertificate=True

Creating Connections

CLI

First, create a file on your local computer containing the connection credentials. This file should also include type, name , config, and credentials. The config and credentials fields should contain fields that are specific to the connection being created.

Below is an example MS SQL connection:

{
    "type": "mssql",
    "name": "my-mssql-connection",
    "config": {
        "username": "john",
        "host": "myserver.example.com",
        "port": 1443,
        "database": "mydatabase",
        "schema": "dbo",
        "params": "TrustServerCertificate=True"
    },
    "credentials": {
        "password": "..."
    }
}

Now that you've created the credentials file, use the CLI to create the connection

gretel connections create --project [project id] --from-file [credential_file.json]

Console

• Navigate to the Connections page using the menu item in the left sidebar.

• Click the New Connection button.

• Step 1, choose the Type for the Connection - MS SQL Server.

• Step 2, choose the Project for your Connection.

• Step 3, fill in the credentials and select Add Connection.

Step 1, choose the Type of connection

Step 3, fill in credentials

SDK

from gretel_client import create_or_get_unique_project
from gretel_client.config import get_session_config
from gretel_client.rest_v1.api.connections_api import ConnectionsApi
from gretel_client.rest_v1.models import (
    CreateConnectionRequest,
    UpdateConnectionRequest,
)

session = get_session_config()
connection_api = session.get_v1_api(ConnectionsApi)

project = create_or_get_unique_project(name="mssql-workflow")

connection = connection_api.create_connection(
    CreateConnectionRequest(
        name="my-mssql-connection",
        project_id=project.project_guid,
        type="mssql",
        config={
            "username": "john",
            "host": "myserver.example.com",
            "database": "mydatabase",
            #"port": 1433,
            #"schema": "dbo",
            #"params": "TrustServerCertificate=True",
        },
        # note: best practice is to read in credentials from a file
        # or secret instead of directly embedding sensitive values
        # in python code.
        credentials={
            "password": "...",
        },
    )
)