passing double quotes in parameter to data flow

Question

passing double quotes in parameter to data flow

Engelhardt, Carl 21

I am working on a data flow to clean invalid characters from incoming data. I am using a derived column , and adding a column pattern, for each column that matches type=='string', with a column name expression of $$, all of which is fine. For the value expression, I need something like this - replace(replace($$,"Á","a"),"Â","a"))
If I hard code this, it works great replacing all instances in all string fields with a lower case a. But we have a table of 60+ characters in a sql table that we don't allow , and I have a stored proc that generates a multiple replace statement like the one above. But when I pass it as a parameter from the data factory it adds escape characters before each double quote. I get this - replace(replace($$,/"Á/",/"a/"),/"Â/",/"a/"))
which doesn't evaluate. Does anyone know of a way to NOT escape double quotes?

Kiran-MSFT 691 Reputation points Microsoft Employee

2021-02-15T02:25:28.803+00:00

Use double escape should keep them. \.

This is a very common practice used in dataflow.
Engelhardt, Carl 21 Reputation points

2021-02-15T17:33:18.203+00:00

Unfortunately that didn't work. It is when I am passing the parameter from ADF to the data flow when it adds the escape characters. According to the answer below, this might not be possible.
Kiran-MSFT 691 Reputation points Microsoft Employee

2021-02-15T18:36:43.993+00:00

Can you attach the dataflow script(click on scroll image next to code) and the definition of your pipeline?
Engelhardt, Carl 21 Reputation points

2021-02-16T18:59:25.847+00:00

Sure. I am changing lowercase a-c to uppercase A-C, so I can easliy verify the results.

Here is what the hardcoded Value Expression that DOES work : replace(replace(replace($$,"a","A"),"b","B"),"c","C")

And here is the parametrized Value Expression that does NOT work: Value Expression = $InvalidCharReplacement

68761-hardcodeexpressionworks.txt
Engelhardt, Carl 21 Reputation points

2021-02-16T19:00:17.157+00:00

And because I couldn't attach 2 files, here is the 68742-parameterexpressiondoesnotwork.txtcode that doesn't work
Engelhardt, Carl 21 Reputation points

2021-02-16T19:01:20.93+00:00

And the $InvalidCharReplacement contains the same value as the hard coded value.
Santhosh Kumar Manohar 6 Reputation points

2021-07-29T14:45:43.907+00:00

Use single quote instead of double quote.
Ryan Sullivan 1 Reputation point

2021-11-05T23:38:44.487+00:00

^this one did it for me

Kiran-MSFT 691 Reputation points Microsoft Employee

2021-02-15T02:25:28.803+00:00

Use double escape should keep them. \.

This is a very common practice used in dataflow.
Engelhardt, Carl 21 Reputation points

2021-02-15T17:33:18.203+00:00

Unfortunately that didn't work. It is when I am passing the parameter from ADF to the data flow when it adds the escape characters. According to the answer below, this might not be possible.
Kiran-MSFT 691 Reputation points Microsoft Employee

2021-02-15T18:36:43.993+00:00

Can you attach the dataflow script(click on scroll image next to code) and the definition of your pipeline?
Engelhardt, Carl 21 Reputation points

2021-02-16T18:59:25.847+00:00

Sure. I am changing lowercase a-c to uppercase A-C, so I can easliy verify the results.

Here is what the hardcoded Value Expression that DOES work : replace(replace(replace($$,"a","A"),"b","B"),"c","C")

And here is the parametrized Value Expression that does NOT work: Value Expression = $InvalidCharReplacement

68761-hardcodeexpressionworks.txt
Engelhardt, Carl 21 Reputation points

2021-02-16T19:00:17.157+00:00

And because I couldn't attach 2 files, here is the 68742-parameterexpressiondoesnotwork.txtcode that doesn't work
Engelhardt, Carl 21 Reputation points

2021-02-16T19:01:20.93+00:00

And the $InvalidCharReplacement contains the same value as the hard coded value.
Santhosh Kumar Manohar 6 Reputation points

2021-07-29T14:45:43.907+00:00

Use single quote instead of double quote.
Ryan Sullivan 1 Reputation point

2021-11-05T23:38:44.487+00:00

^this one did it for me

Answer 1

Nasreen Akter 10,706

Hi @Engelhardt, Carl ,

