Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

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

Storage and High Availability Options for SQL Server in the Cloud

Free MSSQLTips Webinar: Storage and High Availability Options for SQL Server in the Cloud

This webinar will cover best practices for optimizing cloud storage and cost, how to leverage the cloud for disaster recovery, availability options and requirements for SQL Server and key factors to consider in your selection.


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?


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.


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).


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


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.


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?

Learn more about SQL Server tools