How to set dynamic output file name in data flow

Question

How to set dynamic output file name in data flow

Kakehi Shunya (筧隼弥) 201

Hello.
I would like to dynamically set the name of the file to be output to the sink in the data flow, but I am getting an error.

{"message":"Job failed due to reason: at Sink 'sink1': File names cannot have empty value(s) while file name option is set as per partition. Details:","failureType":"UserError","target":"Data flow1","errorCode":"DF-Executor-InvalidPartitionFileNames"}

Sink settings are described below.

・Output as a single file.
・The name of the file to output is "emp_list_yyyyyMMdd.parquet".

concat('emp_list_',toString(toDate($as_of_date),'yyyy'),toString(toDate($as_of_date),'MM'),toString(toDate($as_of_date),'dd'),'.parquet')

　*The parameter '$as_of_date' is the value of the parameter '$as_of_date'.

For example, if the parameter passed is "2022-01-07T00:00:00", I want to output only one file, 'emp_list_20220107.parquet'.

・The parameter '$as_of_date' is a timestamp type, passed from the pipeline with the following settings

toTimestamp(left('@{item()}', 23), 'yyyy-MM-dd\'T\'HH:mm:ss.SSS')

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-12-29T06:09:51.98+00:00

Hi @Kakehi Shunya (筧隼弥) ,

Thankyou for posting your query on Microsoft Q&A platform.

Did you try using substring and formatDateTime functions using pipeline expression instead of passing the value for dataflow parameter as dataflow expression , something like this :

@formatDateTime(substring(item(),0,23),'yyyy-MM-dd HH:mm:ss')

Please let me know how it goes. Thankyou
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-01-05T05:28:41.64+00:00

Hi @Kakehi Shunya (筧隼弥) ,
Have you tried the above suggested approach? If you have been able to resolve the issue with some other approach, I would request you to share the same with the community. Thanks

1 answer

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-12-29T06:09:51.98+00:00

Hi @Kakehi Shunya (筧隼弥) ,

Thankyou for posting your query on Microsoft Q&A platform.

Did you try using substring and formatDateTime functions using pipeline expression instead of passing the value for dataflow parameter as dataflow expression , something like this :

@formatDateTime(substring(item(),0,23),'yyyy-MM-dd HH:mm:ss')

Please let me know how it goes. Thankyou
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-01-05T05:28:41.64+00:00

Hi @Kakehi Shunya (筧隼弥) ,
Have you tried the above suggested approach? If you have been able to resolve the issue with some other approach, I would request you to share the same with the community. Thanks

Answer 1

MarkKromer-MSFT 5,226 Microsoft Employee Moderator

It sounds like sometimes the parameter value is either empty or cannot convert to a valid timestamp. On you monitoring view above in ADF, hover over one of the failed data flow activities and click on the input icon. Observe the value of the input parameter being sent to the data flow activity to ensure that it is valid.

Kakehi Shunya (筧隼弥) 201 Reputation points

2022-12-24T04:53:51.787+00:00
Hi,@MarkKromer-MSFT
Thank you for your answer.

I checked the input icon of the filed dataflow activity, and the input parameter was this.

From what I can tell, I need to set up the expression this way when passing the timestamp parameter from the pipeline to the data flow, is this incorrect?

toTimestamp(left('@{item()}', 23), 'yyyy-MM-dd\'T\'HH:mm:ss.SSS')

Share via

How to set dynamic output file name in data flow

1 answer

Your answer