CODEWITHSUNDEEP
azureazure-data-factoryazure-synapse
E. Erfan
E. Erfan

Reputation: 1411

Azure Synapse Analytics adding source file name as column value in sink in pipelines copy data

I am using Azure Synapse Analytics pipelines to copy a number of excel data files in Azure blob storage and copy their content into a dedicated SQL DB in Azure Synapse Analytics. I am using copy data activity. In Azure data factory it is possible to define an additional column in source and populate its value by $$FILEPATH which refers to the name of the source file. In Synapse Analytics however, it is not allowing to specify additional columns in sink. When I try to debug my pipeline, I get the error:

Specifying additional columns is not allowed when using copy command.

This is what I mean by defining a new column in source and the error I am getting:

enter image description here

The error is as follows:

enter image description here

What is the best workaround to copy the names of the source files into each row of the table in sink to be able to trace where the data is coming from? I prefer to use pipelines rather than data flows as pipeliens are easier to develop and less expensive. But any solution that can resolve this is good.

Upvotes: 2

Views: 1877

Answers (1)

NiharikaMoola
NiharikaMoola

Reputation: 5074

You might have selected copy method as copy command under sink.

As the error message states, additional columns are not allowed when using the copy command.

enter image description here

To copy data from azure storage to synapse DB when additional columns are involved, change the copy method to bulk insert

Input:

enter image description here

Copy data Source:

enter image description here

Copy data Sink:

enter image description here

Output:

enter image description here

Upvotes: 2

Related Questions