All I have a working pipeline, whereby the parameter LogicalKey, type string in pipeline as well as data flow, is filled with one value. This is the logical key of my Sink. Everything works fine. See ‘Old settings’ Now I changed the parameter. It contains two columns. My hash in a Derived Column activity still works. But when I use the settings in Sink, as seen in the attached picture, it says: DF-EXP-061 - Column functions are not allowed in constant expressions – My questions: How can I fix the setting in the Sink so that it works with two columns? This is the most important issue. How can I split the values inside the parameter $LogicalKey into two separate values? Meaning if an incoming column of my source equals the first attribute in my parameter, rename that column and give it the values of the source. So if ‘id’ equals, rename ‘id’ to ‘str_id’ and give it the values of ‘id’. Is there a limit to the number of attributes you can put in a parameter? Regards Ron

Hi @Poel van der, RE (Ron) , I am attaching the pipeline and dataflow JSONs. Please copy into the code section to view the pipeline logic. Below is snap of pipeline parameter and dataflow parameter format Pipeline Parameter: Dataflow Parameter 34403-pipelinejson.txt 34384-mappingdataflowjson.txt Hope this helps!

Don't use byName() here, just split your string to make it into an array, or simply pass in an array of strings here. However, your target database table must have the exact name matches for each of the columns that you are assigning as keys here.

How to pass a parameter with more than one key field?

Poel van der, RE (Ron) 421

All
I have a working pipeline, whereby the parameter LogicalKey, type string in pipeline as well as data flow, is filled with one value. This is the logical key of my Sink. Everything works fine. See ‘Old settings’

Now I changed the parameter. It contains two columns.
My hash in a Derived Column activity still works.
But when I use the settings in Sink, as seen in the attached picture, it says:

DF-EXP-061 - Column functions are not allowed in constant expressions –

My questions:

How can I fix the setting in the Sink so that it works with two columns? This is the most important issue.
How can I split the values inside the parameter $LogicalKey into two separate values?
Meaning if an incoming column of my source equals the first attribute in my parameter, rename that column and give it the values of the source. So if ‘id’ equals, rename ‘id’ to ‘str_id’ and give it the values of ‘id’.
Is there a limit to the number of attributes you can put in a parameter?

Regards
Ron

Accepted answer

HarithaMaddi-MSFT 10,126 Reputation points

2020-10-22T17:46:26.383+00:00

Hi @Poel van der, RE (Ron) ,

I am attaching the pipeline and dataflow JSONs. Please copy into the code section to view the pipeline logic. Below is snap of pipeline parameter and dataflow parameter format

Pipeline Parameter:

Dataflow Parameter

34403-pipelinejson.txt
34384-mappingdataflowjson.txt

Hope this helps!
Please sign in to rate this answer.
HarithaMaddi-MSFT 10,126 Reputation points

2020-10-23T07:45:36.793+00:00

Hi @Poel van der, RE (Ron) ,

We have not received a response from you. Please suggest if above details helped you in implementing the requirement. If you are still facing the issue, please let us know and we will be glad to assist.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Poel van der, RE (Ron) 421 Reputation points

2020-10-23T10:19:42.113+00:00

Hai @HarithaMaddi-MSFT

our database is down so I can't test yet
In the pipeline parameter the format of the value is ['id', 'name']

What format do I fill in in Debug Settings when only debugging the data flow?
Also ['id', 'name']?? Or should there be extra quotes?

Regards

HarithaMaddi-MSFT 10,126 Reputation points

2020-10-23T10:53:58.347+00:00

Thanks @Poel van der, RE (Ron) for sharing the update.

While passing array from pipeline to dataflow there is a known limitation and Product team confirmed us to use alternative approach of keeping the values in a string with the shared representation as above. For testing in debug mode in dataflow, please use below format. Let us know for further queries.

[ "id", "name" ]

Poel van der, RE (Ron) 421 Reputation points

2020-10-23T11:53:56.417+00:00

Thnx @HarithaMaddi-MSFT

