locked
Azure datafactory v2 Execute Pipeline issue with escape character issue RRS feed

  • Question

  • I am trying to use "Execute Pipeline" to invoke a Pipe which has a ForEach activity. I get an error.

    1. Json for Execute pipe:

    <!-- lang: lang-js -->

        [
            {
                "name": "pipeline3",
                "properties": {
                    "activities": [
                        {
                            "name": "Test_invoke1",
                            "type": "ExecutePipeline",
                            "dependsOn": [],
                            "userProperties": [],
                            "typeProperties": {
                                "pipeline": {
                                    "referenceName": "MAIN_SA_copy1",
                                    "type": "PipelineReference"
                                },
                                "waitOnCompletion": true
                            }
                        }
                    ],
                    "annotations": []
                }
            }
        ]

    2. Jason for Invoke pipe for each activity : 

    <!-- lang: lang-js -->

        [
            {
                "name": "MAIN_SA_copy1",
                "properties": {
                    "activities": [
                        {
                            "name": "Collect_SA_Data",
                            "type": "ForEach",
                            "dependsOn": [],
                            "userProperties": [],
                            "typeProperties": {
                                "items": {
                                    "value": "@pipeline().parameters.TableNames",
                                    "type": "Expression"
                                },
                                "batchCount": 15,
                                "activities": [
                                    {
                                        "name": "Sink_SAdata_toDL",
                                        "type": "Copy",
                                        "dependsOn": [],
                                        "policy": {
                                            "timeout": "7.00:00:00",
                                            "retry": 0,
                                            "retryIntervalInSeconds": 30,
                                            "secureOutput": false,
                                            "secureInput": false
                                        },
                                        "userProperties": [
                                            {
                                                "name": "Destination",
                                                "value": "@{pipeline().parameters.DLFilePath}/@{item()}"
                                            }
                                        ],
                                        "typeProperties": {
                                            "source": {
                                                "type": "SqlServerSource",
                                                "sqlReaderQuery": {
                                                    "value": "@concat('SELECT * FROM ',item())",
                                                    "type": "Expression"
                                                }
                                            },
                                            "sink": {
                                                "type": "AzureBlobFSSink"
                                            },
                                            "enableStaging": false,
                                            "parallelCopies": 1,
                                            "dataIntegrationUnits": 4
                                        },
                                        "inputs": [
                                            {
                                                "referenceName": "SrcDS_StructuringAnalytics",
                                                "type": "DatasetReference"
                                            }
                                        ],
                                        "outputs": [
                                            {
                                                "referenceName": "ADLS",
                                                "type": "DatasetReference",
                                                "parameters": {
                                                    "FilePath": "@pipeline().parameters.DLFilePath",
                                                    "FileName": {
                                                        "value": "@concat(item(),'.orc')",
                                                        "type": "Expression"
                                                    }
                                                }
                                            }
                                        ]
                                    }
                                ]
                            }
                        }
                    ],
                    "parameters": {
                        "DLFilePath": {
                            "type": "string",
                            "defaultValue": "extracts/StructuringAnalytics"
                        },
                        "TableNames": {
                            "type": "array",
                            "defaultValue": [
                                "fom.FOMLineItem_manual"
                            ]
                        }
                    },
                    "variables": {
                        "QryTableColumn": {
                            "type": "String"
                        },
                        "QryTable": {
                            "type": "String"
                        }
                    },
                    "folder": {
                        "name": "StructuringAnalytics"
                    },
                    "annotations": []
                },
                "type": "Microsoft.DataFactory/factories/pipelines"
            }
        ]


    I get an error: 

    <!-- lang: lang-js -->

        [
            {
                "errorCode": "BadRequest",
                "message": "Operation on target Collect_SA_Data failed: The execution of template action 'Collect_SA_Data' failed: the result of the evaluation of 'foreach' expression '@pipeline().parameters.TableNames' is of type 'String'. The result must be a valid array.",
                "failureType": "UserError",
                "target": "Test_invoke1",
                "details": ""
            }
        ]

    Input: 

    <!-- lang: lang-js -->

        "pipeline": {
            "referenceName": "MAIN_SA_copy1",
            "type": "PipelineReference"
        },
        "waitOnCompletion": true,
        "parameters": {
            "DLFilePath": "extracts/StructuringAnalytics",
            "TableNames": "[\"fom.FOMLineItem_manual\"]"
        }
    Update: added @array which solved the array error , also added sequential which solved the select statement issue, now facing escape character issue. 

    Input ForEach activity is :

    Input { "source": { "type": "SqlServerSource", "sqlReaderQuery": "SELECT * FROM [\"fom.FOMLineItem_manual\"]" }, "sink": { "type": "AzureBlobFSSink" –

    Error:  SQL error: "Code": 11000, "Message": "Failure happened on 'Source' side. 'Type=System.Data.SqlClient.SqlException,Message=Invalid object name '\"fom.FOMLineItem_manual\"'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=208,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=208,State=1,Message=Invalid object name '\"fom.FOMLineItem_manual\"'.,},]


    Friday, April 10, 2020 11:45 PM

All replies

  • Update: added @array which solved the array error , also added sequential which solved the select statement issue, now facing escape character issue. 
    Friday, April 10, 2020 11:45 PM
  • Hi Twinkle2020,

    Thanks for reaching out here and sincere apologizes for the late response. 

    From the below input you have provided, I see that the input parameter TableNames is having a value that contains back slash in it, could you please tell us where you are getting that TableNames values in your parent pipeline (Execute activity)? This is what causing your pipeline to fail due to invalid select query formation [Error: Invalid object name '\"fom.FOMLineItem_manual\"']. It is expecting input as `"TableNames": "["fom.FOMLineItem_manual", "fom.FOMLineItem_manual2", "fom.FOMLineItem_manual3", "fom.FOMLineItem_manual4",........]"`


    Incorrect input parameter from Parent pipeline (Execute activity):


        "pipeline": {
            "referenceName": "MAIN_SA_copy1",
            "type": "PipelineReference"
        },
        "waitOnCompletion": true,
        "parameters": {
            "DLFilePath": "extracts/StructuringAnalytics",
            "TableNames": "[\"fom.FOMLineItem_manual\"]"
        }

    Correct input parameter from Parent pipeline (Execute activity):

        "pipeline": {
            "referenceName": "MAIN_SA_copy1",
            "type": "PipelineReference"
        },
        "waitOnCompletion": true,
        "parameters": {
            "DLFilePath": "extracts/StructuringAnalytics",
            "TableNames": "["fom.FOMLineItem_manual"]"
        }
    When I tried with below input from Parent pipeline it went through without issues:
    {
        "pipeline": {
            "referenceName": "childPipeline1",
            "type": "PipelineReference"
        },
        "waitOnCompletion": true,
        "parameters": {
            "TableNames": [
                "fom.FOMLineItem_manual",
                "fom.FOMLineItem_manual2",
                "fom.FOMLineItem_manual3",
                "fom.FOMLineItem_manual4"
            ]
        }
    }


    Please make sure that your parent pipeline pass correct (excluding back slash) input parameter (TableNames) value to your child pipeline and let us know how it goes.

    I look forward for your response. 



    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered"Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Wednesday, April 15, 2020 8:07 PM
  • Hi Twinkle2020,

    Following up to see if you have got a chance to see my previous comment. Let us know how it goes.

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, April 16, 2020 8:12 PM