Retrieving a single Value from a data flow for pipeline use

vikranth-0706 120 Reputation points

I have a problem where I need to use a value calculated inside a Data Flow in my pipeline, but I can't use the usual method of setting a variable because of how the Data Flow is set up. Usually, I'd use a 'Set Variable' activity, but that won't work here because the value is computed inside the Data Flow, and I don't know how to get it out. The Data Flow sends its results to a database, and the pipeline just calls the Data Flow. I need to figure out how to get this one value from the Data Flow back into the pipeline so I can use it.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,711 questions
0 comments No comments
{count} votes

Accepted answer
  1. Smaran Thoomu 10,635 Reputation points Microsoft Vendor

    Hi @vikranth-0706

    Thanks for the question and using MS Q&A platform.

    According to what you need, after the Dataflow (where the variable data is sent to the sink), you can use a lookup on the sink dataset to retrieve that variable's value in the pipeline. You can use sink cache for the second sink.

    • Start by using a conditional split on your input data. This will split your data into two branches, each containing the same information.

    enter image description here

    • Assign one branch to the sink and set up the cache as shown below.

    enter image description here

    • In the original sink, transfer your data to target and the second sink is used for sink cache. Give the sink write order for the dataflow.

    enter image description here

    • In the dataflow activity, set the logging to None and uncheck the First row only.

    enter image description here

    • In the dataflow activity output, you can see the second sink output array.

    enter image description here

    • To access the above output array, use the below dynamic content in a set variable activity after the dataflow activity in the pipeline.

    enter image description here

    Later, you can use this array to get the data for your variable as per your requirement.

    Hope this helps. Do let us know if you any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Amira Bedhiafi 16,071 Reputation points

    Since your Data Flow outputs its results to a database, you could use this to your advantage by modifying it to ensure that the value you need is explicitly stored in a table in your database.

    Then use a Lookup activity in your pipeline to retrieve the value from the database where it was stored. In this way, the activity can query the database and store the result in a pipeline variable.

    If modifying the database schema is not preferred, you can output the needed value to a small file in Azure Blob Storage or Data Lake.

    0 comments No comments