question

BharathRS-9450 avatar image
0 Votes"
BharathRS-9450 asked Samy-7940 answered

Copy files from Sharepoint into Azure Data Lake Store Gen 2 using ADF

Hi,

Can you let me know if there is a possibility of copying files from Sharepoint folder into Azure data lake store using ADF?

Regards,
Bharath R S

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered

I have seen similar questions like this but I don't think there was any answer given.

18486
22486
how-to-fetch-sharepoint-data-and-load-into-azure-blob-storage-using-azure-data-f

I had to implement similar requirement once and I ended up using Logic App for this. You can also run the logic app from Data factory if that kind of integration is required

transfer-files-from-sharepoint-to-blob-storage-with-azure-logic-apps


===============================================
If the response helped, do "Accept Answer" and upvote it -- Vaibhav

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KranthiPakala-MSFT avatar image
0 Votes"
KranthiPakala-MSFT answered Kazer-4611 published

Hi @BharathRS-9450 ,

Could you please refer to below MS doc and see if that helps to achieve your requirement. In case if you face any issues or if you have any feedback/suggestions regarding this implementation, please do share it with us so that I can take it forward to appropriate team.

Here is the MS doc: Copy file from SharePoint Online using Azure Data Factory

You can copy file from SharePoint Online by using Web activity to authenticate and grab access token from SPO, then passing to subsequent Copy activity to copy data with HTTP connector as source.

21642-image.png

  1. Follow the Prerequisites section to create AAD application and grant permission to SharePoint Online.

  2. Create a Web Activity to get the access token from SharePoint Online:
    - URL: https://accounts.accesscontrol.windows.net/[Tenant-ID]/tokens/OAuth/2. Replace the tenant ID.
    - Method: POST
    - Headers:
    - Content-Type: application/x-www-form-urlencoded
    - Body: grant_type=client_credentials&client_id=[Client-ID]@[Tenant-ID]&client_secret=[Client-Secret]&resource=00000003-0000-0ff1-ce00-000000000000/[Tenant-Name].sharepoint.com@[Tenant-ID]. Replace the client ID, client secret, tenant ID and tenant name.

    Note: Set the Secure Output option to true in Web activity to prevent the token value from being logged in plain text. Any further activities that consume this
    value should have their Secure Input option set to true.

  3. Chain with a Copy activity with HTTP connector as source to copy SharePoint Online file content:
    - HTTP linked service:
    i) Base URL: https://[site-url]/_api/web/GetFileByServerRelativeUrl('[relative-path-to-file]')/$value. Replace the site URL and relative path to file. Sample relative
    path to file as /sites/site2/Shared Documents/TestBook.xlsx.
    ii) Authentication type: Anonymous (to use the Bearer token configured in copy activity source later)

    - Dataset: choose the format you want. To copy file as-is, select "Binary" type.
    - Copy activity source:
    i) Request method: GET
    ii) Additional header: use the following expression@{concat('Authorization: Bearer ', activity('<Web-activity-name>').output.access_token)}, which uses the Bearer token generated by the upstream Web activity as authorization header. Replace the Web activity name.

    - Configure the copy activity sink as usual.


Hope this helps. Please let us know how it goes.

Thank you.


image.png (6.7 KiB)
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BharathRS-9450,

Following up to see if you have got a chance to see the information shared in my previous response. Please let us know if that was helpful or if you have any further query.

Thank you

0 Votes 0 ·

How does this work with creating content within SharePoint? We wanted to move files from file shares to SharePoint document library, the files will be in bulk. How to push the files to SharePoint? Any help or pointers?

0 Votes 0 ·

Hi @KranthiPakala-MSFT ,

We are trying to use the above approach, to copy Excel files from SharePoint Folder. I am able to create the linked service and test the connection successfully; but that is possible even without using the Web Activity to get the access token , since i have the access to the Sharepoint online. When i am trying to preview the data , it says that unable to read the Sheet. Is there any other way we can copy the files from Sharepoint to Azure blob storage?
Thanks & Kind Regards

0 Votes 0 ·
SiddharthaReddy-2322 avatar image SiddharthaReddy-2322 GurvinderKandhola-1509 ·

I'm also facing the exact same issue. Trying to get a file from Sharepoint but no success so far even I followed the post as is.

Hi @KranthiPakala-MSFT ,

Are we missing anything? I've used an Excel as a source dataset. Not able to select sheet name and preview is giving error even I manually enter the sheet name.

Please assist.

thanks in advance!

0 Votes 0 ·
Kazer-4611 avatar image Kazer-4611 SiddharthaReddy-2322 ·

Hey! Same issue for me. DId you find a solution yet?

0 Votes 0 ·

HI,
i am trying to load an excel file present on share point.
I have applied the same steps as mentioned above. but when I try to select sheet name it gives error as "load failed."
app registration, http linked service created successfully.

I am using below as my base url in data set:

https://domain.sharepoint.com/sites/sitename/_api/web/GetFileByServerRelativeUrl('/sites/sitename/foldername/filename.xlsx')/$value

0 Votes 0 ·
Kazer-4611 avatar image Kazer-4611 rupamalviya-4616 ·

Hey! Same for me. Did you find a solution?

0 Votes 0 ·
TMChaitraSBOBNGITYFB-0943 avatar image
0 Votes"
TMChaitraSBOBNGITYFB-0943 answered

Hi @KranthiPakala-MSFT,

how to do if they are bulk files getting loaded to sharepoint , and we need loop to run over these files and get metadata doesnt support HTTPS ..

please share thoughts

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Samy-7940 avatar image
0 Votes"
Samy-7940 answered

Hi All, I am sorry if I have missed it ,but bit surprised that no one has mentioned Azure functions to accomplish this task. This could be also an alternative solution. Thank you.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.