Error in Script Activity with Script parameters

[Nejib]

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-act...).

Do you have an idea of the issue in my example ? 

[Shreya_Barhate · Accepted solution]

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 parameters 

Option 2:

If you want to use script parameters, you need to create SP
example:
create stored procedure

CREATE 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

[tayloramy]

Hi @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. 

[v-menakakota]

Hi @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 Team