I'm trying to use Azure Data Factory to copy a MySQL view with parameters to an Azure SQL Database. Here is the query to copy the activity:
SET @dDebut=20220201; SET @dFin=20220228; select vue_movements_of_month_with_param.* from (select @dDebut, @dFin) param , vue_movements_of_month_with_param LIMIT 10;
This is what I am running in HeidiSQL. This view contains functions that take the above parameters. In ADF, I defined the parameters dDebut and dFin as parameter pipes:
I then used the replication activity in ADF with a MySQL linked service:
By using the following query:
@concat('SELECT vue_movements_of_month_with_param.* FROM (SELECT ', pipeline().parameters.dDebut, ', ', pipeline().parameters.dFin, ') param, vue_movements_of_month_with_param LIMIT 10')
But the result returns an empty table. It seems my parameters are not recognized. How can I solve this problem?
I finally found the solution. Instead of creating the view with parameters stored as user-defined variables, I'm using a stored procedure to get the parameters needed for input.