I have a dynamic variable. How do I set the file name in the sink to the variable?

Hello Lilly W Thanks for the ask and using MS Q&A platform. Here is a video that explains the steps for both copy and data flow activities. Below steps are for copy activity. If you are using a copy activity, and want to pass variable value as file name in sink, please follow below steps. create a parameter called filename in sink dataset as shown below and use the parameter in the filename part of the data set Next, use the parameter created above in the connection tab of the dataset, in the filename portion as shown below Go to copy activity and click on sink Now you will be able to see the filename parameter in the sink click on the dynamic expression and choose the variable name which contains the file name and you are done. Im having the below expression in my set variable expression @concat('filename',formatDateTime(utcNow(),'MMddyyyy'),'.csv') what i am trying to do is, to capture current date in MMddyyyy format dynamically and suffix it next to my filename and then finally using .csv. (Basically i am trying to convert a ,xlsx file to .csv file with current date in the file name) After i ran the pipeline, i see my file getting converted to .csv as well as using the dynamic name I am deriving in my set variable activity. Hope this helps.

Add parameter (sinkFilename) Add Copy Data activity Select source dataset Select sink data source Pass the parameter to the sink dataset Add dynamic content to the File Path Use the following expression @pipeline().parameters.sinkFilename

How to Set Sink FileName to Variable Value

Accepted answer

Suba Balaji 11,206 Reputation points

2023-07-19T05:14:21.96+00:00
Hello
Lilly W

Thanks for the ask and using MS Q&A platform.

Here is a video that explains the steps for both copy and data flow activities. Below steps are for copy activity.

If you are using a copy activity, and want to pass variable value as file name in sink, please follow below steps.

create a parameter called filename in sink dataset as shown below and use the parameter in the filename part of the data set

Next, use the parameter created above in the connection tab of the dataset, in the filename portion as shown below

Go to copy activity and click on sink

Now you will be able to see the filename parameter in the sink

click on the dynamic expression and choose the variable name which contains the file name and you are done.

Im having the below expression in my set variable expression

@concat('filename',formatDateTime(utcNow(),'MMddyyyy'),'.csv')

what i am trying to do is, to capture current date in MMddyyyy format dynamically and suffix it next to my filename and then finally using .csv. (Basically i am trying to convert a ,xlsx file to .csv file with current date in the file name)

After i ran the pipeline, i see my file getting converted to .csv as well as using the dynamic name I am deriving in my set variable activity. Hope this helps.
Please sign in to rate this answer.
Lilly W 130 Reputation points

2023-07-19T12:25:21.9733333+00:00

When I run it the variable value is being set correctly in the "Set variable" task. But the file name is a long garbled text like this.

body('SFTP File DumpComposeRuntimeVariables')?.PipelineName5g8tutuu876gjgjhfhtrytjhgugu7646546kjg.filename

Lilly W 130 Reputation points

2023-07-19T12:30:02.9533333+00:00

Sorry if this comment post twice.

The variable value is being set correctly in the "Set variable1" task. The file name looks like this.

body('SFTP File DumpComposeRuntimeVariables')?.PipelineName5g8tutuu876gjgjhfhtrytjhgugu7646546kjg.filename

Suba Balaji 11,206 Reputation points

2023-07-19T13:14:01.9333333+00:00

Could you please post how are you seeing it in set variable and how are you using it in sink? Possibly with a screenshot

Lilly W 130 Reputation points

2023-07-19T16:50:44.72+00:00

I can't post images.

I have a variable MyVariable at the pipeline level/scope (string).

The Lookup task called Lookup1 queries a database to return 1 value.

"Set variable" task:
under Setting: Name = MyVariable Value = @concat('List', activity('Lookup1').output.firstRow.MyVariable, '.txt')

When I run the pipeline the value is correct.

Suba Balaji 11,206 Reputation points

2023-07-20T00:10:31.21+00:00

How are you referring to the variable from sink? What's the expression you use? Is it copy activity or data flow that you use. Pls explain so that we can fix the issue

Suba Balaji 11,206 Reputation points

2023-07-21T01:16:01.42+00:00

Hi Lily, just curious to know if you resolved the issue. If not, please let us know the expression you use in sink setting

Lilly W 130 Reputation points

2023-07-21T12:12:55.6733333+00:00

Sorry for the delay. I ran it again. The variable, MyVariable, value is "TestResults22-23.csv" which is what I want it to be. The file output is now "dbo.Results.txt". The sink dataset has a filename parameter but it's not used in the File path under the sink dataset's Connection tab.

Copy data task/Sink tab/Sink dataset/filename is set to @variables('MyVariable').

BTW This is the name of the table in the database the Lookup task is querying dbo.Results

Suba Balaji 11,206 Reputation points

2023-07-21T14:47:12.05+00:00

In your sink data set, do you have a hard-coded value in filename portion saying dbo.results.txt ?? Please check. If so, remove that and map the parameter name in the filename field as shown in my second screenshot.

Lilly W 130 Reputation points

2023-07-22T02:04:51.81+00:00

I made some changes to try to get rid of the long file name that was first returned.

In the "Lookup task":
The query returns 22-23 as the MyVariable value.

In the "Set variable" task:
@concat('TestResults', activity('Lookup1').output.firstRow.MyVariable, '.csv')

In the "Copy data" task:
The filename is @variables('MyVariable')

Suba Balaji 11,206 Reputation points

2023-07-22T02:08:40.1466667+00:00

All good so far. Are you using the passed value in sink dataset or you are using a hard-coded file name in sink data set connection tab - filename option..

Lilly W 130 Reputation points

2023-07-22T02:13:40.1533333+00:00

Under the Sink tab the filename value is @variables('MyVariable'). I don't have anything hard coded here.

Suba Balaji 11,206 Reputation points

2023-07-22T14:23:42.1066667+00:00

Did you check the sink data set itself ? Not the sink tab in copy activity

Lilly W 130 Reputation points

2023-07-22T16:12:03.6233333+00:00

If you click on the Open link for the Sink dataset the file name if blank.

Suba Balaji 11,206 Reputation points

2023-07-22T17:47:46.76+00:00

Ok. If you click open and land in the data set definition, in the filename portion you should use the below expression> @dataset().filename

It shouldn't be left blank. Please try this and run your pipeline and see if it works

Lilly W 130 Reputation points

2023-07-22T18:15:18.0866667+00:00

That's it! Thank you!

Suba Balaji 11,206 Reputation points

2023-07-23T12:57:48.23+00:00

Glad it worked 🙂🙂 thanks for confirming
Sign in to comment

Share via

How to Set Sink FileName to Variable Value

1 additional answer