did you already got an answer on the max number of attributes in a parameter??

Regards
Ron

PS Database still down :(

HarithaMaddi-MSFT 10,126 Reputation points

2020-10-23T12:11:11.917+00:00

@Poel van der, RE (Ron) - Not yet, I am still waiting for response on that from Product team. But I tested with huge numbers in parameter values and it worked fine. However, I will try to get exact details on the same.

Thanks for your patience again!

Poel van der, RE (Ron) 421 Reputation points

2020-10-23T12:16:01.85+00:00

Hi @HarithaMaddi-MSFT

not knowing your first name :) but really appreciating your efforts

Thnx
Ron

HarithaMaddi-MSFT 10,126 Reputation points

2020-10-23T12:26:02.73+00:00

@Poel van der, RE (Ron) - Thanks, glad that I am able to help. You can call me as Haritha :)

Poel van der, RE (Ron) 421 Reputation points

2020-10-26T11:47:06.9+00:00

Hai @HarithaMaddi-MSFT definitely come back on this one, but another flow has more urgency (for which I will ask another question in the forum :) )
Just wanted to let you know

HarithaMaddi-MSFT 10,126 Reputation points

2020-10-27T07:48:53.137+00:00

Thanks @Poel van der, RE (Ron) for sharing the update. We will engage with you on another issue and looking forward for your update on this one.

Poel van der, RE (Ron) 421 Reputation points

2020-10-27T15:52:06.903+00:00

Hai @HarithaMaddi-MSFT

Followed your instructions.
Set my pipeline parameter LogicalKey, type String, value ['CODE','TYPE']
Set my dataflow parameter LogicalKey, type String [], no value
I debugged from the pipeline

Got an error in a Derived Column activity where I want to compute the hash
Column dim1_logicalkey_hash
Expression md5(byNames(split($LogicalKey,',')))

Error: Job failed due to reason: at Derive 'AddHashInput'(Line 19/Col 43): Function 'split' argument 1 should be StringType"

I need the parameter to be of type String [], because in my Sink Settings I set the Key Columns (custom expression) to $Logicalkey (in the hope this works)

Can you help?

Regards
Ron

HarithaMaddi-MSFT 10,126 Reputation points

2020-10-28T15:07:30.283+00:00

@Poel van der, RE (Ron) - Apologies for the delay, I missed seeing the reply. Since parameter is an string array, the elements or column names can be accessed using index instead of split function. Code for generating hash of a column can be as below where Keys is parameter name I have used. Attaching the JSON of the dataflow that is implemented for generating hash of one column. Please suggest if this helps and for further queries.

md5($Keys[0])

35801-dataflowjson.txt

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

HarithaMaddi-MSFT 10,126 Reputation points

2020-10-29T17:35:14.657+00:00

@Poel van der, RE (Ron) - We have not received a response from you. Please let us know if above details helped in implementing the requirement and for any further queries, we will be glad to assist.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

HarithaMaddi-MSFT 10,126 Reputation points

2020-10-30T11:43:51.52+00:00

Hi @Poel van der, RE (Ron) ,

We still have not heard back from you. Following up to check if above details clarified the question, let us know for further queries and we will be glad to assist.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Poel van der, RE (Ron) 421 Reputation points

2020-11-03T08:06:58.833+00:00

Hai @HarithaMaddi-MSFT

did you yet got an answer from the Product team how many attributes can be in a parameter?

regards
Ron

HarithaMaddi-MSFT 10,126 Reputation points

2020-11-03T08:17:42.613+00:00

@Poel van der, RE (Ron) - Apologies for not sharing the update. Product team confirmed that they don't have precise answer for this today and it usually accepts large values. I have raised an GitHub issue to add this information to documentation and assigned it to Product team.

Hope this helps!

Poel van der, RE (Ron) 421 Reputation points

2020-11-03T08:19:33.677+00:00

thnx Haritha

have a nice day

HarithaMaddi-MSFT 10,126 Reputation points

2020-11-11T08:04:08.083+00:00

