none
ODBC error in Data Factory after on-prem integration runtime restart RRS feed

  • Question

  • Hello,

    I try to fetch data from a MS Access DB (.accdb) in Azure Data Factory via an ODBC connection while using the on-premise integration runtime. The first time that the data copy pipeline is running, it executes without a problem and data is transferred. Unfortunately, on every following pipeline execution the data transfer fails.

    If I stop and re-start the Microsoft Integration Runtime the pipeline executes the first time without problems. After the first time all following times it fails again, until I restart the integration runtime once more.

    However, when I am testing the connection string in the Microsoft Integration Runtime diagnostics tab it seem that it works without a problem, no matter if the pipeline fails.

    The ODBC Connection string is:

    Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\path\\filename.accdb;
    

    And the authentication method is anonymous.

    The error code of the failure is:

    {
        "errorCode": "2200",
        "message": "ErrorCode=UserErrorFailedToConnectOdbcSource,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=,Source=,'",
        "failureType": "UserError",
        "target": "pipeline name"
    }

    My work around is to restart the Integration Runtime periodically via a power shell script (see below). However this is just a hack since all other pipelines that do not use an ODBC connection to Access DBs and they use on-prem SQL connections work without restarting the service.

    Restart-Service DIAHostService -Force


    • Edited by ChristosTs Wednesday, May 22, 2019 1:01 PM workaround added
    Wednesday, May 22, 2019 7:58 AM

All replies

  • Hello ChristosTs and thank you for your inquiry.  Could you please tell me what version of the self-hosted Integration Runtime you are using?  Also, if you are comfortable, please send me the logs generated by the Integration Runtime for both success and fail.
    Wednesday, May 22, 2019 6:02 PM
    Moderator