Copy Activity allows HTTP Post to get Excel but when debugging it fails

Steve Dunker 40

So I am trying to import an excel file from a website that uses a HTTP Post to pull the file. I have configured the linked service, dataset and then the Copy Activity. In the copy activity I am able to specifiy it use a Post method. When I click Preview Data I am able to see the data without a problem. I then import the schemas from the source and sink. I map just a single field to minimize everything. When I debug the pipeline I am getting the following error.

ErrorCode=ExcelUnsupportedFormat,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Only '.xls' and '.xlsx' format is supported in reading excel file while error is '   at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.ReadLeByte()
   at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.ReadLeInt()
   at ICSharpCode.SharpZipLib.Zip.ZipInputStream.GetNextEntry()
   at NPOI.OpenXml4Net.Util.ZipInputStreamZipEntrySource..ctor(ZipInputStream inp)
   at NPOI.OpenXml4Net.OPC.ZipPackage..ctor(Stream filestream, PackageAccess access)
   at NPOI.OpenXml4Net.OPC.OPCPackage.Open(Stream in1)
   at NPOI.Util.PackageHelper.Open(Stream is1)
   at NPOI.XSSF.UserModel.XSSFWorkbook..ctor(Stream is1)
   at Microsoft.DataTransfer.ClientLibrary.ExcelUtility.GetExcelWorkbook(String fileExtension, TransferStream stream)'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=ICSharpCode.SharpZipLib.Zip.ZipException,Message=EOF in header,Source=ICSharpCode.SharpZipLib,'

The error states it is not an Excel file but when I click their link directly it opens in Excel after downloading. The Preview data appears to work since I can see the file.

Any ideas on how to fix this because this could be a big time saver for me to process files every day.

phemanth 5,735 Reputation points Microsoft Vendor

2024-03-19T07:27:58.8433333+00:00

@Steve Dunker

Thanks for reaching out to Microsoft Q&A

The error message you’re seeing is typically due to the Azure Data Factory not recognizing the file format as ‘.xls’ or ‘.xlsx’, which are the only formats it supports for Excel files. However, since you’re able to open the file in Excel directly and preview the data, it seems like the file is indeed in a supported format.

One possible solution could be to change the dataset format in your pipeline configuration.

Here are the steps you could follow.

1.Source dataset format: select Binary format.

2.Setting of Http link service Base URL:https://go.microsoft.com/fwlink/?LinkID=521962, Authentication type select Anonymous.

3.Sink dataset format:select Binary format.And file path like below image:

4.download the Financial Sample.xlsx and you can see the data

After making these changes, try running the pipeline again. If the issue persists, it would be helpful to check the file format of your source file again, just to make sure it’s in ‘.xls’ or ‘.xlsx’ format.

Hope this helps. Do let us know if you any further queries.
Steve Dunker 40 Reputation points

2024-03-19T15:33:31.12+00:00

So let me be clear. When I download the file directly it has a .xlsx extension and opens without a problem in Excel.

When configuring the linked service and the dataset, I get errors stating that it got a 404 error. The interesting thing is when I go to the Copy Activity there is an parameter "Request Method" when I change that to POST I am able to preview the data in Azure Data Factory Studio. It imports the schema so I see all the columns listed in the Mapping section.

So with that in mind it appears ADF can read the file, however when I run the pipeline I get the error about the file not being the right type.
Steve Dunker 40 Reputation points

2024-03-19T15:35:15.9566667+00:00

When you are talking about changing the dataset to Binary format, I did not see an option for a Dataset or Linked Service for Binary Format
Steve Dunker 40 Reputation points

2024-03-19T15:39:02.0933333+00:00
Ok so I just tried to publish my pipeline and datasets and I am getting an error from the dataset for the Excel file with the following message.

The document creation or update failed because of invalid reference

My assumption is that since the url requires a Post not a get that is the problem.
phemanth 5,735 Reputation points Microsoft Vendor

2024-03-20T09:31:07.16+00:00
@Steve Dunker You're on the right track! The error message "The document creation or update failed because of invalid reference" likely indicates an issue with how your dataset references the linked service used for downloading the Excel file. Here's why a POST request might be causing the problem:

