Skip to main content

MSSQL [ARCHIVED]

Availability:
Not available Airbyte CloudNot available Airbyte OSSAvailable PyAirbyte
Support Level:
Archived

Features​

FeatureSupported?(Yes/No)Notes
Full Refresh SyncYes
Incremental - Append SyncYes
Incremental - Append + DedupedYes
NamespacesYes

Output Schema​

Each stream will be output into its own table in SQL Server. Each table will contain the following metadata columns:

  • _airbyte_raw_id: A random UUID assigned to each incoming record. The column type in SQL Server is VARCHAR(MAX).
  • _airbyte_extracted_at: A timestamp for when the event was pulled from the data source. The column type in SQL Server is BIGINT.
  • _airbyte_meta: Additional information about the record. The column type in SQL Server is TEXT.
  • _airbyte_generation_id: Incremented each time a refresh is executed. The column type in SQL Server is TEXT.

See here for more information about these fields.

Getting Started​

Setup guide​

  • MS SQL Server: Azure SQL Database, SQL Server 2016 or greater

Network Access​

Make sure your SQL Server database can be accessed by Airbyte. If your database is within a VPC, you may need to allow access from the IP you're using to expose Airbyte.

Permissions​

You need a user configured in SQL Server that can create tables and write rows. We highly recommend creating an Airbyte-specific user for this purpose. In order to allow for normalization, please grant ALTER permissions for the user configured.

Target Database​

You will need to choose an existing database or create a new database that will be used to store synced data from Airbyte.

Configuration​

You'll need the following information to configure the MSSQL destination:

  • Host
    • The host name of the MSSQL database.
  • Port
    • The port of the MSSQL database.
  • Database Name
    • The name of the MSSQL database.
  • Default Schema
    • The default schema tables are written to if the source does not specify a namespace. The usual value for this field is "public".
  • Username
    • The username which is used to access the database.
  • Password
    • The password associated with this username.
  • JDBC URL Parameters
    • Additional properties to pass to the JDBC URL string when connecting to the database formatted as 'key=value' pairs separated by the symbol '&'. (example: key1=value1&key2=value2&key3=value3).
  • SSL Method
    • The SSL configuration supports three modes: Unencrypted, Encrypted (trust server certificate), and Encrypted (verify certificate).
      • Unencrypted: Do not use SSL encryption on the database connection
      • Encrypted (trust server certificate): Use SSL encryption without verifying the server's certificate. This is useful for self-signed certificates in testing scenarios, but should not be used in production.
      • Encrypted (verify certificate): Use the server's SSL certificate, after standard certificate verification.
        • Host Name In Certificate (optional): When using certificate verification, this property can be set to specify an expected name for added security. If this value is present, and the server's certificate's host name does not match it, certificate verification will fail.
  • Load Type
    • The data load type supports two modes: Insert or Bulk
      • Insert: Utilizes SQL INSERT statements to load data to the destination table.
      • Bulk: Utilizes Azure Blob Storage and the BULK INSERT command to load data to the destination table. If selected, additional configuration is required:

MSSQL with Azure Blob Storage (Bulk Upload) Setup Guide​

This section describes how to set up and use the Microsoft SQL Server (MSSQL) connector with the Azure Blob Storage Bulk Upload feature. By staging data in an Azure Blob Storage container and using BULK INSERT, you can significantly improve ingestion speed and reduce network overhead for large or frequent data loads.

Why Use Azure Blob Storage Bulk Upload?​

When handling high data volumes or frequent syncs, row-by-row inserts into MSSQL can become slow and resource-intensive. By staging files in Azure Blob Storage first, you can:

  1. Aggregate Data into Bulk Files: Data is written to Blob Storage in batches, reducing overhead.
  2. Perform Bulk Ingestion: MSSQL uses BULK INSERT to directly load these files, typically resulting in faster performance compared to conventional row-by-row inserts.
Prerequisites​
  1. A Microsoft SQL Server Instance
    • Compatible with on-premises SQL Server or Azure SQL Database.
  2. Azure Blob Storage Account
    • A storage account and container (e.g., bulk-staging) where data files will be placed.
  3. Permissions
    • Blob Storage: ability to create, read, and delete objects within the designated container.
    • MSSQL: ability to create or modify tables, and permission to execute BULK INSERT.
Setup Guide​

Follow these steps to configure MSSQL with Azure Blob Storage for bulk uploads.

1. Set Up Azure Blob Storage​
  1. Create a Storage Account & Container
    • In the Azure Portal, create (or reuse) a Storage Account.
    • Within that account, create a container (e.g., bulk-staging) for staging your data files.
  2. Establish Access Credentials
    • Use a Shared Access Signature (SAS) scoped to your container.
    • Ensure the SAS token or role assignments include permissions such as Read, Write, Delete, and List.
