Azure Data factory parameter

Question

I am trying to load the data from the on-premise sql server to Sql Server at VM. I need to do it every day. For the same, I have created a trigger. Trigger is inserting the data properly. But now, I need to insert triggerID in the destination columns for every run in a column.

I don't know what mistake i am doing. I found many blogs on the same but all have information when we are extracting the data from a blob not from sql server.

I was trying to insert the value of the same like this but it's giving error. "Activity Copy Data1 failed: Please choose only one of the three property "name", "path" and "ordinal" to reference columns for "source" and "sink" under "mappings" property. " pipeline details. Please suggest

{
    "name": "pipeline1",
    "properties": {
        "activities": [
            {
                "name": "Copy Data1",
                "type": "Copy",
                "dependsOn": [],
                "policy": {
                    "timeout": "7.00:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "AzureSqlSource"
                    },
                    "sink": {
                        "type": "SqlServerSink"
                    },
                    "enableStaging": false,
                    "translator": {
                        "type": "TabularTranslator",
                        "mappings": [
                            {
                                "source": {
                                    "name": "Name",
                                    "type": "String"
                                },
                                "sink": {
                                    "name": "Name",
                                    "type": "String"
                                }
                            },
                            {
                                "source": {
                                    "type": "String",
                                    "name": "@pipeline().parameters.triggerIDVal"
                                },
                                "sink": {
                                    "name": "TriggerID",
                                    "type": "String"
                                }
                            }
                        ]
                    }
                },
                "inputs": [
                    {
                        "referenceName": "AzureSqlTable1",
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "SqlServerSQLDEV02",
                        "type": "DatasetReference"
                    }
                ]
            }
        ],
        "parameters": {
            "triggerIDVal": {
                "type": "string"
            }
        },
        "annotations": []
    }
}

I want that each time trigger is executed then the triggerID should be populating into the destination column TriggerID.

Answer 1

Firstly,please see the limitation in the copy activity column mapping:

• Source data store query result does not have a column name that is specified in the input dataset "structure" section.
• Sink data store (if with pre-defined schema) does not have a column name that is specified in the output dataset "structure" section.
• Either fewer columns or more columns in the "structure" of sink dataset than specified in the mapping.
• Duplicate mapping.

So,i don't think you could do the data transfer plus trigger id which is not contained by the source columns.My idea is:

1.First use a Set Variable activity to get the trigger id value.

2.Then connect with copy activity and pass the value as parameter.

3.In the sink of copy activity, you could invoke stored procedure to combine the trigger id with other columns before the row is inserted into table. More details, please see this document.