none
Unable to evalute the expression in the parameter of the pipeline

    Question

  • Hi ,
    My actual requirement is  dump data from MsAccess Source to the Azure SQL Database.
    I have create pipeline named "GetExistingIDs" and another pipeline as "Migrate Data from Ms Access to Azure SQL".
    In the Third Pipeline "Pipleline1" connected these two Pipelines. "GetExistingIDs" Execute Pipeline Activity output will be input paramter of the execute pipeline "Migrate Data from Ms Access to Azure SQL". 
     The output will of the "GetExistingIDs" Pipleline is as shown below:

     {
        "firstRow": {
            "PIDs": "'XXX1291','XXX1293','XXX1321','XXX1325','XXX1330','XXX1340','XXX1355','XXX1358','XXX1361'"
    }
    }

    Relation Source Query in  "Migrate Data from Ms Access to Azure SQL" Pipleline:
    SELECT t.* FROM [tblCPP070: P Info (Raw)] t where PID  not in (@{pipeline().parameters.t.PIDs})

    Input Paramter "t" Expression  : @json(activity('Execute Pipeline1').output).firstRow

    Error:

    { "errorCode": "BadRequest", "message": "The expression 'json(activity('Execute Pipeline1').output).firstRow' cannot be evaluated because property 'firstRow' doesn't exist, available properties are 'pipelineRunId, pipelineName'.", "failureType": "UserError", "target": "Migrate From MS Access To Azure" }

    Query :

    1.Expression not evaluted in the input paramter as well as in the SQL query .
    2.When Access the Ms Access Via Relational Source why tables are not listed automatically as dropp down as we have the table drop down list in the oracle a and other source connections. Forcing to write the Query to access  the table details like this "SELECT t.* FROM [tblCPP070: P Info (Raw)]". Why so?

     How to proceed further?

    Monday, June 11, 2018 8:05 AM

Answers

  • For 1, try to put the two activities in the same pipeline as following. And input your query as following.

    SELECT t.* FROM [tblCPP070: P Info (Raw)] t where PID  not in (@{activity('Lookup1').output.firstRow.PIDs})

    For 2, Did you see any details error message if you click the error symbol? is there an activity id in it? Could you share it to me?


    • Edited by Fang Liu (ADMS) Tuesday, June 12, 2018 3:05 PM
    • Marked as answer by SBH001 Wednesday, June 13, 2018 10:02 AM
    Tuesday, June 12, 2018 3:03 PM

All replies

  • Hi, please try '@{activity('Execute Pipeline1').output.firstRow}' as the expression for Paramter "t".
    Monday, June 11, 2018 9:04 AM
  • 1. For 1, activity Execute Pipeline1 just output a pipeline ID and a pipeline name. You can't access the output of inner pipeline.

    In my opinion, you just need one pipeline. 

    In pipeline1, you have a lookup activity named "lookup1", and then followed a copy activity. Then in your copy, you could use @{activity("lookup1').output.firstRow} as Wang metioned

    2. For 2, is your table a table or a view? If it is a view, then it is a known issue. The PROD team is fixing it. If it is a table, did you try refresh button? 


    Monday, June 11, 2018 11:53 AM
  • Hi,

    Tried the above mentioned solution, but it is not working. 

    Note: "t" expression is object type.

    Getting Error as :

    { "errorCode": "BadRequest", "message": "The expression 'activity('Execute Pipeline1').output.firstRow' cannot be evaluated because property 'firstRow' cannot be selected. Property selection is not supported on values of type 'String'.", "failureType": "UserError", "target": "Migrate From MS Access To Azure" }

    Actually expression is not evaluating in the parameter.

    Any other solution plz..?

    Tuesday, June 12, 2018 2:23 PM
  • Hi,

    above Solution is not working. For query 2# : yes it is a view. i have both table and view but even i could not get the tables as list even when i click refresh button. throwing as error symbol near the refresh symbol.


    Tuesday, June 12, 2018 2:25 PM
  • For 1, try to put the two activities in the same pipeline as following. And input your query as following.

    SELECT t.* FROM [tblCPP070: P Info (Raw)] t where PID  not in (@{activity('Lookup1').output.firstRow.PIDs})

    For 2, Did you see any details error message if you click the error symbol? is there an activity id in it? Could you share it to me?


    • Edited by Fang Liu (ADMS) Tuesday, June 12, 2018 3:05 PM
    • Marked as answer by SBH001 Wednesday, June 13, 2018 10:02 AM
    Tuesday, June 12, 2018 3:03 PM
  • Hi ,

    Thank you. This solution is working fine but if i need to reuse this as pipeline with multiple pipeline without redo in that case parameter is not working. what is the solution in this case?

    For Query 2# getting error in the tooltip as follows 

    'Type=,Message=Unable to cast object of type 'System.DBNull' to type 'System.String'.,Source=,'. Activity ID:4a2119c7-f4e2-4ba8-a532-e31381001c7b

    Thank you 

    Wednesday, June 13, 2018 7:49 AM
  • For #2, we found there is an issue in the PROD for different ODBC driver. We will try to fix it soon. And in the UI, you don't have to use query. You could edit the table field as following.

    For #1, I think it is not easy to reuse both "GetExistingIDs" and  "Migrate Data from Ms Access to Azure SQL". But you could still reuse the second one as following. Add a new parameter named 'query' in the second pipeline and pass the value as following. And in your copy activity, use @pipeline().parameters.query


    Wednesday, June 13, 2018 9:24 AM