Microsoft SQL Server
Microsoft ODBC driver for SQL Server must be installed to use this destination.
This can't be included with
dlts python dependencies so you must installed it separately on your system.
See instructions here to install Microsoft ODBC Driver 18 for SQL Server on Windows, Mac and Linux
Following ODBC drivers are supported:
- ODBC Driver 18 for SQL Server
- ODBC Driver 17 for SQL Server You configure driver name explicitly as well.
Create a pipeline
1. Initalize a project with a pipeline that loads to MS SQL by running
dlt init chess mssql
2. Install the necessary dependencies for MS SQL by running
pip install -r requirements.txt
pip install dlt[mssql]
This will install dlt with mssql extra which contains all the dependencies required by the sql server client.
3. Enter your credentials into
Example, replace with your database connection info:
database = "dlt_data"
username = "loader"
password = "<password>"
host = "loader.database.windows.net"
port = 1433
connect_timeout = 15
You can also pass a SQLAlchemy-like database connection:
# keep it at the top of your toml file! before any section starts
To pass credentials directly you can use
credentials argument passed to
pipeline = dlt.pipeline(pipeline_name='chess', destination='postgres', dataset_name='chess_data', credentials="mssql://loader:<password>@loader.database.windows.net/dlt_data?connect_timeout=15")
All write dispositions are supported
If you set the
replace strategy to
staging-optimized the destination tables will be dropped and
recreated with an
ALTER SCHEMA ... TRANSFER. The operation is atomic: mssql supports DDL transactions.
Data is loaded via INSERT statements by default. MSSQL has a limit of 1000 rows per INSERT and this is what we use.
Supported file formats
- insert-values is used by default
Supported column hints
mssql will create unique indexes for all columns with
unique hints. This behavior may be disabled
This destination fully supports dlt state sync
MS SQL does not support JSON columns, so JSON objects are stored as strings in
Additional destination options
mssql destination does not creates UNIQUE indexes by default on columns with
unique hint (ie.
_dlt_id). To enable this behavior
You can explicitly set the ODBC driver name:
odbc_driver="ODBC Driver 18 for SQL Server"
No dbt support yet