Different Schema name for SQL datasets in ADF using parameters

Question

We have tables under different schema in same db in Azure SQL DB. eg. QA.Mytable and Prod.Mytable . so to manage this in Azure data factory for different environments, is there any way ?

or is it better to create a dedicated DB for each environments ?

I am not able to use global parameters in datasets.

Answer 1

@Agratu : You won't be able to use Global Parameters in a dataset, you can use them in an activity inside an ADF pipeline

Also, I would recommend to separate databases for each environment as a best practice

But still if you need to keep it in the same DB, then you can pass the schema and table name to your dataset dynamically

For that you need to first add parameters in your dataset for Schema name and table name and then use it in the dataset

In the pipeline whenever you will select the dynamic dataset then it will prompt you to add the schema and table name in the pipeline (if you are going to keep different pipeline for different schema)

If you need to use same pipeline for both schemas then you need to create a metadata / config table and use Lookup and Foreach in ADF to load the tables