Datasets in adf

Question

Hi how can we use Same data sets and three tables like table1, table2, table3 in adf I tried can I define table name in parameter section... Will it overwrite 2nd time

Answer 1

You can manage datasets and tables dynamically using parameters. This allows you to reuse the same dataset with different tables without having to create multiple datasets for each table. Here’s how you can achieve this:

1. Define Dataset with Parameters

• Create a dataset in ADF that connects to your data source (Azure SQL Database, Azure Data Lake...).
• Define a parameter in the dataset for the table name. This parameter can be used to dynamically pass the table name during the pipeline execution.

  
     {
  
       "name": "AzureSqlDataset",
  
       "properties": {
  
         "type": "AzureSqlTable",
  
         "typeProperties": {
  
           "tableName": "@{dataset().TableName}"
  
         },
  
         "parameters": {
  
           "TableName": {
  
             "type": "String"
  
           }
  
         }
  
       }
  
     }
  
  

2. Passing Parameters in a Pipeline

• In your pipeline, create a parameter that will hold the table name.
  • When you use this dataset in an activity (like Copy Activity), you can pass the table name to the dataset parameter.

  
     {
  
       "name": "CopyActivity",
  
       "type": "Copy",
  
       "inputs": [
  
         {
  
           "referenceName": "AzureSqlDataset",
  
           "type": "DatasetReference",
  
           "parameters": {
  
             "TableName": "Table1"
  
           }
  
         }
  
       ],
  
       "outputs": [
  
         {
  
           "referenceName": "AzureSqlDataset",
  
           "type": "DatasetReference",
  
           "parameters": {
  
             "TableName": "Table2"
  
           }
  
         }
  
       ]
  
     }
  
  

3. Reuse the Dataset with Different Tables

• In your pipeline, you can reuse the same dataset but pass different table names each time it is used.
  • For example, you can have multiple Copy Activities that use the same dataset but with different table names like Table1, Table2, and Table3.

4. Will it Overwrite the Table?

• Whether the table gets overwritten depends on how your activities are configured.
  • If you're using a Copy Activity, you can specify the behavior (e.g., truncate the table before copying, append to the table, etc.) in the activity's settings.
  • By default, using the same dataset with a different table name will not cause one table to overwrite another unless explicitly configured to do so.

5. Example of Dynamic Table Name Usage in a Pipeline

• You can define three different activities in your pipeline, each passing a different table name to the dataset:

  
     {
  
       "activities": [
  
         {
  
           "name": "CopyFromTable1",
  
           "type": "Copy",
  
           "inputs": [
  
             {
  
               "referenceName": "AzureSqlDataset",
  
               "type": "DatasetReference",
  
               "parameters": {
  
                 "TableName": "Table1"
  
               }
  
             }
  
           ]
  
         },
  
         {
  
           "name": "CopyFromTable2",
  
           "type": "Copy",
  
           "inputs": [
  
             {
  
               "referenceName": "AzureSqlDataset",
  
               "type": "DatasetReference",
  
               "parameters": {
  
                 "TableName": "Table2"
  
               }
  
             }
  
           ]
  
         },
  
         {
  
           "name": "CopyFromTable3",
  
           "type": "Copy",
  
           "inputs": [
  
             {
  
               "referenceName": "AzureSqlDataset",
  
               "type": "DatasetReference",
  
               "parameters": {
  
                 "TableName": "Table3"
  
               }
  
             }
  
           ]
  
         }
  
       ]
  
     }