< Marvin> Why do I receive this error Task run failed with e Prefect Community #ask-marvin

<@ULVA73B9P> Why do I receive this error? Task run...

Ed Hartley

09/03/2025, 4:31 PM

@Marvin Why do I receive this error? Task run failed with exception: InterfaceError("(pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')"

Copy code

connector = SqlAlchemyConnector(
    connection_info=ConnectionComponents(
        driver=SyncDriver.MSSQL_PYODBC,
        username="XX",
        password="XX",
        host="XX",
        query={
            "driver": "ODBC Driver 17 for SQL Server"
        }
    )
)

connector.save("fqrs-sqlserver", overwrite=True)

@task
def load_data():
    with SqlAlchemyConnector.load("fqrs-sqlserver") as connector:
        data = connector.fetch_all("SELECT top 10 * FROM [DW].[dbo].[Freemans]")
        return data

Marvin

09/03/2025, 4:31 PM

✅ thought for 150.8 seconds

Marvin

09/03/2025, 4:33 PM

That error means the ODBC driver you’re referencing in the connection string isn’t available in the environment where the task actually runs. In other words, pyodbc asked the OS ODBC Driver Manager for “ODBC Driver 17 for SQL Server” and it couldn’t find a driver with that exact name (or any default). Common causes: - The Microsoft ODBC driver for SQL Server is not installed on the worker/container/VM running your flow. - The installed version is different (e.g., only “ODBC Driver 18 for SQL Server” is installed but your string says 17). - The driver name string doesn’t exactly match what’s registered on the system. - If running in Docker/Kubernetes, your image likely doesn’t include the MS ODBC driver packages. How to fix: 1) Verify which ODBC drivers are installed where your flow runs - Linux/macOS (unixODBC): run

odbcinst -q -d

- Windows: open “ODBC Data Sources” → Drivers tab Ensure you see exactly “ODBC Driver 17 for SQL Server” or “ODBC Driver 18 for SQL Server”. 2) Install the driver if missing (match the name you’ll use in your query) - Windows: Install “Microsoft ODBC Driver for SQL Server” from Microsoft. - Linux: Install msodbcsql17 or msodbcsql18 and unixODBC. For example on Ubuntu: - Add Microsoft repo, then: -

sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18 unixodbc

- If you’re using a Docker work pool, add the install steps to your Dockerfile so the worker image has the driver. 3) Make the driver string match what’s installed - If you installed v18, change your block to:

Copy code

connector = SqlAlchemyConnector(
         connection_info=ConnectionComponents(
             driver=SyncDriver.MSSQL_PYODBC,
             username="XX",
             password="XX",
             host="XX",
             database="YourDatabase",
             query={
                 "driver": "ODBC Driver 18 for SQL Server",
                 # Add if needed for your server’s TLS setup:
                 # "TrustServerCertificate": "yes"
             }
         )
     )

- If connecting to a named instance or non-default port, use

host="server\\instance"

host="server,1433"

. Notes: - The

database

parameter is optional but typically required for most SQL Server connections; include it unless you have a specific reason not to. - If you switch to ODBC Driver 18, be aware it defaults to Encrypt=yes; you may need

TrustServerCertificate=yes

or proper certificates, otherwise you’ll get a different TLS-related error (not IM002). Docs and references: - Prefect SQLAlchemy integration: prefect-sqlalchemy docs - SQLAlchemy SQL Server + pyodbc URL docs: SQLAlchemy mssql+pyodbc - Microsoft ODBC driver install: Install Microsoft ODBC Driver for SQL Server If you can share: - What OS/container your flow runs on (local, Docker work pool, Kubernetes, etc.) - The output of

odbcinst -q -d

(or driver list on Windows) …I can tailor the exact install steps and confirm the correct

query["driver"]

value.

Open in Slack

Previous Next