data factory dynamic content error

Question

Hello,
Do you know what is wrong here please?

select * from edp.LoadTables where SchemaName = '@{pipeline().parameters.pSchemaName}' and SourceSystem = '@{pipeline().parameters.pSourceSystem}' and (@pipeline().parameters.pEntityName IS NULL OR EntityName = '@{pipeline().parameters.pEntityName}')

error: 'Must declare the scalar variable \"@pipeline\".

It looks like the issue is here
and (@pipeline().parameters.pEntityName IS NULL OR...

My intention is to have the expression as same as sql which works:
declare @EntityName varchar(50) --= 'vw_Department'
select * from edp.LoadTables
where SchemaName = 'eol' and SourceSystem = 'xyz'
and (@EntityName is null OR EntityName = @EntityName)

Thank you

Answer 1

Hello @arkiboys ,

I think you can achieve the same result by using a UNION ALL clause . I am sharing the dynamic expression below and I think it should help .

@markus.bohland@hotmail.de ('select * from edp.LoadTables where SchemaName =''',pipeline().parameters.pSchemaName,'''',
' and SourceSystem = ','''',pipeline().parameters.pSourceSystem,'''',' AND ', pipeline().parameters.pEntityName,' is null' , ' UNION ALL ', 'select * from edp.LoadTables where SchemaName =''',pipeline().parameters.pSchemaName,'''',
' and SourceSystem = ','''',pipeline().parameters.pSourceSystem,'''',' AND ENTITYName =', '''',pipeline().parameters.pEntityName,''''
)

Let me know how it goes .

Thanks
Himanshu

Answer 2

There is a similar thread :

you can use a combination of equals and coalesce to handle that scenario something like below:

@equals(coalesce(pipeline().parameters.Test,'0'),'0')

for this scenario : @{pipeline().parameters.pEntityName} is null