Retrieving a single Value from a data flow for pipeline use

Question

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.

Answer 1

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.

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

• 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.

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

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

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

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.

Answer 2

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.