Linked Service Dependency:

Datasets in Azure Data Factory (ADF) rely on linked services to access data sources.

When you publish your pipeline and datasets, ADF validates all references between them.

Issue with POST Request:

Since your dataset retrieves data using a POST request, the linked service might not be configured to handle that specific method.

Linked services typically assume a GET request by default.

Resolving the Issue:

Here are two potential solutions to address the invalid reference error:

Modify Linked Service Configuration:

Check the configuration of your linked service used for downloading the Excel file.

Look for options related to HTTP methods. If available, configure the linked service to support POST requests explicitly.

Pre-Download the File (Workaround):

As a temporary workaround, consider downloading the Excel file using a separate tool (e.g., Postman) with the POST request.

Store the downloaded file in a supported location (e.g., Azure Blob Storage).

Update your dataset to point to the pre-downloaded file instead of the URL requiring a POST request. This avoids the need for the linked service to handle the POST method directly.

Steve Dunker 40

So I am using an HTTP Linked Service to get to the file. Below is its configuration

{
    "name": "GotSportFieldSchedule",
    "properties": {
        "annotations": [],
        "type": "HttpServer",
        "typeProperties": {
            "url": "https://system.gotsport.com",
            "enableServerCertificateValidation": true,
            "authenticationType": "Anonymous"
        }
    }
}

In reviewing the documentation for the HTTP Linked Service doesn't show anything for specifying Post/Get. Is there another Linked service that I should use?

When looking at the dataset I don't see how to set something for Post


{
    "name": "GotSportFieldSchedule",
    "properties": {
        "linkedServiceName": {
            "referenceName": "GotSportFieldSchedule",
            "type": "LinkedServiceReference"
        },
        "annotations": [],
        "type": "Excel",
        "typeProperties": {
            "sheetName": "UTD",
            "location": {
                "type": "HttpServerLocation",
                "relativeUrl": "/org_event/events/24868/matches_export?venue=14302"
            },
            "firstRowAsHeader": true
        },
        "schema": [],

    }
}

What is crazy is that when I go to the COPY activity set it to the Dataset with POST I am able to preview the data.

{
                "name": "Copy data1",
                "type": "Copy",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "ExcelSource",
                        "storeSettings": {
                            "type": "HttpReadSettings",
                            "requestMethod": "POST",
                            "requestTimeout": ""
                        }
                    },
                    "sink": {
                        "type": "AzureSqlSink",
                        "writeBehavior": "insert",
                        "sqlWriterUseTableLock": false
                    },
                    "enableStaging": false,
                    "translator": {
                        "type": "TabularTranslator",
                        "mappings": [
                            {
                                "source": {
                                    "name": "Match #",
                                    "type": "String",
                                    "physicalType": "String"
                                },
                                "sink": {
                                    "name": "GameNbr",
                                    "type": "String",
                                    "physicalType": "nvarchar"
                                }
                            }
                        ],
                        "typeConversion": true,
                        "typeConversionSettings": {
                            "allowDataTruncation": true,
                            "treatBooleanAsNumber": false
                        }
                    }
                },
                "inputs": [
                    {
                        "referenceName": "GotSportFieldSchedule",
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "GS_External_RawGames",
                        "type": "DatasetReference"
                    }
                ]
            }

I don't quite get what you are talking about in the second option as a temporary workaround. Are you saying that to be able to save the dataset config set it up to pull from a file it can do a get from such as azure blob storage, then when running the package dynamically set the relative URL to the one I need to pull the file using a POST. Or are you saying that I will need to download the file first put it in azure blob storage manually then run things from there? That is not really a solution for me because I need this to run every night to process the data.

phemanth 5,735 Reputation points Microsoft Vendor

2024-03-21T14:38:36.6433333+00:00

@Steve Dunker For your scenario, using the Script Activity with PowerShell or Azure Functions (approach A) seems like a viable option. This allows you to handle the POST request and download the file as binary data within your ADF pipeline, automating the entire process.

Here are some resources to get you started with Script Activities and downloading files using POST requests:

