0

I am currently automating the receipt of daily emails using Data Factory. I make a request through Microsoft API to retrieve the email attachments (always Excel files), but the call returns bytes.

At the moment, my workflow reads an Excel file from SharePoint and processes the data it contains. I would like to either write these bytes directly to SharePoint as an Excel file or, if possible, skip the step of saving to SharePoint and directly use the API call in my copyActivity within Data Factory. I would appreciate some guidance on how to achieve this.

Notes:

The only tools available are ADF and SQL Server. We do not have access to external storage other than SharePoint (no ADL). We do not have access to premium ADF features like Azure Functions or Python (no coding other than T-SQL).

Improve this question
8
  • Currently, excel as target dataset is not supported in ADF and if you want to copy as Binary file, you need ADLS gen2 to use the binary file datasets. Considering your constraints, it might not be possible to achieve your requirement only using ADF. You need to either a logic app or a storage account. Commented Jan 3 at 8:29
  • @RakeshGovindula Hello Rakesh, thanks for responding so fast. I have the bytes saved in a variable. Do you still think it's not possible to transform them in an excel and using Sharepoint to save the files? For example I do an API call to write to sharepoint my binary as txt (I don't know how to save them. since excel uses compressed binary. I'm going to try to save them as an xlsx and see if it will work fine or if it's going to be corrupted) Commented Jan 3 at 8:46
  • Is the Binary text a Base64 string? and coming to writing the string to sharepoint as file, I doubt sharepoint won't support that through ADF. I think for writing a text as file in ADF, you need a file dataset which should have a linked service of either a storage account or a file system of on-prem. Commented Jan 3 at 9:06
  • Hello @RakeshGovindula, I've done some testing and currently i'm downloading a corrupted excel file. The binary code I use is the one i get from the graph api request and it can either be bytes or rawbytes: Bytes prnt.sc/6ha1eYqf2hAo RawBytes prnt.sc/y3ZX3ssAXOi6 I've tried to write both responses to my sharepoint using the current API request: POST site/_api/web/GetFolderByServerRelativeUrl('path')/…, url='@{item().name}') body is the byte response. But it is creating corrupted excel files Commented Jan 3 at 10:49
  • If needed I could write the response as a .bin file on sharepoint and then read it on ADF. Unluckily I'm new to ADF so I'm not even sure if this is a possibility (to use an HTTP dataset to read bin as excel) @RakeshGovindula Commented Jan 3 at 11:24

1 Answer 1

Reset to default
0

As you mentioned

If possible, skip the step of saving the data to SharePoint and directly use the API call within my copyActivity in Data Factory.

I have found the below approach to Ingest data from Microsoft Graph API using Azure Data Factory.

Thank you @Kyle Gibson for excellent documentation.

I have tried the below appproach to sink data in ADLS as .json format.

Below are the steps:

Create an app registration and go to the api permissions to provide the required Graph API permissions to your App Registration.

enter image description here

Copy the secret from the Value column and store it securely, as this will be the only time to view the secret value. and store these value in an Azure Key Vault.

Now in the ADF Create a Linked service using the Rest service with below parameters:

enter image description here

Create a dataset:

Go to Services and apps just like when creating your linked service and select REST the same as your linked service. create a dataset parameter called endpoint and use the add dynamic content @dataset().endpoint

enter image description here

Create a pipeline set up a Web activity to fetch your bearer token for the Graph API.

Use the like below format URL: https://login.microsoftonline.com/YourTenantID/oauth2/v2.0/token

If you are using azure key vault use the below:

https://login.microsoftonline.com/@{activity('Get Tenant ID’).output.value}/oauth2/v2.0/token

enter image description here

Use the below for using azure key vault:

grant_type=client_credentials&client_id=@{activity(‘Get Client ID’).output.value}&client_secret=@{activity(‘Get Client Secret’).output.value}&scope=https://graph.microsoft.com/.default

Next use copy activity configure source using the dataset that is used same as Rest service.

enter image description here

For Authorization: Bearer @{activity(‘Get Token’).output.access_token}

For the Pagination rules you can use the below:

AbsoluteUrl: [‘@odata.nextLink’]

In the SINK configuration you can choose ADLS and write it to .json format.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hello DIleep. I'm not able to see the images but if i get it correct you're using ADLS which as said we don't have access too. We've resolved by using power automate to ingestion data to sharepoint

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.