Question 103052 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – 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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    For Copy activity of ADLS to PostgreSQL, we can't choose an auto-create table on the sync side for that, I used Precopy Script before running copy activity, parametrising all the columns and after reading from adls. and checking to DB if it is not exist then create. I solved using this approaches


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search