Copy data using ODBC fails: "Format of the initialization string [...] starting at index 0"

We ran into a frustrating issue in ADF where a Copy activity using an ODBC source (Databricks) consistently failed with the same error, despite the connection test and data preview working perfectly fine. This post describes the issue and we hope to get some help from the community :)

Our Setup:

- Source: Databricks (via ODBC linked service, self-hosted IR)

- Sink: Azure SQL Database

- ADF Component: Copy Activity

- Integration Runtime: Self-hosted IR

The goal is to simply copy a table from Databricks into Azure SQL. We also tried to copy only a couple rows but same error. 

What works:

- Test Connection on the ODBC linked service → passes

- Data Preview on the ODBC dataset → returns correct data

- Azure SQL sink linked service connection test → passes

And here is what goes wrong:

Running the Copy Activity in the pipeline gives:

```

ErrorCode=InvalidParameter,

'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,

Message=The value of the property '' is invalid:

'Format of the initialization string does not conform to

specification starting at index 0.',

Source=,

''Type=System.ArgumentException,

Message=Format of the initialization string does not conform to

specification starting at index 0.,

Source=System.Data,'

```

This is the source config:

ODBC Dataset:

```json

{

"type": "OdbcTable",

"typeProperties": {

"tableName": {

"value": "@concat(dataset().catalog, '.', dataset().schema, '.', dataset().table)",

"type": "Expression"

}

},

"parameters": {

"catalog": { "type": "string" },

"schema": { "type": "string" },

"table": { "type": "string" }

}

}

```

Copy Activity Source:

```json

"source": {

"type": "OdbcSource",

"queryTimeout": "02:00:00"

}

```

ODBC Connection String (sanitized):

```

Driver={Databricks ODBC Driver};

Host=<databricks-host>;

Port=443;

HTTPPath=/sql/1.0/warehouses/<warehouse-id>;

AuthMech=11;

Auth_Flow=1;

Auth_Client_ID=<client-id>;

Auth_Client_Secret=<client-secret>;

Auth_Scope=<scope>/.default;

OIDCDiscoveryEndpoint=https://login.microsoftonline.com/<tenant>/v2.0/.well-known/openid-configuration;

SSL=1;

ThriftTransport=2

```

- AuthMech=11 = Azure Active Directory authentication

- Auth_Flow=1 = Service Principal / Client Credentials via OIDC

- Self-hosted IR is used

What we tried:

1. Hardcoding catalog parameter instead of using the global parameter --> Same error 

2. With and without column mappings from Copy activity --> Same error 

3. Removing double curly braces around client secret in Terraform (i.e. from 

Auth_Client_Secret={${pwd}} to 

Auth_Client_Secret=${pwd})

--> Same error 

4. Using a query (top 10 rows) instead of table config in the source of copy activity  --> Same error 

5. Verifying all linked services and datasets work in preview --> All work fine 

We find the error confusing and are unsure how to continue. It suggests the issue is specific to how ADF handles the ODBC connection string during pipeline execution on the Self-hosted IR, as opposed to interactive operations (like preview data).

Any help is appreciated.

Thanks in advance!