This connector allows to connect to a SQL Server database and execute SQL queries.
Requirements #
ODBC Driver 18 must be installed on the executing computer.
- For Linux, follow the instructions from Microsoft documentation
- For Windows, follow the instructions from Microsoft documentation
- For macOS, follow the instructions from Microsoft documentation
Connection configuration #
Password mode #
Definition #
| Name | Mandatory | Default | Description |
|---|---|---|---|
| mode | no | password | Change the connection mode. Can be “password” or “connection_string”. “connection_string” mode allow to use a custom connection string. |
| hostname | yes | Target host name | |
| database | yes | Target database name | |
| username | yes | Sql user name | |
| password | yes | Sql user password | |
| port | no | 1433 | Port to use by the connection |
| trust_server_certificate | no | false | Trust the server ssl connection |
| encrypt | no | yes | Encrypt the connection |
| driver | no | ODBC Driver 18 for SQL Server | Driver to use by the connection |
⚠️ it’s highly recommended to use a parameter to pass the password value
Example #
mssql_example:
type: mssql
hostname: ploosh.database.windows.net
database: SampleDB
username: sa_ploosh
password: $var.sa_ploosh_password Definition #
Connection string mode #
| Name | Mandatory | Default | Description |
|---|---|---|---|
| mode | no | password | Use “connection_string” value to use custom connection_string |
| connection_string | yes | Connection string use to access in the database. Refer to SQLAlchemy documentation to get the accepted format |
Example #
mssql_example:
type: mssql
mode: connection_string
connection_string: "mssql+pyodbc://ploosh01:1433/SampleDB?driver=ODBC+Driver+18+for+SQL+Server&TrustServerCertificate=yes&authentication=ActiveDirectoryIntegrated"Test case configuration #
Definition #
| Name | Mandatory | Default | Description |
|---|---|---|---|
| connection | yes | The connection to use | |
| query | yes | The query to execute to the database |
Example #
Example SQL Server:
source:
connection: mssql_example
type: mssql
query: |
select *
from [rh].[employees]
where [hire_date] < '2000-01-01'
expected:
type: csv
path: data/employees_before_2000.csv
