Transfer Files from SharePoint To Blob Storage with Azure Logic Apps


By:   |   Updated: 2019-02-05   |   Comments (4)   |   Related: More > Azure

Problem

I have a number of Excel files in a SharePoint library. I want to load the data from those files into my database in Azure. I tried using Azure Data Factory, but SharePoint isn't listed as one of the sources. What are my options to get the data out of SharePoint?

Solution

At the moment, SharePoint is not supported as a data source in Azure Data Factory (ADF), the cloud-based data integration service by Microsoft. It is not listed as a supported data store/format for the Copy Activity, nor is it listed as one of the possible connectors. If we want to read the data from the Excel files and write it to an Azure SQL database, we need to explore other options. Luckily, there are many:

In this tip, we'll take a look at the last option. Logic Apps allow you to create serverless workflows that automate the integration between various services without writing a single line of code. Luckily, Logic Apps can connect to SharePoint, and many other sources as well, such as BizTalk, Office 365, Box, OneDrive, Power BI, Teradata and so on. The list is over 200 items long! The example in this tip uses SharePoint as a source, but Logic Apps can handle many different scenarios.

Creating a Logic App

The goal is to read data from an Excel file stored in a SharePoint document library. The sample data looks like this (it's retrieved from the employee dimension of the AdventureWorks data warehouse):

customer alternate key

In the Azure Portal, click on Create a Resource.

microsoft azure

Search for Logic Apps.

logic app

Click on the Create button to add a new Logic App.

logic app

Choose a name, a subscription, a resource group and a location.

logic app designer

When you go to the newly created Logic App, the designer will open. You can start with a blank workflow, or you can choose between any of the existing templates. If you start with a blank flow, you need to choose a trigger. The trigger will specify when the Logic App will run. In this example, we'll choose a HTTP request, so we can run the app on demand.

logic app designer

After choosing the trigger, the design canvas will be opened, with the trigger added to the top:

logic apps designer

Now we need to add a step to the workflow, which will be executed once the trigger has been called. Simply click on New step. A step will be added and you need to choose the action the step will perform:

choose an action

Search for SharePoint and in the resulting list, choose the List Folder action. This action will list all of the items in the document library. We can then use this output to loop over the items and read them their contents.

sharepoint

The next step is to sign into your SharePoint environment. If you have an on-premises SharePoint, you can connect through a data gateway (more info can be found in the tip Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1).

sharepoint

A pop-up will come where you can enter your credentials. It's possible this pop-up is blocked in some browsers. After signing in, you'll get the following window where you can configure your action:

list folder

If you click the first dropdown, you'll get a list of SharePoint sites you can connect to. Once you select one, you can click on the folder icon to browse to the desired library:

list folder sharepoint

Click on the arrows on the right to go to a subfolder, or on the folder itself to select it. Click on New step to add a new subsequent step. In the new step, choose SharePoint again as the connector. Then, select Get File Content. In the File Identifier, we want to use the identifiers of all the files in the folder, found by the List Folder step. To do this, select Add dynamic content. This allows you to use values/objects returned by previous steps.

get file content

In the list, select Id (the unique id of the file). The Logic Apps designer is smart enough to detect that you can actually have multiple files in the folder, so it's going to wrap the Get File Content step in a For each loop.

get file content

Don't forget to select a SharePoint site as well, which obviously needs to be the same site as in the List Folder step. The final step will write the contents of the file to Azure Blob storage (configuration of blob storage is out of scope for this tip, but examples can be found in the tips Customized Setup for the Azure-SSIS Integration Runtime or Copying SQL Server Backup Files to Azure Blob Storage with AzCopy). Click on Add an action. In the action screen, search for blob, choose Azure Blob Storage and then select Create blob.

create blob

If you have multiple storage accounts, choose the one you need for your setup. Give the connection a name.

create blob

Now you can configure the action to create the blob. You can use dynamic content to use the same file name. For the blob content, you need to choose File Content from the dynamic content.

create blob

Testing the Logic App

The logic app is now finished. Click on the save icon to save your work. Once the app is saved, you can run it from the designer.

logic apps designer

The logic app will then run, check if there are files in the SharePoint folder and then copy them to the Azure Blob container.

list folder

In the blob container, we can verify the Excel file is present:

logic app

Conclusion

In this we showed how you can easily create a workflow with Azure Logic Apps that transfers data from SharePoint (there are many other connectors available as well) to Azure Blob Storage, without writing a single line of code. The app created in this tip can be called by an HTTP request, but there are other types of triggers as well, such as when a file arrives in a folder.

If you've worked with Microsoft Flow before, creating Logic Apps will be very familiar. In fact, Flow is built upon Logic Apps and they share the same designer and connectors.

Next Steps
  • Try it out yourself. You can try to automate some of your tasks or replace some old SSIS packages with native cloud functionality. You can also incorporate the logic app into an Azure Data Factory pipeline by using the HTTP request.
  • An interesting article comparing Flow, Logic Apps, Webjobs and Azure Functions.
  • You can find more Azure tips in this overview.


Last Updated: 2019-02-05


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, October 21, 2019 - 9:33:50 AM - Koen Verbeeck Back To Top

Hi edison,

does the 100MB error occur in SharePoint or in Logic Apps? If in SharePoint, you might need to speak with your admin to see if the restriction can be lifted. If in Logic Apps, I'm afraid there's not much you can do. Maybe split up the file if possible or compress it.

Koen


Sunday, October 20, 2019 - 11:55:18 PM - edison Back To Top

If the file is greater than 100MB, "get file content" activity will be failed due to the 100MB limit. Can you advise how to work around it?


Wednesday, February 13, 2019 - 9:16:29 AM - Koen Verbeeck Back To Top

Hi Kieran,

I assume so, since there is a "create file" SharePoint action.


Wednesday, February 13, 2019 - 7:18:25 AM - Kieran Kelly Back To Top

Can you do it the other way around, from Blob Storage to Sharepoint?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools