In Azure Data Factory, how can I set an Excel dataset parameter for file name from the data flow that uses this Excel dataset as its source?

Question

In Azure Data Factory, how can I set an Excel dataset parameter for file name from the data flow that uses this Excel dataset as its source?

Michael G 20

Here's a little more detail of what I am trying to do:

PROVIDED AS BACKGROUND, I DO NOT NEED HELP ON THIS PART I have a pipeline that has 2 parameters: type and file name. The type parameter is used to determine which of 7 different data flow to call - one for each of 7 different Excel sheet types. Each of the data flows will run an import of data from Excel To SQL Server. The pipeline will pass the file name as a parameter to the data flow called. I have the pipeline and the data flow parameters working the way I want.
I NEED HELP ON THIS PART Each data flow uses an Excel data source and I need to be able to pass the file name as a parameter to the Excel data set. I have defined my Excel datasets to have a file name parameter , and can use this in specifying the path for the dataset. However, I do not see a way to set dataset parameters from the data flow using them as a source. Is there a way to do this?

Chandra Boorla 14,665 Reputation points Microsoft External Staff Moderator

2024-08-06T13:49:23.4966667+00:00

Hi @Michael G

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

As I understand, you want to pass the file name parameter from the data flow to the Excel dataset used as a source in the data flow. The Excel dataset has a parameter for the file name, but you did not see a way to set this parameter from the data flow.Unfortunately, Azure Data Factory doesn't provide a direct way to set dataset parameters from within a data flow. Instead, you can effectively achieve the desired behavior by using a combination of pipeline and data flow parameters.Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

Leveraging Pipeline and Data Flow Parameters:
Define Dataset Parameter: Open your Excel dataset. Add a parameter named fileName. Use the Dataset Parameter: In the Connection tab, set the file path to @dataset().fileName. Configure Data Flow: Open your data flow. Ensure the Excel source dataset is selected. Configure Data Flow Activity in the Pipeline: Open your pipeline. Add a Data Flow activity. In the Data Flow activity settings, map the fileName pipeline parameter to the data flow parameter. Set Dataset Parameter in the Source: In the Data Flow activity, go to the "Source settings" for the Excel source. Set the file name parameter to @pipeline().parameters.fileName. By following these steps, you should be able to dynamically set the file name in your Excel dataset using a pipeline parameter. This will allow you to pass different file names to your data flow and import data from different Excel files

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Chandra Boorla 14,665 Reputation points Microsoft External Staff Moderator

2024-08-07T10:33:59.7133333+00:00

Hi @Michael G

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Michael G 20 Reputation points

2024-08-07T16:41:50.7433333+00:00

Thank you @Chandra Boorla for the courtesy of your response. Unfortunately, I do not think the information you provided addresses my issue. I see where you have a file name parameter in both the pipeline and the dataflow, and how you set the dataflow file name parameter in the dataflow using the pipeline parameter. What I have not found is a way in the dataflow to set a dataset parameter for a dataflow source object.

Since making my original post, I can report that I found a workaround whereby I can set the dataflow source "Source options - Wildcard paths" to the value of my data flow fileName parameter (see attached image). This will cause the Excel dataset object to use the parameter driven file name.
Chandra Boorla 14,665 Reputation points Microsoft External Staff Moderator

2024-08-08T07:11:32.5866667+00:00

Hi @Michael G

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.
Michael G 20 Reputation points

2024-08-08T15:22:53.9166667+00:00

Hi @Chandra Boorla , I would like to accept the workaround I provided as the answer to my question. Please repost.

Thank you for all of your attention to this issue.

Accepted answer

0 additional answers

Your answer

Chandra Boorla 14,665 Reputation points Microsoft External Staff Moderator

2024-08-07T10:33:59.7133333+00:00

Hi @Michael G

Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Michael G 20 Reputation points

2024-08-07T16:41:50.7433333+00:00

Thank you @Chandra Boorla for the courtesy of your response. Unfortunately, I do not think the information you provided addresses my issue. I see where you have a file name parameter in both the pipeline and the dataflow, and how you set the dataflow file name parameter in the dataflow using the pipeline parameter. What I have not found is a way in the dataflow to set a dataset parameter for a dataflow source object.

Since making my original post, I can report that I found a workaround whereby I can set the dataflow source "Source options - Wildcard paths" to the value of my data flow fileName parameter (see attached image). This will cause the Excel dataset object to use the parameter driven file name.
Chandra Boorla 14,665 Reputation points Microsoft External Staff Moderator

2024-08-08T07:11:32.5866667+00:00

Hi @Michael G

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.
Michael G 20 Reputation points

2024-08-08T15:22:53.9166667+00:00

Hi @Chandra Boorla , I would like to accept the workaround I provided as the answer to my question. Please repost.

Thank you for all of your attention to this issue.

Answer 1

Hi @Michael G

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Ask: In Azure Data Factory, how can I set an Excel dataset parameter for file name from the data flow that uses this Excel dataset as its source?

Here's a little more detail of what I am trying to do:

PROVIDED AS BACKGROUND, I DO NOT NEED HELP ON THIS PART I have a pipeline that has 2 parameters: type and file name. The type parameter is used to determine which of 7 different data flow to call - one for each of 7 different Excel sheet types. Each of the data flows will run an import of data from Excel To SQL Server. The pipeline will pass the file name as a parameter to the data flow called. I have the pipeline and the data flow parameters working the way I want.
I NEED HELP ON THIS PART Each data flow uses an Excel data source and I need to be able to pass the file name as a parameter to the Excel data set. I have defined my Excel datasets to have a file name parameter , and can use this in specifying the path for the dataset. However, I do not see a way to set dataset parameters from the data flow using them as a source. Is there a way to do this?

Solution: Since making my original post, I can report that I found a workaround whereby I can set the dataflow source "Source options - Wildcard paths" to the value of my data flow fileName parameter (see attached image). This will cause the Excel dataset object to use the parameter driven file name.

User's image

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Share via

In Azure Data Factory, how can I set an Excel dataset parameter for file name from the data flow that uses this Excel dataset as its source?

0 additional answers

Your answer