ADF: Export Parquet Files with Column names with spaces and where clause

Question

I have a lookup that retrieves a few records from a SQL Server table containing server, database, schema, table name and a where clause with values. These values are passed to a copy data (within a ForEach) In the forEach flow I've also insterted a lookup in order to remove spaces and any special characters in the column names, in order to save to parquet file. My issue is that I can't get the "where" clause to work.

VALUES IN TABLE:

SRC_SERVERNAME	SRC_DATABASE	SRC_SCHEMANAME	SRC_TABLENAME	SRC_WHERE_DATE_CLAUSE
SQ01	NAV	dbo	Company$Sales Invoice Header	[Posting Date] >= '2021-01-01'

SOURCE SETUP:

ERROR MSG:

A database operation failed with the following error: 'Incorrect syntax near '2021'. Must declare the scalar variable "@wherename".' Incorrect syntax near '2021'. Must declare the scalar variable "@wherename"., SqlErrorNumber=102,Class=15,State=1,

QUERY

@concat(' 

DECLARE @s VARCHAR(MAX) 
DECLARE @servername VARCHAR(500) = ''', item().SRC_SERVERNAME ,''' 
DECLARE @databasename VARCHAR(500) = ''', item().SRC_DATABASENAME ,''' 
DECLARE @schemaname VARCHAR(500) = ''', item().SRC_SCHEMANAME ,''' 
DECLARE @tablename VARCHAR(500) = ''', item().SRC_TABLENAME ,''' 
DECLARE @wherename VARCHAR(500) = ''', item().SRC_WHERE_DATE_CLAUSE ,''' 

SELECT @s = ISNULL(@s + '', '','''') + ''['' + c.name + '']'' + '' as [''+replace(replace(replace(replace(replace(replace(replace(c.name,'' '',''''),''ö'',''o''), ''ä'',''a''),''å'',''a''),''('',''_''),'')'',''''),''-'',''_'') +''] '' 
FROM sys.all_columns c join sys.tables t ON c.object_id = t.object_id 
WHERE t.name =  @tablename 

 
SELECT ''select '' + @s + '' from '' + ''['' + @servername + '']'' + ''.'' + ''['' + @databasename + '']'' + ''.'' + ''['' + @schemaname + '']'' + ''.'' + ''['' + @tablename + '']'' + '' where '' + @wherename  as Query') 

Answer 1

I've solved it, ADF handles characters differently.

Had to update the where clause and add extra characters.

So instead of:

Input

INSERT INTO [dbo].[ADF_DL_ONPREMSQL_CONFIGURATION] ([SRC_SERVERNAME], [SRC_DATABASENAME], [SRC_SCHEMANAME], [SRC_TABLENAME], [SRC_WHERE_DATE_CLAUSE])
VALUES   ('SQ01', 'Company', 'dbo', 'Company$Sales Invoice Header', 'where [Posting Date] >= ''2021-01-01''')

Output

where [Posting Date] >= '2021-01-01'

I updated with:

INSERT INTO [dbo].[ADF_DL_ONPREMSQL_CONFIGURATION] ([SRC_SERVERNAME], [SRC_DATABASENAME], [SRC_SCHEMANAME], [SRC_TABLENAME], [SRC_WHERE_DATE_CLAUSE])
VALUES   ('SQ01', 'Company', 'dbo', 'Company$Sales Invoice Header', 'where [Posting Date] >= ''''2021-01-01''''')

Output

where [Posting Date] >= ''2021-01-01''

Final Query

@concat(' 

DECLARE @s VARCHAR(MAX) 
DECLARE @servername VARCHAR(500) = ''', item().SRC_SERVERNAME ,''' 
DECLARE @databasename VARCHAR(500) = ''', item().SRC_DATABASENAME ,''' 
DECLARE @schemaname VARCHAR(500) = ''', item().SRC_SCHEMANAME ,''' 
DECLARE @tablename VARCHAR(500) = ''', item().SRC_TABLENAME ,''' 
DECLARE @wherename VARCHAR(500) = ''', string(item().SRC_WHERE_DATE_CLAUSE) ,''' 

SELECT @s = ISNULL(@s + '', '','''') + ''['' + c.name + '']'' + '' as [''+replace(replace(replace(replace(replace(replace(replace(c.name,'' '',''''),''ö'',''o''), ''ä'',''a''),''å'',''a''),''('',''_''),'')'',''''),''-'',''_'') +''] '' 

FROM sys.all_columns c join sys.tables t 

ON c.object_id = t.object_id 

WHERE t.name =  @tablename 

 

SELECT ''select '' + @s + '' from '' + ''['' + @servername + '']'' + ''.'' + ''['' + @databasename + '']'' + ''.'' + ''['' + @schemaname + '']'' + ''.'' + ''['' + @tablename + '']'' + '' '' + @wherename  as Query')