According to your description what I understand is that you were trying to pass expression as a STRING from pipeline to a DataFlow and was trying to execute as an EXPRESSION. In my knowledge, there is no STRING to EXPRESSION conversion functionality available in DataFlow yet. :(

----------

If the response is helpful, please Accept as Answer and Upvote it. Thanks!

Mark Kromer MSFT 1,146 Reputation points

2021-02-15T22:27:49.01+00:00

ADF does indeed support sending in expressions as parameters to data flows from pipelines.

Example: replace(replace('mÁrkstring',"Á","a"),"Á","a") in pipeline param for a data flow activity

Derived Column consuming that parameter in a data flow as an expression:
Nasreen Akter 10,706 Reputation points

2021-02-15T22:42:59.95+00:00

Hi @Mark Kromer MSFT , in the pipeline, there is a "Expression" checkbox available, which evaluates the expression before sending the data to the dataflow. It will not work for this described issue.
Mark Kromer MSFT 1,146 Reputation points

2021-02-15T23:15:28.487+00:00

That checkbox is only for pipeline expressions. This question is regarding sending in a data flow expression as a parameter. Pipeline expressions require that checkbox to be set to enact string interpolation. It is not needed here.
Nasreen Akter 10,706 Reputation points

2021-02-15T23:57:21.177+00:00

Hi @Mark Kromer MSFT , thank you for your reply. That would be wonderful, if we can do that. Would you please check with the above sample replace(replace($$,"Á","a"),"Â","a")) and let us know how you did it. Much appreciated. :)
Mark Kromer MSFT 1,146 Reputation points

2021-02-16T00:28:09.26+00:00

That particular pattern is using the "this" or "current value" metafunction $$, so it won't work as-is. If you replace that with a static column reference, the parameter will work fine.
Nasreen Akter 10,706 Reputation points

2021-02-16T00:47:10.48+00:00

@Mark Kromer MSFT , exactly. That is what I was talking about. You cant pass a string as a parameter to a dataflow and execute similar like eval( ) function. :)
Mark Kromer MSFT 1,146 Reputation points

2021-02-16T00:56:30.127+00:00

Not exactly. You can absolutely pass expressions as strings to a data flow from a pipeline parameter. Using $$ syntax is what is not currently supported.
Nasreen Akter 10,706 Reputation points

2021-02-16T13:14:33.217+00:00

Hi @Mark Kromer MSFT , I have checked with replace(ColumnName, "O","X") as a String TYPE Pipeline parameter with Expression checkbox on. And it's working. I thought, with expression checkbox on, the expression will be evaluated prior sending the data to the dataflow. But I was wrong. Thank you for your reply. I appreciate it.
Engelhardt, Carl 21 Reputation points

2021-02-16T19:19:08.223+00:00

Mark is correct, the pipeline parameter like that won't work. I am using a derived column task in the data flow, and creating a column pattern, where I say Each Column that matches : type =='string'. Then the column name expression is $$, which stands for the generic column name, and the replacement string is : replace($$,"Á","a"),"Á","a")
with that nice little statement I can replace every value in every column dynamically. Apparently Mark is correct, I have tried multiple ways of doing this and they all fail. And if I try to pass the parameter as an expression, I get this fun error, which does not seem to exist anywhere on the internet: "Meta variables are only supported in meta functions"
Engelhardt, Carl 21 Reputation points

2021-02-16T19:20:03.01+00:00

oops, sorry, Nasreen is correct.
Engelhardt, Carl 21 Reputation points

2021-02-16T19:22:33.037+00:00

I've got to have the $$ to work to get what I want, dynamic replacement.
HimanshuSinha-msft 19,246 Reputation points Microsoft Employee

2021-02-25T20:21:05.317+00:00

Hello @Engelhardt, Carl ,
As it turns out at this time the product feature you are looking for is not supported. May I request you to please log the same here .
Product group does monitor the request and they can plan for the implementation in future .Once you log the feature request you will also be notified on the status of the request . Please do share the url of the logged issue here so that other community members are aware of the same .We expect you to keep using this forum and motivate others to do the same . You can always help other community members by answering to their queries .

Thanks
Himanshu
Engelhardt, Carl 21 Reputation points

2021-03-01T16:23:04.21+00:00

Thank you for your reply. I just posted my idea. I have a completely dynamic data factory pipeline bringing in multiple on premise sql tables into Azure, and another for csv files. This is the only place where I had to hard code anything.
Kiran-MSFT 691 Reputation points Microsoft Employee

2021-03-01T17:37:13.34+00:00

There is a new feature added to the product to support passing dataflow expression via pipeline parameters. Please stay tuned to product updates.

passing double quotes in parameter to data flow

0 additional answers

Activity