Load Microsoft SQL Server data to BigQuery

Microsoft SQL Server is a relational database management system developed by Microsoft. This page has everything needed to load Microsoft SQL Server tables into BigQuery with dlt's sql_database source: the SQLAlchemy driver, the connection string, how to choose tables and load only new rows, plus the steps that hand the job to your coding agent.

---

Build your Microsoft SQL Server to BigQuery pipeline

Set up a project, install the dltHub AI harness and its sql-database-pipeline toolkit, and let your coding agent build the pipeline:

Copy
uv init
uv add "dlt[hub,sql_database,bigquery]" pyarrow pandas pyodbc
uv run dlthub ai init --agent <your-agent> # <agent>: claude | cursor | codex
uv run dlthub ai toolkit install sql-database-pipeline

Then paste this prompt into Claude, Codex, or Cursor:

The toolkit lists the schemas and tables in your database, asks which tables to load, then scaffolds a one-table pipeline and runs it with a load limit before scaling up. Credentials are inspected through MCP tools, so your agent never reads secrets.toml itself. About the toolkit →

Prefer to write it yourself? Every fact the agent uses is below.

---

Microsoft SQL Server at a glance

---

How do I connect to Microsoft SQL Server?

dlt needs a database user with read access to the tables you want to load. Add its connection details to .dlt/secrets.toml:

Copy
[sources.sql_database.credentials]
drivername = "mssql+pyodbc"
host = "localhost"
port = 1433
username = "my_user"
password = "my_password"
database = "my_database"
query = { "driver" = "ODBC Driver 18 for SQL Server" }

Or as a single connection string:

Copy
[sources.sql_database]
credentials = "mssql+pyodbc://my_user:my_password@localhost:1433/my_database?driver=ODBC+Driver+18+for+SQL+Server"

Use SQLAlchemy's mssql dialect with a supported driver such as pyodbc; the mssql+pyodbc connection requires pyodbc and, for hostname connections, an installed ODBC driver whose URL name is supplied in the lowercase driver query parameter. SQLAlchemy also supports the mssql+mssqlpython dialect in version 2.1 and later, which does not require a separately installed ODBC driver.

BigQuery credentials go in the same file; the BigQuery destination docs list the fields it needs. dlt reads .dlt/secrets.toml automatically at runtime — never hardcode credentials in your pipeline script. For production, see setting up credentials with dlt.

---

Which Microsoft SQL Server tables can I load into BigQuery?

Any table or view the database user can read. The sql_database source reads each table's schema from Microsoft SQL Server and creates a matching table in BigQuery. These options narrow a load down to what you need:

Always pass table_names=[...] to sql_database(): without it, dlt reflects every table in the schema before loading anything.

---

How do I load only new Microsoft SQL Server rows?

Give each table a cursor column and a primary key, and dlt loads only rows changed since the last run and merges them into BigQuery:

Copy
source.orders.apply_hints(
    incremental=dlt.sources.incremental("updated_at"),
    write_disposition="merge",
    primary_key="id",
)

Use a timestamp such as updated_at for tables whose rows change, and an increasing id for append-only tables. On the first run dlt loads everything; after that it reads only rows past the last cursor value. See incremental loading.

---

What does the Microsoft SQL Server to BigQuery pipeline look like?

This loads the customers and orders tables from the dbo schema into BigQuery. Replace the table names with your own:

Copy
import dlt
from dlt.sources.sql_database import sql_database


def load_microsoft_sql_server_to_bigquery() -> None:
    source = sql_database(
        schema="dbo",
        table_names=["customers", "orders"],
        backend="pyarrow",
    )
    source.orders.apply_hints(
        incremental=dlt.sources.incremental("updated_at"),
        write_disposition="merge",
        primary_key="id",
    )

    pipeline = dlt.pipeline(
        pipeline_name="microsoft_sql_server_pipeline",
        destination="bigquery",
        dataset_name="microsoft_sql_server_data",
    )
    load_info = pipeline.run(source)
    print(load_info)


if __name__ == "__main__":
    load_microsoft_sql_server_to_bigquery()

Credentials are not passed in the code: sql_database() reads them from [sources.sql_database] in .dlt/secrets.toml. Run it with uv run python microsoft_sql_server_pipeline.py.

---

Which backend should I use?

The backend decides how rows are read from Microsoft SQL Server. The example uses pyarrow; switch if your data calls for it:

The toolkit's /optimize-sql-performance skill measures where time goes and picks a backend, chunk size and parallelism for you. See configuring the backend.

---

How do I query Microsoft SQL Server data in BigQuery?

After a run, pipeline.dataset() holds the loaded tables. A Relation is a query on that dataset, either a whole table or a SQL string, and reading it gives you the data as a Pandas DataFrame, a PyArrow table or Python tuples. Nothing is read until you ask for it.

Copy
import dlt

pipeline = dlt.pipeline("microsoft_sql_server_pipeline")

# Step 1: Get the dataset from the pipeline
dataset = pipeline.dataset()

# Step 2: Get a Relation, either a whole table...
orders = dataset.table("orders")
# ...or any SQL query against BigQuery
recent = dataset("SELECT * FROM orders ORDER BY updated_at DESC LIMIT 100")

# Step 3: Read the data
df = orders.df()  # or orders.df(chunk_size=50) for a large table
arrow_table = recent.arrow()  # a PyArrow Table
rows = recent.fetchall()  # a list of Python tuples

The columns are the ones of your Microsoft SQL Server tables; dataset.row_counts().df() lists every table with its row count. Your agent can run the same queries through the MCP server's execute_sql_query tool, and uv run dlthub show opens the Pipeline Dashboard with the schema, load metrics and a query box. See access loaded data in Python and exploring it in marimo notebooks.

---

How do I deploy the Microsoft SQL Server to BigQuery pipeline in production?

The pipeline runs locally, which is ideal for prototyping and one-off loads. When you need it on a schedule, monitored on every load, and shared with your team, deploy the same dlt code on the dltHub platform — no infrastructure to maintain. The harness's dlthub-platform toolkit takes your agent there:

Copy
uv run dlthub ai toolkit install dlthub-platform

• Deploy & schedule — run the pipeline as a managed job with automatic retries.
• Monitor — observable job queues, alerting, and load metrics for every run.
• Transform — promote raw Microsoft SQL Server loads into governed, documented models.
• Visualize & share — explore data in notebooks and publish live dashboards instead of static screenshots.

Book a demo →

---

What other destinations can I load Microsoft SQL Server data to?

dlt loads into any of these — only the destination argument changes:

Set dlt.pipeline(destination="snowflake") and add that destination's credentials in .dlt/secrets.toml. See the full destinations list.

---

Next steps

• Ready for production? Book a demo of the dltHub platform →
• SQL database source reference
• The sql-database-pipeline toolkit
• Join the dlt community on Slack