Problem: You have a number of Excel files in a SharePoint library and you want to load the data from those files into your database in Azure. However, Azure Data Factory does not support SharePoint as a data source. What are your options to get the data out of SharePoint?

Solution: While Azure Data Factory does not directly support SharePoint as a data source, there are several alternative options you can explore:

1. Code a solution using .NET, Python, or PowerShell and automate it using Azure Runbooks.
2. Build an Integration Services package and use an Azure-SSIS runtime environment to schedule the package inside Azure Data Factory.
3. Use Azure Logic Apps to fetch the files from SharePoint, dump them into Azure Blob Storage, and then copy the data into the database using the Copy Activity in Azure Data Factory.

In this blog post, we will focus on the third option: using Azure Logic Apps to transfer data from SharePoint to Azure SQL Database.

Creating a Logic App

To get started, follow these steps:

1. In the Azure Portal, click on “Create a Resource” and search for “Logic Apps”.
2. Click on the “Create” button to add a new Logic App.
3. Choose a name, subscription, resource group, and location for your Logic App.
4. Once your Logic App is created, the designer will open. You can start with a blank workflow or choose from existing templates.
5. Choose a trigger for your Logic App. In this example, we’ll use an HTTP request trigger to run the app on demand.
6. Add a step to the workflow that will be executed once the trigger is called.
7. Search for SharePoint and choose the “List Folder” action. This action will list all the items in the SharePoint document library.
8. Sign into your SharePoint environment to connect to the desired library.
9. Add a new subsequent step and choose SharePoint again as the connector.
10. Select the “Get File Content” action and use the identifiers of all the files in the folder obtained from the previous step.
11. The Logic Apps designer will automatically wrap the “Get File Content” step in a loop to handle multiple files.
12. Add another action to write the contents of the file to Azure Blob Storage.
13. Configure the action to create the blob, using dynamic content to use the same file name and file content.

Testing the Logic App

Once your Logic App is finished, save your work and run the app from the designer. The Logic App will check if there are files in the SharePoint folder and then copy them to the Azure Blob container. You can verify the presence of the Excel file in the blob container.

Conclusion

In this blog post, we have demonstrated how you can easily create a workflow with Azure Logic Apps to transfer data from SharePoint to Azure SQL Database. By leveraging Logic Apps, you can automate the integration between various services without writing a single line of code. Logic Apps offer a wide range of connectors, including SharePoint, to handle different scenarios. Give it a try and simplify your data transfer process!