Hi @HimanshuSinha-msft @KranthiPakala-MSFT @ShaikMaheer-MSFT I have a requirement where i need to insert the row incrementally if the parameter value is greater than my one of the date column value. I'm trying to do it in alter row transformation in data flow activity. Bit stuck to fetch the watermark table value from the one of my database table. Attaching the screenshot of my both flow and pipeline activity. Please help me!

Hi @Venkatesh Srinivasan , What I understand from the description is you were trying to pass a value from a LookUpActivity to the DataFlow as a parameter. In the lookup, if the First row only checkbox is ON , then run the pipeline in a Debug Mode and go to the Pipeline --> Output tab and see the output you are getting from the LookUpActivity. @activity('Lookup1').output.firstRow.SKU_ADJUSTMENT_DATETIME If the First row only checkbox is OFF in the LookUpActivity, then you have to do something like @activity('Lookup1').output.value[0].SKU_ADJUSTMENT_DATETIME you can also see https://learn.microsoft.com/en-gb/azure/data-factory/control-flow-lookup-activity Thanks!

Need to pass watermark table value as a parameter in data flow(parameter)

Accepted answer

Nasreen Akter 10,791 Reputation points

2021-11-18T20:28:18.957+00:00
Hi @Venkatesh Srinivasan ,

What I understand from the description is you were trying to pass a value from a LookUpActivity to the DataFlow as a parameter.

In the lookup, if the First row only checkbox is ON, then run the pipeline in a Debug Mode and go to the Pipeline --> Output tab and see the output you are getting from the LookUpActivity.

@activity('Lookup1').output.firstRow.SKU_ADJUSTMENT_DATETIME

If the First row only checkbox is OFF in the LookUpActivity, then you have to do something like @activity('Lookup1').output.value[0].SKU_ADJUSTMENT_DATETIME

you can also see https://learn.microsoft.com/en-gb/azure/data-factory/control-flow-lookup-activity

Thanks!
Please sign in to rate this answer.

2 people found this answer helpful.
Venkatesh Srinivasan 86 Reputation points

2021-11-18T21:34:35.247+00:00

Hi @Nasreen Akter

Thanks for your response.

I'm trying to get the cutoffdate parameter value inside the dataflow, where I can use that value to validate whether my current pipeline run date column value is greater than my lookup table value(Basically a incremental load).

I tried your suggestions , I got a error that cannot parse from my flow. In my last snap you can see where I'm using the parameter to valid my current column value in data flow alter activity.

Thanks in advance!

Venkatesh Srinivasan 86 Reputation points

2021-11-18T21:54:27.847+00:00

Hi @Nasreen Akter

Attaching the snap where i need to get the value below

Nasreen Akter 10,791 Reputation points

2021-11-19T14:07:12.173+00:00

Hi @Venkatesh Srinivasan ,

Would you please make the DataFlow Parameter --> CutOffDate as string instead of timestamp and convert that String to Timestamp in your AlterActivity e.g.,

FileNameDate > toTimestamp($CutOffDate, 'yyyy-MM-dd\'T\'HH:mm:ss')

Now, if you want to DEBUG the DataFlow, please set the CutOffDate Default Value.
First, DEBUG the pipeline, go to the Output --> DataFlow Input, you will get the CutOffDate value which is passed from the LookUpActivity to the DataFlow.
Then, put that value as a DEFAULT value in the DataFlow --> Parameter --> CutOffDate. Now, when you will DEBUG the DataFlow, you will get the Default Value for the CutOffDate Date you just set and you will able to continue with your implementation and fix the issues you are having with the DataFlow.

Hope this helps! Thanks!

Venkatesh Srinivasan 86 Reputation points

2021-11-25T16:40:55.747+00:00

Hi @Nasreen Akter

Can you please help me here, Need small tweak in your expression.

FileNameDate > toTimestamp($CutOffDate, 'yyyy-MM-dd\'T\'HH:mm:ss') - This is failing in my condition

Attaching the detail snap for your reference

Venkatesh Srinivasan 86 Reputation points

2021-11-25T16:48:27.947+00:00

Additional note : Attaching my parameter value in a column so that you can will clear view about the format

Basically my expectation is to allow rows only if filename date > existing max date column

Please need your help here! Thanks in advance!!!!

Nasreen Akter 10,791 Reputation points

2021-11-25T17:40:48.937+00:00

Hi @Venkatesh Srinivasan , you are missing T in the middle of the Timestamp e.g, 2021-11-18T18:04:00 instead of 2021-11-18 18:04:00. In the screenshot with column ExistingMaxDate has T. If you pass the ExistingMaxDate column in the expression e.g., FileNameDate > toTimestamp(ExistingMaxDate, 'yyyy-MM-dd\'T\'HH:mm:ss') what are you getting? Thanks!

Venkatesh Srinivasan 86 Reputation points

2021-11-26T11:25:11.15+00:00

Thanks for your help!! @Nasreen Akter I got the desired output now.

Nasreen Akter 10,791 Reputation points

2021-11-26T13:45:33.237+00:00

@Venkatesh Srinivasan , awesome! Thank you. :)
Sign in to comment