Contents x
    ODBC Datasources
    • 10 Apr 2025
    • 2 Minutes to read
    • Dark
      Light
    Contents

    ODBC Datasources

    • Updated on 10 Apr 2025
    • 2 Minutes to read
    • Dark
      Light
    Article summary

    Introduction

    These types of datasources represent a connection to an ODBC database or datasource.

    Free and commercial ODBC drivers are available to many data sources such as Databricks, Snowflake, Dremio, Greenplum and Power BI. Below is a simple example showing how to connect to an Excel file.

    Note
    • This is available in version 3.5.11 and later.
    • We are not able to offer support for specific ODBC drivers or data sources. ODBC Drivers will need to support a certain minimum level of functionality - including importantly, parameterised queries - for all SQL functionality to be supported.
    • Your ODBC Connection string (or underlying DSN) should include the Schema you are connecting to.
      • If you specify Field, Table or View SQL queries in the connection (see below) these should also specify the schema as a WHERE clause.
    • If you are using Mail & Deploy SaaS please contact us if you would like to discuss installing an ODBC driver on your instance.

    Properties

    This type of datasource has the following properties:

    Name

    		Description
    Connection StringConnection string to the ODBC data source.

    Examples:
    DSN=my_db_dsn;PWD=password123;
    DSN=my_other_db_dsn
    Driver={MyDbDriver};Server=yourdbserver.com;Database=SAMPLE_DATA;Schema=SCHEMA1;Uid=USERA;Pwd=password123;
    Field Query SQLA SQL query which returns a table containing TABLE_NAME and COLUMN_NAME columns and rows for all combinations of tables and column names.

    This is optional, if it is set it will be used to populate the 'Fields' entries in the Datasource Browser in the Mail & Deploy Designer.

    Example:
    SELECT tableName As TABLE_NAME, columnName As COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='MY_SCHEMA' AND TABLE_CATALOG='MY_DATABASE_NAME'
    Table Query SQLA SQL query which returns a table containing a TABLE_NAME column and rows for all of the tables in the datasource.

    This is optional, if it is set it will be used to populate the 'Tables' entries in the Datasource Browser in the Mail & Deploy Designer.

    Example:
    SELECT distinct TableName as TABLE_NAME FROM system_tables
    View Query SQLA SQL query which returns a table containing a TABLE_NAME column and rows for all of the views in the datasource.

    This is optional, if it is set it will be used to populate the 'Views' entries in the Datasource Browser in the Mail & Deploy Designer.

    Example:
    SELECT TABLE_NAME FROM sys_views

    Example

    You can connect to an Excel file using a connection string like this:

    Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\path\to\your\file\Test Source.xlsx;Extended Properties='READONLY=TRUE'

    And here are some example queries which you could use in Table Element in the report designer:

    Note

    You should make sure that the Excel file is closed when you access it via the ODBC driver.

    SELECT * from [Sheet1$]
SELECT * FROM [MyRange]
SELECT * FROM [Sheet1$A1:C2]
SELECT B, sum(A) as count FROM [MyRange] group by B

    You may find other examples posted here from time to time.

    Was this article helpful?
    What's Next