Variable adf

Question

Hi, there is sql query with variable declared.... Will that work in adf copy activity sql query editor is declare date='2024-04-04' select * from table1 where date1 =@date... If not then what is the option as do not want to change source query... Pls share screen shot

Answer 1

You have 2 options to achieve your desired output :

Option 1: Use ADF Parameters

1. Create a Parameter in ADF:
   • Go to the pipeline where your Copy Activity is located.
   • Click on the "Parameters" tab.
   • Create a new parameter, e.g., dateParam.
2. Pass the Parameter to the Copy Activity:

• In the Copy Activity, go to the "Source" tab.
• Use dynamic content to inject the parameter into your SQL query.

    SELECT * FROM table1 WHERE date1 = '@{pipeline().parameters.dateParam}'
  

When you run the pipeline, you can provide the date value for dateParam.

Option 2: Use Stored Procedure

If you want to keep the SQL logic intact (including the use of variables), you could use a stored procedure:

1. Create a Stored Procedure in Your SQL Database:

      
      CREATE PROCEDURE GetTable1Data 
      
      @dateParam DATE
      
      AS
      
      BEGIN
      
       SELECT * FROM table1 WHERE date1 = @dateParam
      
      END
      
   

2. Call the Stored Procedure from ADF:
   • In ADF, use the "Stored Procedure" activity or pass the parameters in the SQL query as follows:

      
      EXEC GetTable1Data @dateParam = '@{pipeline().parameters.dateParam}'