Buidling SQLqueries in dynamic content ADF

Mathias Ravn Stephansen 0 Reputation points
2023-09-01T08:49:42.4766667+00:00

Im trying to use generic values for my SQL query. I have created parameters for a DB, schema and a table that should be passed into the FROM clause, and i ahve tried two approaches as can be seen in the SQL queries. But as is im getting thsi error:

Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Could not find stored procedure 'SELECT *

FROM [{$database}].[{$schema}].[{$table}]

WHERE CreationDate > '2023-08-01T11:08:34Z''.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Could not find stored procedure 'SELECT *

FROM [{$database}].[{$schema}].[{$table}]

WHERE CreationDate > '2023-08-01T11:08:34Z''.,Source=.Net SqlClient Data Provider,SqlErrorNumber=2812,Class=16,ErrorCode=-2146232060,State=62,Errors=[{Class=16,Number=2812,State=62,Message=Could not find stored procedure 'SELECT *

FROM [{$database}].[{$schema}].[{$table}]

WHERE CreationDate > '2023-08-01T11:08:34Z''.,},],'

"SELECT * 
FROM pipeline().parameters.database.pipeline().parameters.schema.pipeline().parameters.table
WHERE CreationDate > '2023-08-01T11:08:34Z'"

"SELECT * 
FROM [{$database}].[{$schema}].[{$table}]
WHERE CreationDate > '2023-08-01T11:08:34Z'"
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
10,962 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
8,009 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mathias Ravn Stephansen 0 Reputation points
    2023-09-01T09:17:28.5033333+00:00

    Okay dont look at the time for when this was posted :)

    But the solution is here, and i also parameterized the creation date 

    @concat('SELECT * 
FROM "', pipeline().parameters.database, '".', pipeline().parameters.schema, '.', pipeline().parameters.table, '
WHERE CreationDate > ''', pipeline().parameters.date, '''')
    0 comments
  2. ShaikMaheer-MSFT 34,536 Reputation points Microsoft Employee
    2023-09-04T09:06:16.52+00:00

    Hi Mathias Ravn Stephansen,

    Thank you for posting query in Microsoft Q&A Platform. Glad to know your issue resolved and thank you for sharing your implementation details.

    You can also consider using string interpolation syntax as below as well.

    User's image

    Kindly consider marking it as accept answer as you cannot mark your own answer as accepted. Accepted answers help community as well. Thank you.