Contents x
    ODBC Datasources
    • 03 Mar 2025
    • 1 Minute to read
    • Dark
      Light
    Contents

    ODBC Datasources

    • Updated on 03 Mar 2025
    • 1 Minute 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 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 do not offer support for specific ODBC drivers or data sources.
    • 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