This video helped create a successful ADF pipeline to pull a file from sharepoint site . I just followed the steps mentioned in your video and it works . Thanks for posting this video
You are amazing!!! If you are doing an xlsx file, instead of Binary use xlsx file type and you can copy right into SQL rather than having to drop into data lake or blob storage first.
I don't think you can do that, but give it a try and let us know if it's possible. Anyway it's a good practice to keep an archive of the data you are processing, therefore, keeping a copy should be the best approach in most of the cases.
This is a useful tips but I have question, if you have multiple files in SharePoint document folder how do you read the folder metadata to get ChildItem to iterate in the ForEach activity since metadata activity has no connector for SharePoint?
Hi Javibi,I'm getting an error after 4:34 when I click on "Trust," which says, "Sorry, you don't have access." Could this be because I don’t have access to the SharePoint site, or should I inform the Azure admin to grant the necessary API permissions for the app? I see that the permissions are currently "Not granted." Please advise.
No, but I think you need to create a process for each of the files. Don't think in SharePoint source as a blob storage where you can use the metadata activity and loop over several files.
I think they need to be same tenant . when you create a app in AAD, you need to mention the same tenant where your sharepoint site is available . usually they have dev tenant and prod tenant
Great tutorial! Is there a way to make the filename dynamic for xlsx? So if we have filename.xlsx, how do we do it for *.xlsx or %file%.xlsx to capture anything named file or all files in the directory?
Using the above way, I can connect to SP and pull an Excel file to be stored as an Azure blob. I then want the blob to be a dataset in my dataflow. Can I have the web token+copy data pipeline run periodically? (As the same Excel file in Sharepoint may be updated periodically?)
Hello ! thank youu for the video.. I suppose we can do reverse the same way? by switching sink and source in copy data? from datalake to sharepoint? if not, why? thank you again 👍
I didn't get that far but I would say it's not possible. There is an end point that return the content of the Sharepoint folder on XML format that it's a bit complex and not very well formated. I would give it a try
To copy file from sharepoint , can we use excel dataset instead of binary . In this video it is explained as binary format. In copy activity configuration, if i set source a binary dataset then sink has to binary dataset as well. Can someone help how do i achieve the requirement to copy a excel sheet(a.xlsx) from a SharePoint website and put that in to azure storage account container as csv file
@javibi, What an excellent video!!!. This process also works for MS Fabric? In my case, I will copy txt data from SharePoint to the bronze layer in MS Fabric.
@Aditya Singh there is an endpoint to list content in a folder, but if I remember well, it returns a XML not really well formated with high complexity to extract data from it, but give it a try.
Thank you so much for the very explanatory video with great details. Is it possible to make SharePoint as Destination and Azure BLOB as a source ? Do we need any extra permission to use SharePoint as sink
Hi, I don't think you can do that with Data Factory directly, but I think you can do that with a Logic App. Then you can call the Logic App from Data Factory
@@javibi I came across a limitation that Logic App only supports files up to 1GB . Our project requirement is store files up to 2GB from Azure BLOB to SharePoint . What is the best method I can explore to achieve this requirement ? Any idea ?
Difficult one. With files of that size I would recommend a Data Lake. Maybe another option is a SFTP. Maybe also using s VM with a folder synched with SharePoint and placing the file in it? Not sure right now if that's even allowed in Data Factory.
Hi, It was a great video. I wanted to know if I am copying data from sharepoint to ADLS and if I delete one file from sharepoint then I want to delete the same file from ADLS too. How can I implement this?
I'm copying .xlsx file from sharepoint. But the files are copied to blob in different (unreadable) format. I guess it is in xml format. Please help on this
Same with me .. It doesnt work for xlsx files.. file content is shown in xml format when you download $value file in notepad++ @vasantha .. were you able to resolve the issue ?
There's an endpoint to get the list of files in a Sharepoint folder but, if I remember right, but the output is an xml with a not really nice format. Give it a try!
HI! thanks for sharing walkthrough. I'm getting the following error: ErrorCode=UserErrorInvalidCredentialToReadHttpFile,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The credential to read http file is invalid.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Net.WebException,Message=The remote server returned an error: (401) Unauthorized.,Source=System,'
Hi @Dilip, there is another endpoint to list the Sharepoint content but, if I dont remember wrong, the output is an xml and it's not very good formated to create a dynamic call. Anyway, I would suggest you to give it a try.
I am trying to copy about 500 gb of data from sharepoint to Azure Files. Is it possible to copy an entire FIle structure? there are a lot of folders and files within the root folder. Please help me
Thanks for sharing, I have tried the same steps to get data from a SharePoint list and it keeps saying Unauthorized, I already checked the URL passed to the copy activity returns values. I did not see access to SharePoint was given when the app was registered in Azure App Registration. Can this be the reason?
@@javibi thanks for responding, I am having a really hard time setting up the linked service, I registered the app in Azure, gave full permissions, and I am using the client id and secret but no success. Have you done this before? Any ideas? Thanks!
Yes I did it a couple of times. I'll suggest you to test the process on Postman with hardcoded values to check if it works. This way you confirm that the client Is and setting are working fine. Probably it is just a small thing you missed
@@javibi ,error code is "error": "invalid_request", "error_description": "AADSTS50169: The realm '9756717f-02ac-4b97-bc8e-0257aef812dc' is not a configured realm of the current service namespace. Trace ID: 2588bbc5-1c0b-4203-96a7-a68374fb5600 Correlation ID: 31f01417-7f62-4db5-949f-a8cef0825565 Timestamp: 2023-08-24 11:19:01Z", "error_codes": [ 50169 ],
I am getting below error while running Get Token activity {"error":"invalid_client","error_description":"AADSTS7000215: Invalid client secret provided. Ensure the secret being sent in the request is the client secret value, not the client secret ID, for a secret added to app 'c602b95b-6dba-4e30-9eb0-d52d33d2920c'. Trace ID: 7ca2f6a7-d8ce-4181-be1a-7dc05e030800 Correlation ID: 8d5c75ab-9ff0-48fd-aca3-00ed2e80e9ae Timestamp: 2023-06-08 00:56:37Z","error_codes":[7000215],"timestamp":"2023-06-08 00:56:37Z","trace_id":"7ca2f6a7-d8ce-4181-be1a-7dc05e030800","correlation_id":"8d5c75ab-9ff0-48fd-aca3-00ed2e80e9ae","error_uri":"accounts.accesscontrol.windows.net/error?code=7000215"}
I have the base url copied in a new browser and it throws me a XML file does not appear error. Does this mean that the SharePoint permissions were not granted properly?
The base url to download the file applied on you browser should download the file if you are logged with your account and you have permissions if I remember right.
@@javibi I mean the permissions to the SharePoint was granted by someone else. But I'm creating the add pipeline.. it should work anyway right? I mean the site url has been given access too so if I use the same procedure it should be working i suppose.
If you dont have access to the Sharepoint file I guess and xml should come with an error. The only way to test if the permissions are granted properly to de appid is to use it through Data factory or through a Postman call
@@javibi I'm just a member to that site not an owner so do I need the role of an owner to do this. Or even with a member role can I follow the same procedure as you and proceed? Just FYI, I can access the file if I go to SharePoint.
If you are able to access the file through the Sharepoint portal, typing the same url you should use to download the file on Data Factory should download a file on your browser
I have created an App id , but when i search it in share point , I am unable to see the App Name while i use look up option. Is it related to access issue
Thanks for this video! great work! Can you explain how you can setup a pipeline to use a trigger so when a file is created in Sharepoint it automatically copies to the Data Lake/Azure Blob?
That's in fact not an option for the triggers. You can only use data lake folders with that purpose. You can run periodically the proccess to move a file, and add a trigger in the destination folder to process it later on.
@@AdityaSingh-uw9df It's possible with a Logic App/Power Automate Premium but both these "solutions" have strict limits so basically useless for processing larger files (100,000 lines or more). Also there's a reason why SharePoint isn't natively connected to Data Factory as again it has limitations, simply not designed for big data. Ended up ditching Data Factory and using Apache Airflow.
@@shashidhar9510 you must be doing something wrong, I'm exporting excel documents from SharePoint Online using Binary dataset with Hppt linked service as source and Binary dataset with datalake linked service as destination
Same with me .. It doesnt work for xlsx files.. file content is shown in xml format when you download $value file in notepad++ @shashi dhar .. were you able to resolve the issue ?
Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials, please check your activity settingsRequest URL. But while cliking on the url shown in output , i get my csv file correctly. The sharepoint I am trying to access is in a different tenant. Will this not work if the tenants are different?
@@antonyaswin1176 No, in my scenario the issue was because the share point portal and Azure was in different tenants. If they are in same tenant, you should not have any issues
I keep getting this error; "Input Payload is invalid, validation result - '[\"Connector or activity name: Datalake_LSb00ee7af200d441da59965050eb96f7d, connector or activity type: AzureBlobFS, error: The domain of this endpoint is not in allow list. Original endpoint: does any one faced it?
Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials, please check your activity settingsRequest URL. But while cliking on the url shown in output , i get my csv file correctly. What might be the issue
When you click the link is your account (the one you logged with in your browser) the one accesing the file. When you do the http request on Data Factory is the created API of your tenant the one accesing, so I guess the permissions are not correctly granted.
Get this error on last step: ErrorCode=HttpRequestFailedWithClientError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with client error, status code 411 LengthRequired, please check your activity settings.
Hi , Thanks a lot for detailed Explanation. Does the process work for Excel file too ? I am getting an error with Excel files Not able to access the worksheets in the Excel file. Can you please help me Thanks?
Hi Raghu, it should work with any kind of file. The download will work as a binary and you stablish the file extension when saving it. Are you sure you are assigning the right extension?
Thank You for the Video, I've followed all the steps, but I still get the following error. "ErrorCode=HttpRequestFailedWithClientError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with client error, status code 403 Forbidden, please check your activity settings. If you configured a baseUrl that includes path, please make sure it ends with '/'
HI, Thank You for the Video. I followed all the steps but still I am getting below Error. "Operation on target Copy from Share Point failed: ErrorCode=HttpRequestFailedWithClientError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with client error, status code 403 Forbidden, please check your activity settings. If you configured a baseUrl that includes path, please make sure it ends with '/'." Please help on this.
My pipeline fails with this error: The remote server returned an error: (403) Forbidden. Could you please suggest what might be the issue. I have followed all of your steps
@@gastondemundo9822 no I could not resolve it...I did exactly the same but I still get the error 403..apart from providing access to the tenant id of the azure app that we created, is there any other permission or access we need to give?
@@shantanu69073 I was finally able to do it! What worked for me in my case was to use the other xml that the document provides in the part of giving permissions to the sharepoint application. Now I can sleep in peace
I followed all the steps and i have the following error : ErrorCode=HttpRequestFailedWithUnauthorizedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials, please check your activity settings. Request URL: help my plis
Hi Sir , I am getting the below error for a different sharepoint site other than same tenant.(It worked earlier for the same tenant) ErrorCode=HttpRequestFailedWithUnauthorizedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials, please check your activity settings.
Javibi very nice work ! thanks for the video . I did it all but i have an error 403 in the copy data activity from the source. The web token is generate fine, I gave permissions to the application to sharepoint but i dont know what can be. I am stuck. any idea?
I would suggest you to try to replicate the same call from Postman, just to confirm if the permission are not correctly set on Sharepoint or if you are not properly building the call in data factory
@gaston : Did you find the solution for this problem. I'm also facing same issue. Able to generate token but copy activity is failing due to "Http request failed with status code 401 Unauthorized " Error
Excellent video! Thanks a lot! I was able to extract a csv file from a sharepoint online and sink it to an Azure Blob container. However, I'm getting a � wherever there's a special character such as "ç" or "â". I've checked the file downloaded as $value from the Base URL and the special characters are there, but when ADF extracts it and sink it to the Blob, I get these �. Do you know how I can manage that?
@@javibi Found the problem. I downloaded the CSV file saved by ADF, opened it in Notepad and verified that the encoding is ANSI (WINDOWS-1252), not UTF-8 as I imagined. When importing this CSV into ADF, I selected this encoding instead and the problem was solved! Thank you very much for your attention!
can we try this with .xlsx file. I done with .xlsx file with utf-8 encoding, but still i'm finding � like characters when i open in notepad. Is there any way to this ?
@@javibi when i open this $value file after downloading for the .xlsx file i'm seeing like PK ! ýÉ7¯ ™ ×[Content_Types].xml but when i try with .csv file, i can see the content in the $value file. Why is it happening, i hv also checked the encoding
If it's an xlsx file. You need to open it with excel, not as XML or CSV. The only way to see it properly is with excel, if not you are going to see weird characters
Maybe you have any special character on the path? Like a quote ('). If you have access to the file, paste the url on your browser and check if the file is being downloaded. If not, it' s not properly formed
Hi! Thank you for this! It's very useful. I'm currently receiving an error on my pipeline ErrorCode=HttpRequestFailedWithClientError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with client error, status code 403 Forbidden, please check your activity settings. If you configured a baseUrl that includes path, please make sure it ends with '/'. Request URL: 'Type=System.Net.WebException,Message=The remote server returned an error: (403) Forbidden.,Source=System,'"
I would suggest you all with this kind of errors to do the same calls using postman. This way you will check if the issue is somewhere on your pipeline or if the permissions are not correctly granted
@@ginodejesus5698 I also got the same error. The issue was, App permission has not granted properly. Please check the steps of granting SharePoint Online site permission to your registered application.
Extremely useful and explained much better than any other article that I've come across on getting this setup - thanks for saving me hair loss!
Thanks for the feedback, happy to help
This video helped create a successful ADF pipeline to pull a file from sharepoint site . I just followed the steps mentioned in your video and it works . Thanks for posting this video
Thanks for the feedback!
@@javibi I am trying to extract the files from sharepoint but need your help in one flow. Can you please help me with contact so that I can connect.
Straight to the point, no bs. Thanks for the tutorial.
Thanks!
You are amazing!!!
If you are doing an xlsx file, instead of Binary use xlsx file type and you can copy right into SQL rather than having to drop into data lake or blob storage first.
I don't think you can do that, but give it a try and let us know if it's possible.
Anyway it's a good practice to keep an archive of the data you are processing, therefore, keeping a copy should be the best approach in most of the cases.
Incredibly well explained and walked through
Thank you so much!
Thanks for the feedback
Exactly what I needed and nothing more. This was perfect. Tnx a lot!
Thanks for the feedback!
Where are you creating the Data Lake at 9:48? Is this from another video?
Data Lake creation is not covered here, but there are plenty of content of how to create one out there
@@javibi Thanks I'll look!👍
supper clear! Thanks for your time and effort!
Thank you!
This is a useful tips but I have question, if you have multiple files in SharePoint document folder how do you read the folder metadata to get ChildItem to iterate in the ForEach activity since metadata activity has no connector for SharePoint?
Sorry, but that's not possible on SharePoint. I couldn't find a way to do that.
Hi Javibi,I'm getting an error after 4:34 when I click on "Trust," which says, "Sorry, you don't have access." Could this be because I don’t have access to the SharePoint site, or should I inform the Azure admin to grant the necessary API permissions for the app? I see that the permissions are currently "Not granted."
Please advise.
Yes, seems so. Usually this part of the task is responsibility of IT o SharePoint admins, so maybe they need to do that for you
Simply superb 👌 👏 👍
Thanks!
Very good explaination!!!Thank you!
Thanks!!
Great tutorial! Wanted to know if there is a way to use this to pull down the entire documents library?
No, I don't think so. File by file
Thank you so much. It really helped me! Now, i need to do it with Microsoft list.
Thanks!
Thanks for the great video.
Can I also copy excel stored in sharepoint library into SQL database using same method?
Sure, in two steps atl least. SharePoint to data lake, data lake to database
Good video thank you. Can you also use this approach to copy data to sharepoint?
Not sure but I would say the answer is no. I would recommend Logic Apps for that task. Then you can call them from your pipeline
Can this work the other way round? Output/Sink to SharePoint online and replace excel file if exists.
Never tried that. Maybe there is an API you can try for this.
You have done a great Job mate. Well done! I liked the way you explained it
Thanks!
Really helpful video. Thanks a lot.
Thanks!
This video helped me a lot, Thanks. Just been courious, do you have one version with multiples CSV/XLSX Files?
No, but I think you need to create a process for each of the files. Don't think in SharePoint source as a blob storage where you can use the metadata activity and loop over several files.
So well explained. Well done man. Thank you.
Thank you!!
How can you copy multiple files in a particualr sharepoint folder
I don't think you can push them with ADF
Is there a way to to the process if the SharePoint and AD not on the same tenant?
Not really sure. I guess the SharePoint id and credential should be enough to access from any service. Just try it out and let me know.
I think they need to be same tenant . when you create a app in AAD, you need to mention the same tenant where your sharepoint site is available . usually they have dev tenant and prod tenant
Great tutorial! Is there a way to make the filename dynamic for xlsx? So if we have filename.xlsx, how do we do it for *.xlsx or %file%.xlsx to capture anything named file or all files in the directory?
You can build the url dynamically, but I don't think you can't get dynamically the list of files in a folder.
Using the above way, I can connect to SP and pull an Excel file to be stored as an Azure blob. I then want the blob to be a dataset in my dataflow. Can I have the web token+copy data pipeline run periodically? (As the same Excel file in Sharepoint may be updated periodically?)
Yes, after the copy activity to get the file, you can have a data flow activity in the pipeline to transform the file as you need.
@@javibi Thanks! Managed to get it working. Can this work in the reverse way? Copying from Azure Blob to Sharepoint?
Hello ! thank youu for the video.. I suppose we can do reverse the same way? by switching sink and source in copy data? from datalake to sharepoint? if not, why? thank you again 👍
I don't think that there's a way to do that. The idea of data platforms is to integrate the data in them, not to move it away.
Amazing, very helpful video. This is what I was expecting
Thanks a lot!
I want pull the secrent form Keyvault how can I do that
Look I at my videos, I have another one exactly for that
Hi, this is a good video. Can you make a video on how to extract data from Sharepoint Online List instead of file using ADF Pipeline.
I think that there's a conector for this in data factory, isn't it? Anyway, I'll take note of your comment
So useful! Excuse me, i have a question, can redirect data after use ADF to Azure datalake gen 2. Thanks!
Yes, absolutely
How I copy the last modified file from SharePoint using the adf
I didn't get that far but I would say it's not possible. There is an end point that return the content of the Sharepoint folder on XML format that it's a bit complex and not very well formated. I would give it a try
@@javibi can you say how we can do it by endpoint
@@mehersandeepattuluri8850 learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-folders-and-files-with-rest
Hi Sir,
Thanks for the video. How to capture the info of modified by and Last updated time from share point to maintain a audit log.
I don't think SharePoint allows that. There's an endpoint that returns metadata from a specific folder, but I don't think that will solve your issue
What is the use of Access Token which you are generating from WEB activity. As you are not using in next process. Please help
Yes, it's been used in the next Copy activity in the header. See 9:10 onwards
Outstanding!!! Thank you!!!
Thanks!
Hi, why it don't have a HTTP source??
Probably you are looking at the wrong place. Check it again
Can I also upload the data to Sharepoint?
Didn't find an API to do so, but maybe there's a way
To copy file from sharepoint , can we use excel dataset instead of binary . In this video it is explained as binary format. In copy activity configuration, if i set source a binary dataset then sink has to binary dataset as well. Can someone help how do i achieve the requirement to copy a excel sheet(a.xlsx) from a SharePoint website and put that in to azure storage account container as csv file
I think it should be binary in both, but give it a try with Excel
@javibi, What an excellent video!!!. This process also works for MS Fabric?
In my case, I will copy txt data from SharePoint to the bronze layer in MS Fabric.
I guess it works, but I would say that One Lake at some point should replace SharePoint as a file storage
How can we extract the latest CSV file (latest timestamp) from the SharePoint?
I don't think that's possible. There's not an option to get metadata in Sharepoint
@@javibi is there any other way to do it ?
@Aditya Singh there is an endpoint to list content in a folder, but if I remember well, it returns a XML not really well formated with high complexity to extract data from it, but give it a try.
Is it possible to give the app permission to all sharepoint sites in your tenant? Instead of 1 by 1
I'm not expert in SharePoint, but I would say it's not possible.
How to provide file name dynamically in base url?
Just build the url dynamically and add on it the file name
Sharepoint and adf both under same tenant id as per your video? does it work if sharepoint under different tenant id
I think it needs to be from the same tenant.
That was EXACTLY what I needed, awesome !!
Cool!
Thank you so much for the very explanatory video with great details. Is it possible to make SharePoint as Destination and Azure BLOB as a source ? Do we need any extra permission to use SharePoint as sink
Hi, I don't think you can do that with Data Factory directly, but I think you can do that with a Logic App. Then you can call the Logic App from Data Factory
@@javibi I came across a limitation that Logic App only supports files up to 1GB . Our project requirement is store files up to 2GB from Azure BLOB to SharePoint . What is the best method I can explore to achieve this requirement ? Any idea ?
Difficult one. With files of that size I would recommend a Data Lake. Maybe another option is a SFTP. Maybe also using s VM with a folder synched with SharePoint and placing the file in it? Not sure right now if that's even allowed in Data Factory.
How to do it for multiple files like entire sp library
I don't think that is feasible in a dynamic way. Files need to be hard coded 1 by 1
thanks, very informative.
Thanks
Hi javibi , after giving appid while lookuping app name not coming, iam using different adf account and different SharePoint
I think both should be in the same tenant
Excellent!
Thanks!
Good job! Great video, man!
Thanks @Diego
Hi,
It was a great video. I wanted to know if I am copying data from sharepoint to ADLS and if I delete one file from sharepoint then I want to delete the same file from ADLS too. How can I implement this?
I don't think that there's a way to do that.
I'm copying .xlsx file from sharepoint. But the files are copied to blob in different (unreadable) format. I guess it is in xml format. Please help on this
Maybe you are using xml dataset as destination? If thats the case use binary one.
Same with me ..
It doesnt work for xlsx files.. file content is shown in xml format when you download $value file in notepad++
@vasantha .. were you able to resolve the issue ?
I'm exporting xlsx and works fine
@@european.destination , were you able to access the worksheet names in the dataset? I am receiving error 403
Thank you for the video! Great great great job!
Thanks!
Error during server relative url, says specified value not supported
Probably you have something wrong somewhere
hello there, the video is such usefull, have you posted any other video copying data from an excel file?
It doesn't matter if you want to extract a CSV, and Excel file or whatever other type. The process is exactly the same
Can I iterate over multiple files of a folder in the SharePoint site? Is there any way?
There's an endpoint to get the list of files in a Sharepoint folder but, if I remember right, but the output is an xml with a not really nice format. Give it a try!
HI!
thanks for sharing walkthrough.
I'm getting the following error:
ErrorCode=UserErrorInvalidCredentialToReadHttpFile,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The credential to read http file is invalid.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Net.WebException,Message=The remote server returned an error: (401) Unauthorized.,Source=System,'
Were tou able to solve this? Seems that the password used to get the token is not a valid one
@@javibi i have the same issue. If its the credentials, wouldn't that fail with the Link Service test?
Is it requires Microsoft Grap API Permission?
No, nothing to do with that.
how do you build the data lake linked service?
Just in the manage area go to linked services and choose the data lale one
Nice video! Thank you
Thanks!!
Can it be dynamic?or if we want to move all files inside a folder then how to create relative URL?
Hi @Dilip, there is another endpoint to list the Sharepoint content but, if I dont remember wrong, the output is an xml and it's not very good formated to create a dynamic call. Anyway, I would suggest you to give it a try.
I am trying to copy about 500 gb of data from sharepoint to Azure Files. Is it possible to copy an entire FIle structure? there are a lot of folders and files within the root folder. Please help me
Hi, don't think that's possible, but give it a try pointing to a folder instead of to a file.
I think you will need to go file by file.
Is it possible from adls to share point?
I didn't try that but I would say that the answer is no. Maybe you can use a Logip App for this calling it from adf
Thanks for sharing, I have tried the same steps to get data from a SharePoint list and it keeps saying Unauthorized, I already checked the URL passed to the copy activity returns values. I did not see access to SharePoint was given when the app was registered in Azure App Registration. Can this be the reason?
This method is to export files from a SharePoint site. For lists there is a connector that you can use
@@javibi thanks for responding, I am having a really hard time setting up the linked service, I registered the app in Azure, gave full permissions, and I am using the client id and secret but no success. Have you done this before? Any ideas? Thanks!
Yes I did it a couple of times. I'll suggest you to test the process on Postman with hardcoded values to check if it works. This way you confirm that the client Is and setting are working fine. Probably it is just a small thing you missed
I was finally able to connect using Graph API. Thank you!
I am getting below error while extracting token key.
@ramakrishnaalavala5280, you forgot the error
@@javibi ,error code is
"error": "invalid_request",
"error_description": "AADSTS50169: The realm '9756717f-02ac-4b97-bc8e-0257aef812dc' is not a configured realm of the current service namespace.
Trace ID: 2588bbc5-1c0b-4203-96a7-a68374fb5600
Correlation ID: 31f01417-7f62-4db5-949f-a8cef0825565
Timestamp: 2023-08-24 11:19:01Z",
"error_codes": [
50169
],
Its complaining about the Id 9756... Probably you are not doing the call properly
How to extract items from sharepoint to ADF?
Follow the steps
I am getting below error while running Get Token activity
{"error":"invalid_client","error_description":"AADSTS7000215: Invalid client secret provided. Ensure the secret being sent in the request is the client secret value, not the client secret ID, for a secret added to app 'c602b95b-6dba-4e30-9eb0-d52d33d2920c'.
Trace ID: 7ca2f6a7-d8ce-4181-be1a-7dc05e030800
Correlation ID: 8d5c75ab-9ff0-48fd-aca3-00ed2e80e9ae
Timestamp: 2023-06-08 00:56:37Z","error_codes":[7000215],"timestamp":"2023-06-08 00:56:37Z","trace_id":"7ca2f6a7-d8ce-4181-be1a-7dc05e030800","correlation_id":"8d5c75ab-9ff0-48fd-aca3-00ed2e80e9ae","error_uri":"accounts.accesscontrol.windows.net/error?code=7000215"}
Seems that the parameter you are passing is not correct
I have the base url copied in a new browser and it throws me a XML file does not appear error. Does this mean that the SharePoint permissions were not granted properly?
The base url to download the file applied on you browser should download the file if you are logged with your account and you have permissions if I remember right.
@@javibi I mean the permissions to the SharePoint was granted by someone else. But I'm creating the add pipeline.. it should work anyway right? I mean the site url has been given access too so if I use the same procedure it should be working i suppose.
If you dont have access to the Sharepoint file I guess and xml should come with an error. The only way to test if the permissions are granted properly to de appid is to use it through Data factory or through a Postman call
@@javibi I'm just a member to that site not an owner so do I need the role of an owner to do this. Or even with a member role can I follow the same procedure as you and proceed?
Just FYI, I can access the file if I go to SharePoint.
If you are able to access the file through the Sharepoint portal, typing the same url you should use to download the file on Data Factory should download a file on your browser
I have created an App id , but when i search it in share point , I am unable to see the App Name while i use look up option. Is it related to access issue
It should be accessible. Both are in the same tenant right?
Thanks for this video! great work!
Can you explain how you can setup a pipeline to use a trigger so when a file is created in Sharepoint it automatically copies to the Data Lake/Azure Blob?
That's in fact not an option for the triggers. You can only use data lake folders with that purpose. You can run periodically the proccess to move a file, and add a trigger in the destination folder to process it later on.
it can be done using azure logic app
You were able to do it ?
@@AdityaSingh-uw9df It's possible with a Logic App/Power Automate Premium but both these "solutions" have strict limits so basically useless for processing larger files (100,000 lines or more). Also there's a reason why SharePoint isn't natively connected to Data Factory as again it has limitations, simply not designed for big data. Ended up ditching Data Factory and using Apache Airflow.
It doesnt work for Excel file, content is binary and cannot convert to .xlsx in blob .
Convert it to binary also on destination, and name them with xlsx extension. This should work.
@@javibi Tried all options didnt work, could you make video on that too. pls
@@shashidhar9510 you must be doing something wrong, I'm exporting excel documents from SharePoint Online using Binary dataset with Hppt linked service as source and Binary dataset with datalake linked service as destination
Same with me ..
It doesnt work for xlsx files.. file content is shown in xml format when you download $value file in notepad++
@shashi dhar .. were you able to resolve the issue ?
Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials, please check your activity settingsRequest URL. But while cliking on the url shown in output , i get my csv file correctly. The sharepoint I am trying to access is in a different tenant. Will this not work if the tenants are different?
Yes, I would say so.
@jayachandranappavoo5169 : Did it get resolved ? I am facing same issue as well.
@@antonyaswin1176 No, in my scenario the issue was because the share point portal and Azure was in different tenants. If they are in same tenant, you should not have any issues
@@jayachandranappavoo5169 how you resolved this issue
I keep getting this error;
"Input Payload is invalid, validation result - '[\"Connector or activity name: Datalake_LSb00ee7af200d441da59965050eb96f7d, connector or activity type: AzureBlobFS, error: The domain of this endpoint is not in allow list. Original endpoint:
does any one faced it?
It's not clear to me what this issue is
Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials, please check your activity settingsRequest URL. But while cliking on the url shown in output , i get my csv file correctly. What might be the issue
When you click the link is your account (the one you logged with in your browser) the one accesing the file. When you do the http request on Data Factory is the created API of your tenant the one accesing, so I guess the permissions are not correctly granted.
@sharavananp5570 : Did it get resolved . I am facing the same issue. It works for same tenant but for different tenant , it throws this error.
very useful
Thanks
Get this error on last step: ErrorCode=HttpRequestFailedWithClientError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with client error, status code 411 LengthRequired, please check your activity settings.
Are you adding the header? 411 usually raise when you are missing it
@@javibi Found the error . Thanks!
Hi , Thanks a lot for detailed Explanation. Does the process work for Excel file too ? I am getting an error with Excel files
Not able to access the worksheets in the Excel file. Can you please help me Thanks?
Hi Raghu, it should work with any kind of file. The download will work as a binary and you stablish the file extension when saving it. Are you sure you are assigning the right extension?
Hello sir
I followed all the steps and still getting 401 error
web activity is fine and able to get to token
please suggest
Difficult to say, you must be missing something for sure. Maybe you didn't build the connection string properly.
@@javibi Thanks I find it it was (comma) after the expression that was causing the issue
Thank You for the Video,
I've followed all the steps, but I still get the following error.
"ErrorCode=HttpRequestFailedWithClientError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with client error, status code 403 Forbidden, please check your activity settings. If you configured a baseUrl that includes path, please make sure it ends with '/'
Hi
Did you get the resolution for the above error??
Maybe permissions were not properly granted?
HI,
Thank You for the Video. I followed all the steps but still I am getting below Error.
"Operation on target Copy from Share Point failed: ErrorCode=HttpRequestFailedWithClientError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with client error, status code 403 Forbidden, please check your activity settings. If you configured a baseUrl that includes path, please make sure it ends with '/'."
Please help on this.
Hi
Did you get the resolution for the above error??
Maybe permissions were not properly granted?
Graciela!
A ti!
My pipeline fails with this error: The remote server returned an error: (403) Forbidden. Could you please suggest what might be the issue. I have followed all of your steps
There must be something not properly done on the permissions. Seems like the app is not allowed.
@@javibi is there a way to check if the sharepoint team site is allowing the adf to read the data...any way to check the permission
could you resolve it? i did exactly all in the video and i get the same error 403 too
@@gastondemundo9822 no I could not resolve it...I did exactly the same but I still get the error 403..apart from providing access to the tenant id of the azure app that we created, is there any other permission or access we need to give?
@@shantanu69073 I was finally able to do it! What worked for me in my case was to use the other xml that the document provides in the part of giving permissions to the sharepoint application. Now I can sleep in peace
I followed all the steps and i have the following error : ErrorCode=HttpRequestFailedWithUnauthorizedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials, please check your activity settings. Request URL: help my plis
@javibi
Were you able to make it run @jeancid7702?
Hi Sir , I am getting the below error for a different sharepoint site other than same tenant.(It worked earlier for the same tenant)
ErrorCode=HttpRequestFailedWithUnauthorizedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials, please check your activity settings.
Maybe the Application on the Azure portal lost the privileges
Javibi very nice work ! thanks for the video . I did it all but i have an error 403 in the copy data activity from the source. The web token is generate fine,
I gave permissions to the application to sharepoint but i dont know what can be. I am stuck. any idea?
I would suggest you to try to replicate the same call from Postman, just to confirm if the permission are not correctly set on Sharepoint or if you are not properly building the call in data factory
@gaston : Did you find the solution for this problem. I'm also facing same issue. Able to generate token but copy activity is failing due to "Http request failed with status code 401 Unauthorized " Error
As per the Lastest update by Microsoft. This process of http is no more acceptable
Maybe, with the Fabric approach is SharePoint who will be replaced with One Lake
Excellent video! Thanks a lot! I was able to extract a csv file from a sharepoint online and sink it to an Azure Blob container. However, I'm getting a � wherever there's a special character such as "ç" or "â". I've checked the file downloaded as $value from the Base URL and the special characters are there, but when ADF extracts it and sink it to the Blob, I get these �. Do you know how I can manage that?
Maybe I'm wrong, but I think the issues are not in the extraction but in the read. Probably you are using a wrong encoding for the CSV?
@@javibi Found the problem. I downloaded the CSV file saved by ADF, opened it in Notepad and verified that the encoding is ANSI (WINDOWS-1252), not UTF-8 as I imagined. When importing this CSV into ADF, I selected this encoding instead and the problem was solved! Thank you very much for your attention!
can we try this with .xlsx file. I done with .xlsx file with utf-8 encoding, but still i'm finding � like characters when i open in notepad. Is there any way to this ?
With Binary option for the destination this shouldn't be an issue. Try to then transform the file into parquet with the right encoding
@@javibi when i open this $value file after downloading for the .xlsx file i'm seeing like PK ! ýÉ7¯ ™ ×[Content_Types].xml but when i try with .csv file, i can see the content in the $value file. Why is it happening, i hv also checked the encoding
If it's an xlsx file. You need to open it with excel, not as XML or CSV. The only way to see it properly is with excel, if not you are going to see weird characters
I cant get the getFileByServerRelativeURL link to work. The expression is not valid.
Maybe you have any special character on the path? Like a quote ('). If you have access to the file, paste the url on your browser and check if the file is being downloaded. If not, it' s not properly formed
@@javibi The file is downloaded, but the data is not readable. Its in diff format. Any guess
Hi! Thank you for this! It's very useful. I'm currently receiving an error on my pipeline
ErrorCode=HttpRequestFailedWithClientError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with client error, status code 403 Forbidden, please check your activity settings. If you configured a baseUrl that includes path, please make sure it ends with '/'.
Request URL: 'Type=System.Net.WebException,Message=The remote server returned an error: (403) Forbidden.,Source=System,'"
Hi, I get the same error, were you able to solve it?
I would suggest you all with this kind of errors to do the same calls using postman. This way you will check if the issue is somewhere on your pipeline or if the permissions are not correctly granted
@@josuedavidvazquezcalvillo2044 Not yet, even setting the permission with FullControl
@@javibi I'm testing using Postman, I can get an access token but reveing 403 Unauthorize when calling web api.
@@ginodejesus5698 I also got the same error. The issue was, App permission has not granted properly. Please check the steps of granting SharePoint Online site permission to your registered application.
Hi how to get multiple files from sharepoint site folder
Using multiple activities for each of them, but you can't dynamically get the names of the files. They should be hardcoded