Pipeline in dev and qa

Question

Hi I am following the below steps to run pipeline can create variables and use expressions to set their values based on the environment parameter ("dev" or "qa") using the following approach:

1. Create an Environment Parameter:
   • Go to the pipeline in Azure Data Factory.
     • In the Parameters section, add a new parameter called environment. Set the possible values to "dev" and "qa".
     1. Create Variables for Server and Database:
        • In the Variables section, create variables such as ServerName and DatabaseName.
        1. Assign Values Based on the Environment Parameter:
           • In the pipeline's Set Variable activity, use expressions to set the values of ServerName and DatabaseName based on the environment parameter.
             • For example, for ServerName:azureCopy

     @if(equals(pipeline().parameters.environment, 'dev'), 'dev-server-name', 'qa-server-name')

           ```
     
           Similarly, for `DatabaseName`:azureCopy
     
           ```sql
     

     @if(equals(pipeline().parameters.environment, 'dev'), 'dev-database-name', 'qa-database-name')

           ```
     

2. Use Variables in the SQL Dataset:

• In your SQL dataset, parameterize the server name and database name.
  • In the Linked Service for the SQL dataset, bind the parameters to the variables you created earlier. You can pass the variable value using an expression like:azureCopy

              
         @variables('ServerName')
         
    

    andazureCopy

                    @variables('Database name')                 how to add  2 database in dev database for bronze to silver and 2 for qa and how to run it... Screenshot pls
    

Answer 1

Hi Vineet S ,

Thankyou for posting your query on Microsoft Q&A platform .

I understand that you are trying to parameterize your pipeline dynamically so that it can be reused in both Dev and QA environment and can fetch data from 2 DBs for each of the environments.

I would suggest you to segregate Dev and QA environment as it is not the best practice recommended. However, if it's not an end to end project you are working with, then you can carry on with your approach.

Now coming to your query, "how to add 2 database in dev database for bronze to silver and 2 for qa and how to run it" , by this query it seems you have set of 2 DBs in each env for which you have to perform the action.

Instead of passing the values for ServerName and DBName in the pipeline level , you could store the metadata in a control table in SQL having columns ServerName, DBName, env

ServerName	DBName	UserName	Pwd Env
dev-server-name	dev-database1	username1	pwd1 dev
dev-server-name	dev-database2	username1	pwd1 dev
qa-server-name	qa-database1	username2	pwd2 qa
qa-server-name	qa-database2	username2	pwd2 qa

Fetch the value from this table using lookup activity and loop through it using Foreach activity and use the expressions @item().ServerName and @item().DBName in the activities to refer each rows.

Parameterize the linked service like this:

Create Dataset level parameters as well and configure like below, make sure if you need to parameterize tablename as well , you can create metadata for tablename too and create additional parameters :

In case you are using copy activity inside foreach, you can pass the values for the parameters from the lookup output :

Hope it helps. Kindly accept the answer if it helped. Thankyou