@Poel van der, RE (Ron) - Thanks for your patience. Product team has responded to the ask on the GitHub issue that is available in this document under "Characters per expression" category.

Please suggest if that helps answering the query.
Sign in to comment

1 additional answer

MarkKromer-MSFT 5,186 Reputation points Microsoft Employee

2020-10-21T21:33:17.987+00:00

Don't use byName() here, just split your string to make it into an array, or simply pass in an array of strings here. However, your target database table must have the exact name matches for each of the columns that you are assigning as keys here.
Please sign in to rate this answer.

1 person found this answer helpful.
Poel van der, RE (Ron) 421 Reputation points

2020-10-22T07:05:51.76+00:00

Thnx for your answer Mark.

How do I split my string into an array?

Is there a limit to the number of attributes you can put in a parameter?

regards
Ron

HarithaMaddi-MSFT 10,126 Reputation points

2020-10-22T08:06:41.197+00:00

Hi @Poel van der, RE (Ron) ,

Welcome to Microsoft Q&A Platform.

Please note that 'Set Variable' activity can be used to convert a string into an array as shown below. There are 50 parameters limited to a data factory, I will reach out to internal team and share with you more details on limit of values in a parameter.

Poel van der, RE (Ron) 421 Reputation points

2020-10-22T10:02:34.777+00:00

Hi @HarithaMaddi-MSFT

Thnx for your answer.
You say: There are 50 parameters limited to a data factory

I don't know if we mean the same. My question was: How many attributes can I put in one parameter?

regards
Ron

HarithaMaddi-MSFT 10,126 Reputation points

2020-10-22T10:42:36.29+00:00

Hi @Poel van der, RE (Ron) ,

Thanks for posting the query. Yes, those details as I mentioned above will check with internal teams and get back to you once I hear from them.

Thanks for your patience!

Poel van der, RE (Ron) 421 Reputation points

2020-10-22T10:50:21.21+00:00

HI @HarithaMaddi-MSFT

sorry, I misunderstood your answer.
Thnx, I will try out the answers somewhere today or tomorrow and let you know

regards
Ron

Poel van der, RE (Ron) 421 Reputation points

2020-10-22T14:28:37.88+00:00

Hai @MarkKromer-8019

I tried to make life easier and split the composite key in my parameters. But still I have a problem in my Sink, see attachment

This problem seems very hard to solve. Already busy with it for days.
Probably also has to do with the fact I am rather new.
Cause when using one composite key as parameter I don't get it working. The hashing won't work, the Sink won't work etc. Tried pipeline parameter as string, data flow parameter as string{} etc etc
So

Hope the attachment clarifies

Regards
Ron

PS would be nice if there is somewhere an example with composite keys, because almost all demo's use one logical key

MarkKromer-MSFT 5,186 Reputation points Microsoft Employee

2020-10-22T15:45:13.573+00:00

When expressing more than 1 column for Key Columns through parameters, select "Custom expression". In your screenshot above you've selected "List of columns" instead.

HarithaMaddi-MSFT 10,126 Reputation points

2020-10-22T15:57:02.457+00:00

Hi @Poel van der, RE (Ron) ,

Yes, please check the suggestion from @MarkKromer-8019 . And, I used array parameter in dataflow and able to get this done as in below GIF. Please suggest if this helps.

Poel van der, RE (Ron) 421 Reputation points

2020-10-22T16:57:16.05+00:00

I know @MarkKromer-8019

but custom expression gives the same error

Poel van der, RE (Ron) 421 Reputation points

2020-10-22T16:58:37.29+00:00

Hi @HarithaMaddi-MSFT

I will give it a try and let you know when I am finished

regards

Poel van der, RE (Ron) 421 Reputation points

2020-10-22T17:12:21.503+00:00

Hi @HarithaMaddi-MSFT

it goes fast. Is there a way to pause a gif? :)

You are only doing something with Alter Row and the Sink?
How does the parameter look like (datatype and value) when debugging from dataflow and debugging from pipeline?

regards
Sign in to comment