Copying Variable / Parameter into Sink (Azure Blob Storage, Azure Database etc.) via Data Factory / Synapse Pipeline

Problem Statement :

Is it possible to Copy a Variable / Parameter via Azure Data Factory (ADF) / Synapse into a Sink (Azure Blob Storage, Azure SQL Database etc.) as there is no out of the box connector for Variable / Parameter as Source.

Use Cases :

a) Preserve Watermark like Last Processed date

b) Save Pipeline Activity logs as Part of Logging framework

Example :

Prerequisites :

1. Azure Data Factory / Synapse
2. Azure Blob Storage ( For our case, we would be Copying the Variable into a File within Azure Blob Storage )

Solution :

GitHUB Code

1. To Copy a Variable / Parameter into a file, we would be leveraging Copy Activity and Copy Activity expects a Source Dataset to be present. For that purpose, we need to create a Dummy Source dataset in the form of a Dummy file uploaded in Azure Blob Storage (or your necessary sink) with one row.

2. Source Dataset Configuration mapping to Dummy file :

3. In Source tab of Copy activity,  Add New column by clicking +New in additional columns

And Provide the necessary name for the column at Source and value mapping to the Variable / Parameter that we intend to Copy into Sink.

4. Create a Sink Dataset mapping to the Output Azure Blob Storage Container with necessary path and filename details.

5. In Mapping tab of Copy activity, Click on Import Schemas and delete all columns mappings ( In our case C1 from Source file ) except OutputVariable.

6. For Debugging purpose, have added a Set variable activity prior to Copy activity to assign the Parameter value to the Variable :OutputVariable.

Result:

Input –

Output –