Dynamicly processing multiple excel sheets in a Data Flow activity nested in a ForEach - Stack Overflow

admin管理员组

文章数量:1201411

As a bit of a follow up to my last question I am running into some issues with my data flow, and I don't know if its something thats even possible. Can someone confirm if this is possible, and if so, why it wouldn't be working?

Step 1: Data is loaded into a blob storage - Works fine

Step 2: A script activity is sent to Snowflake, returns a table of data with 3 columns: RowID, SnowflakeName, Sheetname - Works fine

Step 3: A ForEach container sets the SnowflakeName and SheetName into variables, that are then used as parameters in the data flow - Works fine

Step 4: A data flow of three parts - Read the excel spreadsheet in blob using the "SheetName" to identify which sheet to read. Make the headers all uppercase. Move the data to snowflake:

As each sheet has different columns, I haven't imported a schema and allowed schema drift for it to identify the change in columns. In the snowflake output I have enabled Auto-Mapping for the same reason.

In my mind, this should all be possible. Each time the ForEach runs for each new row, it should accurately attribute the SheetName and SnowflakeTable into variables, which it does, but it then fails on the data flow, saying its an invalid identifier. The column exists in Snowflake, and matches data types (string) to what is being picked up from the excel file.

The error code DF-Executor-UserError is troubleshooted on Microsofts Troubleshooting as:

Error code: DF-Executor-UserError Message: Job Failed due to reason: GetjobStatus, Job Failed - com.microsoft.dataflow.issues: DF-MICROSOFT365-CONSENTPENDING Cause: Privileged access approval is needed to copy data. It's a user configuration issue. Recommendation: Ask the tenant admin to approve your Data Access Request in Office365 in privileged access management (PAM) module.

In terms of PIM approvals, I have all relevant permissions to access the subscription. Is there a specific role thats needed for this process to run in debug mode that I may have missed? Or is this error message a red herring and theres something else wrong with the process? If its even possible?

Any help appreciated!

As requested in comments, I am using the SnowflakeTable variable in the LinkedService for snowflake:

As a bit of a follow up to my last question I am running into some issues with my data flow, and I don't know if its something thats even possible. Can someone confirm if this is possible, and if so, why it wouldn't be working?

Step 1: Data is loaded into a blob storage - Works fine

Step 2: A script activity is sent to Snowflake, returns a table of data with 3 columns: RowID, SnowflakeName, Sheetname - Works fine

Step 3: A ForEach container sets the SnowflakeName and SheetName into variables, that are then used as parameters in the data flow - Works fine

Step 4: A data flow of three parts - Read the excel spreadsheet in blob using the "SheetName" to identify which sheet to read. Make the headers all uppercase. Move the data to snowflake:

As each sheet has different columns, I haven't imported a schema and allowed schema drift for it to identify the change in columns. In the snowflake output I have enabled Auto-Mapping for the same reason.

In my mind, this should all be possible. Each time the ForEach runs for each new row, it should accurately attribute the SheetName and SnowflakeTable into variables, which it does, but it then fails on the data flow, saying its an invalid identifier. The column exists in Snowflake, and matches data types (string) to what is being picked up from the excel file.

The error code DF-Executor-UserError is troubleshooted on Microsofts Troubleshooting as:

Error code: DF-Executor-UserError Message: Job Failed due to reason: GetjobStatus, Job Failed - com.microsoft.dataflow.issues: DF-MICROSOFT365-CONSENTPENDING Cause: Privileged access approval is needed to copy data. It's a user configuration issue. Recommendation: Ask the tenant admin to approve your Data Access Request in Office365 in privileged access management (PAM) module.

In terms of PIM approvals, I have all relevant permissions to access the subscription. Is there a specific role thats needed for this process to run in debug mode that I may have missed? Or is this error message a red herring and theres something else wrong with the process? If its even possible?

Any help appreciated!

As requested in comments, I am using the SnowflakeTable variable in the LinkedService for snowflake:

• snowflake-cloud-data-platform
• azure-data-factory

Share Improve this question Follow edited Jan 21 at 16:07 MikeLanglois asked Jan 21 at 13:54 MikeLangloisMikeLanglois 6122 silver badges99 bronze badges 4

• 1 Can you show where you are using the first parameter snowflakeTable in the dataflow? – Rakesh Govindula Commented Jan 21 at 15:35
• 1 Thanks @RakeshGovindula I have included two new pictures in an edit on the main question – MikeLanglois Commented Jan 21 at 16:07
• 1 Let me try from my end. I will update you. – Rakesh Govindula Commented Jan 21 at 16:53
• Thanks very much I appreciate the help :) – MikeLanglois Commented Jan 22 at 15:29

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You are using the dataflow parameters in a wrong way. Based on your source and sink of the dataflow, it seems you are using Inline excel dataset for the dataflow source and Normal snowflake dataset for your dataflow sink.

When you want to pass any value to a normal dataset using parameters, you need to use dataset parameter. If you are using Inline dataset, then you can use pass the value to it using dataflow parameters.

In this case, you are directly giving the dataflow parameter in the snowflake sink dataset which might be the reason for the above error.

To achieve your requirement, use dataset parameters for that dataset. In the dataset, create the required string parameter.

Now, use this parameter as @dataset().<parameter_name> in the table name of the dataset.

Now, give this dataset to your sink. In the dataflow activity of the pipeline, it will ask to provide value. Here, you can pass the required table name from the pipeline to this parameter using pipeline expression.

As you are using Inline dataset for the dataflow source, you can use dataflow parameter for it. But for the sink, you need to use dataset parameters as it is a normal dataset.

If you want to use Inline dataset for the dataflow sink, you can do that directly. In the dataflow sink, select Inline and choose Snowflake and give your snowflake linked service.

Now, you can use the dataflow parameter for the snowflake table name in the dataflow sink settings as shown below.

本文标签： Dynamicly processing multiple excel sheets in a Data Flow activity nested in a ForEachStack Overflow