Introduction

In this article, we will discuss different types of variables available in Azure Data Factory (ADF). Variables are used to store values and can be referenced in the pipeline activities. We will learn about these topics:

• System variables
• User-defined variables
• Append variable activity
• Set variable activity

System Variables

ADF provides various system variables which can be used in pipeline activities. These are the system variables available:

• Data factory name - Data factory name where the pipeline is running.
• Pipeline name - Name of the pipeline.
• Pipeline group id - The group id where the pipeline is running.
• Pipeline run ID - This ID is specific to the pipeline run.
• Pipeline trigger ID -  ID of the trigger that invokes the pipeline.
• Pipeline trigger name - Name of the trigger that invokes pipeline.
• Pipeline trigger time - Time when the trigger invokes the pipeline.
• Pipeline trigger type - Type of the trigger that invokes the pipeline (Scheduled/Manual).
• Pipeline triggered by pipeline name - Name of the pipeline that triggers the pipeline from execute pipeline activity.
• Pipeline triggered by pipeline run ID - Run ID of the Pipeline that triggers the pipeline from execute pipeline activity.

I will create a sample pipeline to check the value of the system variables. Before that, let's create a table to store the values from the system variables:

CREATE TABLE [dbo].[Tbl_System_Variable_Demo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Variables] [varchar](50) NULL,
[Value] [varchar](50) NULL
) ON [PRIMARY]
GO

I created the below procedure to use in the stored procedure activity:

CREATE PROCEDURE [dbo].[sp_system_variable_demo] @Variables VARCHAR(50),@Value VARCHAR(50)
AS
INSERT INTO Tbl_Variable_Demo
SELECT @Variables,@Value

Let's create a new pipeline, named PL_VARIABLE with a stored procedure activity. I added system variables in the procedure parameter value :

Now I created a master pipeline and added execute pipeline activity, selected child pipeline PL_VARIABLE in the invoked pipeline option:

Now we will run the pipeline:

The below table shows all the system variables and their sample values:

User-defined variables

There are three types of user-defined variables available in ADF: String, Boolean, and Array. We can see these in the drop down below.

I created a table to store variable datatype and values:

Create Table Tbl_Variable_Demo
(ID INT IDENTITY,
DataType Varchar(10),
Value Varchar(50))

I used the below procedure to insert variable datatype and values in the table:

CREATE PROCEDURE sp_variable_demo @DataType VARCHAR(10),@Value VARCHAR(50)
as
INSERT INTO Tbl_Variable_Demo
SELECT @DataType,@Value

String variables

Now, I created a new pipeline with a stored procedure activity, added a String type variable Var_Str, and assigned the default value Hello:

Next, I edited the Settings of stored procedure activity and added the string variable in the parameter value:

Let's run the pipeline:

The string data type and value were inserted into the SQL table:

Boolean variables

We can use a boolean variable to store either true or false values. In this pipeline I created a boolean variable, Var_Bool, with a default value of true:

Now, I added a Stored procedure activity to the pipeline. In the settings tab, I added boolean variable in the parameter as shown below:

Let's run the pipeline:

The boolean variable and the value were inserted into the table:

Array variables

We can store multiple values of the same type in the array variable. In the below pipeline, I added an array variable, Var_Array, and assigned the default values Red, Green, and Blue:

I added a stored procedure activity. In the setting tab I configured the stored procedure parameter as below:

Let's run the pipeline:

The second array value inserted into the table:

Append variable activity

We can use append variable activity to add a value to an array variable.

I created the below table to store the append variable value:

CREATE TABLE [dbo].[TBL_APPEND_VAR](
[VALUES] [varchar](30) NULL
) ON [PRIMARY]
GO

I used below procedure to insert append variable value to the table:

CREATE procedure [dbo].[sp_insert_append_var_value] @VALUE VARCHAR(30)
AS  
 
INSERT INTO TBL_APPEND_VAR 
SELECT (@VALUE)
GO

I created a new pipeline and an array variable, VAR_ARRAY, and assigned the default values 10 and 20.

Now, I added an append variable activity to the pipeline. In the variables tab, I added the variable VAR_ARRAY and the value 30. Now, the value 30 will be added to the existing items in the array variable:

Now, I added a stored procedure activity to insert the append variable value in a SQL table:

In the stored procedure activity settings tab, I added array variable with second index so that we can access the third variable in the array:

Let's run the pipeline:

The third item in the array variable inserted to the SQL table:

Set variable Activity

We can use set variable activity to assign values to an existing variable in the pipeline.

I created the below table to store set variable activity values:

CREATE TABLE [dbo].[TBL_SET_VAR](
[VALUES] [varchar](30) NULL
) ON [PRIMARY]
GO

I used the below procedure to insert variable values to the table:

CREATE PROCEDURE [dbo].[sp_insert_set_var_value] @VALUE VARCHAR(30)
AS  
 
INSERT INTO TBL_SET_VAR 
SELECT (@VALUE)

I created a new pipeline and declared a String variable, VAR:

I added a set variable activity, in the variables tab assigned the variable, VAR, and provided value Hello:

In the stored procedure activity settings tab, I added the variables in the parameter value:

Let's run the pipeline:

The value from the variable inserted into the SQL table :

Conclusion

In this article, we discussed various types of variables available in the Azure Data Factory. We also learned two activities associated with the variables: the append variable activity and the set variable activity.