2. Configure MSSQL​

See the official Microsoft documentation for more details. Below is a simplified overview:

  1. (Optional) Create a Master Encryption Key
    If your environment requires a master key to store credentials securely, create one:

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<your_password>';

  2. Create a Database Scoped Credential
    Configure a credential that grants MSSQL access to your Blob Storage using the SAS token:

    CREATE DATABASE SCOPED CREDENTIAL <credential_name>
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
         SECRET = '<your_sas_token>';

  3. Create an External Data Source
    Point MSSQL to your Blob container using the credential:

    CREATE EXTERNAL DATA SOURCE <data_source_name>
    WITH (
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://<storage_account>.blob.core.windows.net/<container_name>',
        CREDENTIAL = <credential_name>
    );

    You’ll reference <data_source_name> when configuring the connector.

3. Connector Configuration​

You’ll need to supply:

  1. MSSQL Connection Details
    • The server hostname/IP, port, database name, and authentication (username/password).
  2. Bulk Load Data Source
    • The name of the external data source you created (e.g., <data_source_name>).
  3. Azure Storage Account & Container
    • The name of the storage account and container.
  4. SAS Token
    • The token that grants Blob Storage access.

See the Getting Started: Configuration section of this guide for more details on BULK INSERT connector configuration.

Reference​

No configuration specification is available for this connector.

Changelog​

Expand to review
VersionDatePull RequestSubject
2.0.02025-03-1155684Release 2.0.0
2.0.0.rc132025-03-0755252RC13: Bugfix for OOM on Bulk Load
2.0.0.rc122025-03-0554159RC12: Support For Bulk Insert Using Azure Blob Storage
2.0.0.rc112025-03-0455193RC11: Increase decimal precision
2.0.0.rc102025-02-2454648RC10: Fix index column names with hyphens
2.0.0.rc92025-02-2154197RC9: Fix index column names with invalid characters
2.0.0.rc82025-02-2054186RC8: Fix String support
2.0.0.rc72025-02-1153364RC7: Revert deletion change
2.0.0.rc62025-02-1153364RC6: Break up deletes into loop to reduce locking
2.0.0.rc52025-02-0753236RC5: Use rowlock hint
2.0.0.rc42025-02-0653192RC4: Fix config, timehandling, performance tweak
2.0.0.rc32025-02-0453174RC3: Fix metadata.yaml for publish
2.0.0.rc22025-02-0452704RC2: Performance improvement
2.0.0.rc12025-01-2452096Release candidate
1.0.32025-01-1051497Use a non root base image
1.0.22024-12-1849891Use a base image: airbyte/java-connector-base:1.0.0
1.0.12024-11-04#48134Fix supported sync modes (destination-mssql 1.x.y does not support dedup)
1.0.02024-04-11#36050Update to Dv2 Table Format and Remove normalization
0.2.02023-06-27#27781License Update: Elv2
0.1.252023-06-21#27555Reduce image size
0.1.242023-06-05#27034Internal code change for future development (install normalization packages inside connector)
0.1.232023-04-04#24604Support for destination checkpointing
0.1.222022-10-21#18275Upgrade commons-text for CVE 2022-42889
0.1.202022-07-14#14618Removed additionalProperties: false from JDBC destination connectors
0.1.192022-05-25#13054Destination MSSQL: added custom JDBC parameters support.
0.1.182022-05-17#12820Improved 'check' operation performance
0.1.172022-04-05#11729Bump mina-sshd from 2.7.0 to 2.8.0
0.1.152022-02-25#10421Refactor JDBC parameters handling
0.1.142022-02-14#10256Add -XX:+ExitOnOutOfMemoryError JVM option
0.1.132021-12-28#9158Update connector fields title/description
0.1.122021-12-01#8371Fixed incorrect handling "\n" in ssh key
0.1.112021-11-08#7719Improve handling of wide rows by buffering records based on their byte size rather than their count
0.1.102021-10-11#6877Add normalization capability, add append+deduplication sync mode
0.1.92021-09-29#5970Add support & test cases for MSSQL Destination via SSH tunnels
0.1.82021-08-07#5272Add batch method to insert records
0.1.72021-07-30#5125Enable additionalPropertities in spec.json
0.1.62021-06-21#3555Partial Success in BufferedStreamConsumer
0.1.52021-07-20#4874declare object types correctly in spec
0.1.42021-06-17#3744Fix doc/params in specification file
0.1.32021-05-28#3728Change dockerfile entrypoint
0.1.22021-05-13#3367Fix handle symbols unicode
0.1.12021-05-11#3566MS SQL Server Destination Release!
Was this page helpful?