Search code examples
azureodbcazure-data-factoryetlazure-data-lake

Auto create table in ADF copy activity for ODBC Sink Dataset (Not Working)

I am trying to perform copy activity between ADLS dataset and ODBC sync dataset (POSTGRESQL) But, table is not creating automatically in target dataset and error coming like

Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [42P01] ERROR: relation "semantic_dev.dim_storage_location" does not exist;\nError while preparing parameters,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [42P01] ERROR: relation "semantic_dev.dim_storage_location" does not exist;\nError while preparing parameters,Source=PSQLODBC35W.DLL**

How can I perform copy activity WITH AUTO CREATE TABLE FOR odbc CONNECTOR or any other way I can create linked services for postgresql and sync dataset because I am not able to create sink dataset for postgresql

I tried ODBC sink dataset for my postgresql

Dataset JSON

{
    "name": "AZR_DS_PSQL_PROC_LAYER",
    "properties": {
        "linkedServiceName": {
            "referenceName": "AZR_LS_PSQL_ODBC_STRL_POC",
            "type": "LinkedServiceReference"
        },
        "parameters": {
            "Schema_name": {
                "type": "string"
            },
            "Table_name": {
                "type": "string"
            }
        },
        "annotations": [],
        "type": "OdbcTable",
        "schema": [],
        "typeProperties": {
            "tableName": {
                "value": "@concat(dataset().Schema_name,'.',dataset().Table_name)",
                "type": "Expression"
            }
        }
    },
    "type": "Microsoft.DataFactory/factories/datasets"
}

enter image description here

enter image description here

Solution

  • ODBC as a sink does not support auto create table option in ADF. Also, PostgreSQL database as a sink is not supported in ADF. You can use precopy activity of ODBC and write the query to create the table. enter image description here

    This will make sure that the table is created before you load the data to sink.