Script Activity in ADF: https://learn.microsoft.com/en-us/azure/data-factory/transform-data-using-script

Downloading files with PowerShell: https://4sysops.com/archives/use-powershell-to-download-a-file-with-http-https-and-ftp/

Azure Functions for HTTP triggers: https://learn.microsoft.com/en-us/azure/azure-functions/functions-bindings-http-webhook-trigger
phemanth 5,735 Reputation points Microsoft Vendor

2024-03-22T04:53:58.99+00:00

@Steve Dunker We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Steve Dunker 40 Reputation points

2024-03-22T16:24:57.84+00:00

I am not following how a Script Activity would help in this process, my understanding is that it would allow me to move data from one source to another via TSQL. Which is great and I use it for other things. The documentation does not appear to support PowerShell

The powershell might work, but it still looks like it wants to store the file locally which if running in ADF I don't have access to a C drive. Doesn't look like I could specify the outfile as a blob storage location.

Would I use the Azure Function to invoke the Powershell?
phemanth 5,735 Reputation points Microsoft Vendor

2024-03-25T09:12:19.5133333+00:00
@Steve Dunker You're on the right track with Azure Functions. Here's how it can help:

Create an Azure Function: Develop an Azure Function triggered by HTTP requests. This function can be written in PowerShell or any other supported language like C#.

