Passing triggerID to the destination column

  • ammit.it2006

    Ten Centuries

    Points: 1233

    Hi

    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 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": []

    }

    }

  • Site Owners

    SSC Guru

    Points: 80378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • dave-dj

    SSChampion

    Points: 11292

    Hi

    If I have correctly understood what you are trying to do, you want to pass a pipeline parameter as part of the data copy from source to destination, inserting the pipeline parameter as a new column (or even replacing an existing value) into your destination table

    If that is correct, the way to do this is as follows:

    1. in your sql database create used defined  table type under Programmability > User-Defined Table Type

      (i defined my table with the same columns as my destination table - but essentially you need this to be the data the source table columns)

    2. Create a stored procedure to include the table type and any proc parameters.... for example:

      create proc MyInsertProc @MyDefinedTableTypeParameter [dbo].[MyDefinedTableType] READONLY, @TriggerID int

      @MyDefinedTableTypeParameter - this is a variable for your table type

      [dbo].[MyDefinedTableType] - this will be your defined table type object reference that you defined in step 1.  In addition to this of course  you will to define what your procedure does.... i.e. insert the record into a destination table!

    3. Within you data factory pipeline select your copy task, for the sink, specify your stored procedure create in step 2.  You can then click import parameters and it will pull in the proc parameters.

      3.1 Specify the table type as [dbo].[MyDefinedTableType] (or as you have named it)

      3.2 specify the table type parameter as MyDefinedTableTypeParameter (or again as per your named defined in step 2.

      3.3 Map your pipeline parameters to your proc parameters as required.

    4.  define your source to sink mappings

      I hope that helps you out - i can see the post is from a couple of months ago, but I've only recently been through the same process.

    Let me know if its useful 🙂

     

     

    _____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply