Forum Discussion
Error in Script Activity with Script parameters
Hi,
I want to use parameters in a script activity. However I get an error trying to do that.
But according to the docuentation, this approach is supposed to work fine (https://learn.microsoft.com/en-us/azure/postgresql/configure-maintain/how-to-data-factory-script-activity-fabric?tabs=query).
Do you have an idea of the issue in my example ?
Hi Nejib,
You’re running into this because Script activity parameters can replace only values in a SQL/PGSQL statement, not identifiers (like schema, table, or column names). In other words:Works: using a parameter in predicates or values (e.g., WHERE status = @status).
Fails: trying to use a parameter where SQL expects an identifier (e.g., SELECT col FROM ... or FROM schema.@table). In those cases, the engine won’t substitute an identifier.If your screenshots show param inside the SELECT list or the FROM clause, that’s why it errors.
Option1:
If you want to use parameters in sql statement like select,drop,etc
use pipeline parametersOption 2:
If you want to use script parameters, you need to create SP
example:
create stored procedureCREATE OR ALTER PROCEDURE dbo.usp_select_from_any
schema_name sysname, @table_name sysname
AS
BEGIN
DECLARE sql nvarchar(max) =
N'SELECT * FROM ' + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name) + ';';
EXEC (@sql);
END;call it in script like:
EXEC dbo.usp_select_from_any
schema_name = schema_name,
@table_name = @table_name;
Thanks and Regards,
Shreya
3 Replies
Hi Nejib,
You’re running into this because Script activity parameters can replace only values in a SQL/PGSQL statement, not identifiers (like schema, table, or column names). In other words:Works: using a parameter in predicates or values (e.g., WHERE status = @status).
Fails: trying to use a parameter where SQL expects an identifier (e.g., SELECT col FROM ... or FROM schema.@table). In those cases, the engine won’t substitute an identifier.If your screenshots show param inside the SELECT list or the FROM clause, that’s why it errors.
Option1:
If you want to use parameters in sql statement like select,drop,etc
use pipeline parametersOption 2:
If you want to use script parameters, you need to create SP
example:
create stored procedureCREATE OR ALTER PROCEDURE dbo.usp_select_from_any
schema_name sysname, @table_name sysname
AS
BEGIN
DECLARE sql nvarchar(max) =
N'SELECT * FROM ' + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name) + ';';
EXEC (@sql);
END;call it in script like:
EXEC dbo.usp_select_from_any
schema_name = schema_name,
@table_name = @table_name;
Thanks and Regards,
ShreyaHi Nejib ,
Thanks for reaching out to the Microsoft fabric community forum.
In Script activities, parameters can be used only for passing values to a query. They cannot be used for SQL object names such as schema or table names. Because of this, using parameters directly in DDL statements like creating or dropping tables results in a syntax error.
Please go through the below solved issue, which explains how to construct the SQL statement dynamically using the expression builder, so that the schema and table names are resolved before the script runs.
Solved: Use Script parameters in Script activitie in Data ... - Microsoft Fabric Community
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Community Support TeamHi Nejib,
You can use dynamic expressions instead, open the expression builder and build out your query in there.
The post that v-menakakota linked has an example of this as the accepted solution.
