none
Trouble creating linked service to on-premise MS Access database with Azure Data Factory V2 (Preview).

    Question

  • I am using Azure Data Factory V2 (Preview) and creating pipelines to shift on-premise data into Azure.  I have created and installed an Integration Runtime (IR) self hosting fine and am successfully moving data out of an on-premise SQL Server database into Azure Blob Storage.

    I need to do the same approach now for a Microsoft Access Database, which for this setup/test is sitting on my C: drive.

    Following instructions here;

    https://docs.microsoft.com/en-us/azure/data-factory/connector-odbc#microsoft-access-source

    Creating a new linked service in the Azure Data Factory UI tool, I'm picking ODBC and connecting via the existing named IR self-hosted runtime.

    For the connection string I'm trying the usual connection strings you'd use.  For example;

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\FolderA\FolderB\MyDatabase.mdb;

    I'm trying double back slashes in the filename path too but no success.

    Failed to connect to the database. Error message: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    In the Data Factory UI I have also tried using the Advanced JSON Editor to specify all the settings.  Have also tried these in the IR Config Manager Preview tool.

    {
    "name": "kCreateLinkedService",
    "properties": {
    "type": "Odbc",
    "typeProperties": {
    "connectionString": {
    "type": "SecureString",
    "value": "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\Program Files\\FolderA\\FolderB\\MyDatabase.mdb;Database Password=sometopsecretpwd;"
    },
    "authenticationType": "Anonymous",
    "userName": "<username>",
    "password": {
    "type": "SecureString",
    "value": "<password>"
    }
    },
    "connectVia": {
    "referenceName": "NVDIntegrationRuntime-SelfHosted-x42 ",
    "type": "IntegrationRuntimeReference"
    }
    }
    }

    Any ideas?

    Thanks,

    Richard.


    Friday, April 20, 2018 3:11 PM