Download File in the Function: Within the Function code, use libraries like Invoke-WebRequest (PowerShell) or the HttpClient class (C#) to make the POST request and download the file as binary data.

Store Downloaded Data: Instead of storing the file locally, leverage the Azure Functions SDK to directly upload the binary data to Azure Blob Storage using functions like CloudStorageAccount.CreateCloudBlobClient().GetBlobReference().

Call the Function from ADF: In your ADF pipeline, utilize a Web Activity to call the Azure Function you created. Configure the Web Activity to send a POST request, potentially with any required headers or body content for the file download.

Benefits:

Flexibility: Azure Functions provide more freedom in handling POST requests and data manipulation compared to Script Activities with T-SQL.

Managed Environment: You don't need to worry about local storage access or managing script execution environments.

Resources:

Azure Functions for HTTP triggers: https://learn.microsoft.com/en-us/azure/azure-functions/functions-bindings-http-webhook-trigger

Uploading to Azure Blob Storage from Azure Functions: https://learn.microsoft.com/en-us/azure/storage/blobs/storage-quickstart-blobs-dotnet

This approach leverages Azure Functions' capabilities to handle the POST request, download the file, and store it in Azure Blob Storage - all within a managed environment accessible by ADF.
Steve Dunker 40 Reputation points

2024-03-25T19:13:06.4233333+00:00

This looks similar to an approach I have been working with using Power Automate. The challenge I have there and I think similar here would be the Web Activity call to either Azure Function or Power Automate is asyncronous, which means I won't know when the file has actually been downloaded to start the rest of the ADF pipeline. Any thoughts on how to determine if the function is complete or not?
phemanth 5,735 Reputation points Microsoft Vendor

2024-03-27T17:17:35+00:00

@Steve Dunker Following up to see if the below answer was helpful. 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.

Accepted answer

phemanth 5,735 Reputation points Microsoft Vendor

2024-03-26T06:05:04.4366667+00:00
@Steve Dunker Here are a few approaches to address this challenge and ensure your ADF pipeline waits for the download completion before proceeding:

1. Azure Function Monitoring with Application Insights:

Integration: Enable Application Insights in your Azure Function. This built-in monitoring tool provides detailed logs about function executions.

Function Completion Logs: Azure Functions logs information about function start and completion at the "Information" level. You can configure ADF to wait until it sees the "Function completed" log in Application Insights.

Implementation: Utilize the Azure Monitor data factory activity within your ADF pipeline. Configure it to query Application Insights for the specific "Function completed" log message related to your download function. Once the query finds the log, the ADF pipeline will proceed.

2. Azure Blob Storage Monitoring with Event Grid:

Concept: Leverage Azure Event Grid, a serverless event routing service. Configure your Azure Blob Storage account to publish an event to Event Grid whenever a new blob (downloaded file) is uploaded.

ADF Trigger: Set up an ADF pipeline trigger based on the Event Grid event. This ensures the pipeline starts only when a new blob (downloaded file) arrives in storage.

Drawback: This approach doesn't confirm successful download by the function, only successful upload to storage. Consider additional logging within the function for better tracking.

3. Polling with Logic Apps (For Power Automate alternative):

Concept: If you're considering switching away from Power Automate due to its asynchronous nature, explore Azure Logic Apps. They offer built-in waiting capabilities.

Implementation: Create a Logic App workflow that triggers the download function. Within the Logic App, use a "Until" loop configured to poll a specific Azure Function endpoint (returning completion status) until it receives a success response. Once successful, the Logic App can then trigger your ADF pipeline.
Please sign in to rate this answer.
Steve Dunker 40 Reputation points

2024-03-26T15:19:10.49+00:00

Looking at the options I like the idea of option 3. One challenge that I am trying to overcome is this process will have approximately 20-50 file downloads and processing. My initial thought was to have a ADF pipeline that first retrieves the list of files to download, then while looping that list it will first download the file to Azure Blob Storage then run a dataflow with a dataset configured for that file to be processed into a staging table. Once the data has been processed the loop would then process the next file. Once all the files have been downloaded and processed the staging table would then be processed to move to production.

If l go with option 3 it sounds like I would need to break my design into various pieces but not sure how to coordinate at the end that all files have been processed. Right now I have tried a low tech option which is to have a progress table that before the PowerAutomate flow is run the pipeline updates a table stating it is processing. At the end of the Flow it updates the same row stating is finished. After the PowerAutomate flow is kicked off in the pipeline I then use a loop that checks the status of that table, once it gets the finished status it then starts the dataflow to move the data. All of that in a big loop for each file and when complete runs a data flow to move the data from staging to production.

Thoughts on this process? Keep in mind the pipeline will essentially only run once a day except when debugging something.

phemanth 5,735 Reputation points Microsoft Vendor

2024-03-27T09:18:14.7766667+00:00

@Steve Dunker concern about using Logic Apps (option 3) for his scenario with many downloads is valid. Here's a breakdown of his challenge and potential solutions:

Challenge:

The process involves downloading and processing 20-50 files.

Logic Apps with polling might become inefficient for many loops due to the overhead of calls to the function endpoint.

Solution Options:

Enhanced ADF Loop with Integration Runtime (IR):

Keep the core ADF pipeline design with a loop iterating through the download list.

Utilize an ADF Integration Runtime (IR) for better orchestration within the loop.

Within the loop, leverage the built-in "Wait Until" activity in ADF. Configure it to check for the downloaded file's existence in Blob Storage before proceeding. This avoids unnecessary function calls.

Azure Durable Functions:

Consider using Azure Durable Functions for the download process.

Durable Functions offer built-in orchestration capabilities for managing long-running workflows.

Design the download function as a durable function that tracks its progress and signals completion.

The ADF pipeline can then trigger the durable function and wait for its completion signal before processing the data.

Hybrid Approach (For Complex Workflows):

If the download logic is complex, combine Durable Functions with Logic Apps for a hybrid approach.

Use a Durable Function for the core download and processing logic with progress tracking.

Trigger the Durable Function from a Logic App.

The Logic App can then manage the overall workflow, including looping through the download list and triggering subsequent ADF dataflows upon completion of each download.

Comparison:

Enhanced ADF Loop: Simpler to implement, but might require additional logic within the IR for complex scenarios.

Durable Functions: Well-suited for managing long-running workflows with built-in orchestration. Might require some additional development effort.

Hybrid Approach: Offers flexibility for complex workflows, but involves managing multiple components
Sign in to comment

1 additional answer

Steve Dunker 40 Reputation points

2024-03-27T15:53:06.4266667+00:00

Thanks for the options. Was able to get my PowerAutomate solution to work by adding a management table that I monitor with an Until loop. Not as elegant as it could be and doesn't allow for parallelization. Given my current need that works, however I do think the long term solution is using Durable function.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment