Azure Data Factory Script activity input parameter issue

Question

Azure Data Factory Script activity input parameter issue

Partha Das 286

Hi,

I'm facing Azure Data Factory Script activity input parameter issue. - design - ADF_ScriptActivity.jpg

When I triggered this activity, I got input as

===============================

{
"scripts": [
{
"parameters": [
{
"name": "tableName",
"type": "String",
"value": "Testtable",
"direction": "Input"
},
{
"name": "result",
"type": "String",
"value": null,
"direction": "Output",
"size": "300"
}
],
"type": "Query",
"text": "set @result = (SELECT * FROM @tableName FOR XML AUTO, ELEMENTS);"
}
]
}

===============================

But it failed with below error - ADF_Error.jpg

Early response is highly appreciated.

Regards,
Partha

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Nandan Hegde 36,716 MVP Volunteer Moderator

Hey
It should be @{Pipeline().parameters.tableName} in your query

Partha Das 286 Reputation points

2022-08-12T08:16:08.01+00:00

Hi Nandan,

Thanks for quick response but when I modified the script as
set @result = (SELECT * FROM @{Pipeline().parameters.tableName} FOR XML AUTO, ELEMENTS);

I was unable to publish the pipeline with below error

I believe since tableName is a script parameter and not a pipeline parameter, we can't access it that way.

Please suggest.
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2022-08-12T08:56:43.387+00:00

Exactly.

Script parameters need to be accessed using @. But table name can't be derived from script parameters as they are meant to be used as filters.
Partha Das 286 Reputation points

2022-08-12T09:01:18.217+00:00

Then how can I parameterize the table name in query of a Script Activity so that I can use that within FOREACH activity.
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2022-08-12T09:08:50.55+00:00

If your foreach loop is not a parallel one you can use a set variable activity to frame the query or store table name and use it in the script activity. Like below where the table name comes from a variable

@markus.bohland@hotmail.de ('select * from ' , variables('tablenamevariable'), ' where addressed=10')
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2022-08-12T09:21:39.713+00:00

Thank you @Subashri Vasudevan for the explanation.

@Partha Das :Sorry for the confusion. It seems my entire answer was not published .

As stated , @tablename is also a syntax for SQL for table variable thereby creating that error.
SO my suggestion was to create a pipeline parameter and leverage it.

But your use case is for loop, so you can directly call @{item()} as the tablename for every iteration in your code if need be assuming your iteration is on the basis of tablenames only
Partha Das 286 Reputation points

2022-08-12T10:24:27.243+00:00

Hi @Nandan Hegde ,
When I tried to directly use item() instead of tableName parameter, its input was showing

======================================

{
"scripts": [
{
"parameters": [
{
"name": "tableName",
"type": "String",
"value": {
"table_name": "Testtable"
},
"direction": "Input"
}
],
"type": "Query",
"text": "DECLARE @tempTable TABLE\n (\n ResultXML VARCHAR(max)\n )\n\nDECLARE @RS NVARCHAR(max);\nset @RS = (SELECT * FROM item() FOR XML AUTO, ELEMENTS);\n\n\nINSERT INTO @tempTable values (@RS )\nselect * from @tempTable"
}
]
}

======================================

But now getting the error below
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2022-08-12T10:36:48.123+00:00

Hi,

Could you please post your pipeline design and the activity output that is used by foreach?

Thanks
Partha Das 286 Reputation points

2022-08-12T10:49:40.767+00:00

Hi @Subashri Vasudevan ,

The pipeline is attached.230753-pipeline.pdf
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2022-08-12T11:42:36.697+00:00

Hey,
use @{item()}
Partha Das 286 Reputation points

2022-08-12T12:23:40.353+00:00

Hi @Nandan Hegde and @Subashri Vasudevan ,

Both your answers worked for me.

Many thanks
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2022-08-12T12:29:15.43+00:00

Glad we could be of help :)
And thank you @Subashri Vasudevan

@Partha Das Can you please accept the above solution as answer as it might help other community members as well.

Thank you for using MSFT Q&A
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2022-08-12T12:29:38.237+00:00

Could you please elaborate a bit on how you made it work. Could be helpful for the community. Thanks in advance
Partha Das 286 Reputation points

2022-08-12T12:38:09.44+00:00

Ether way it is working in FOREACH use case -

@markus.bohland@hotmail.de ('set @resultXML = (SELECT * FROM ', item().table_name, ' FOR XML AUTO, ELEMENTS);')

set @resultXML = (SELECT * FROM @{item().table_name} FOR XML AUTO, ELEMENTS);

Note: resultXML is output string parameter

Share via

Azure Data Factory Script activity input parameter issue

Note: resultXML is output string parameter

0 additional answers

Your answer