This post was originally published here
I have a scenario at work where we need to provide some simple syncronization between a SQL Azure table and a SharePoint Online Custom List. As a pre-requisite each morning before business users get into the office, we need to purge the contents from the SharePoint list and update it with today’s data + a 6 day forecast of future data.
I have integrated BizTalk with custom SharePoint Lists in the past, and even wrote about it in one of my books. It wasn’t a particularly good experience so I was interested in evaluating how Logic Apps would deal with custom lists.
One difference between BizTalk and Logic Apps, in this case, is that BIzTalk has a SharePoint Adapter but it will only interface with SharePoint Document Libraries. If you wanted to integrate BizTalk with SharePoint Custom lists you are likely going to do so with the Lists.asmx web service. While it is completely possible to use this custom web service approach, be prepared to spend a few hours (if you are lucky) getting everything working.
With Logic Apps, it is a very different experience. From the Logic Apps canvas you need to add a trigger to kick off your workflow. In my case, I used a Recurrence trigger that will run every day at 7:45 am. I can also kick this trigger off manually in the Azure Portal if I wish.
Next, I want to add an action and then search for SharePoint from the Microsoft managed APIs dropdown. After we do that, all available SharePoint Online operations will be displayed.
In my case, I want to purge all items, but there is no Delete List operation. Instead, I need to get all items in my list first, so that I can use the ID from each record to delete that item. In my scenario I expect 7 days * 24 hourly records (168 items) to be in my list at any given time so this load is not a concern.
With this situation in-mind, I will select the Get Items operation.
Once I have selected my Get Items operation, I need to establish my connection to my Office 365 subscription. (I will spare you from the password prompts)
With my connection established, I now need to provide the URL to my SharePoint List. I can also specify optional parameters that control the number of items returned.
I must say the experience in this dialog is a good one. I can click on the Site URL dropdown and all of the sites that I have access to will be in that list. Once I have selected my URL and then click on the List Name dropdown, I then see all the lists that I have access to on that site.
Next, I need to add another activity and this time I will select the Delete Item operation.
I have a similar experience in the Delete Item dialog that I had in the Get Items dialog. With my connection already established, I need to provide the same Site URL and the same List Name. What is different this time is I need to provide an ID for the list item that I would like to delete. In this case it will be an ID that is coming from my Get Items response.
You might be asking yourself – will how is that going to work for all of my items in my list? Don’t you need a for loop to iterate through the Get Items collection? The answer is Yes, but the Logic Apps team has made this very simple – they have done the heavy lifting for you. If you go into Code View you can see it there:
“foreach”: “@body(‘Get_items’)[‘value’]”,
“inputs”: {
“host”: {
“api”: {
“runtimeUrl”: “https://logic-apis-westus.azure-apim.net/apim/sharepointonline”
},
“connection”: {
“name”: “@parameters(‘$connections’)[‘sharepointonline’][‘connectionId’]”
}
},
“method”: “delete”,
“path”: “/datasets/@{encodeURIComponent(encodeURIComponent(string(‘https://SharePointsharepoint.com/sites/HOPOC/Bighorn/SitePages/Home.aspx’)))}/tables/@{encodeURIComponent(encodeURIComponent(string(‘c28b1ea2-e2a0-4faf-b7c2-3eerec21a8b’)))}/items/@{encodeURIComponent(string(item()[‘ID’]))}”
},
Awesome!
The end result is a Logic App that looks like this:
I can now my Logic App from the Azure Portal by clicking on Select Trigger and then recurrence.
I can follow my execution and dive into my Get Items and Delete Items calls. By inspecting my inbound and outbound traces I can see the exact payloads and HTTP Status codes from the underlying operations. In this case I was able to delete 300 items in 23 seconds. For each item in my collection, a Delete Item call is made.
Conclusion
It honestly took me about 10 minutes to figure this out. Part of the reason why I am writing about this is I know how long this would take with BizTalk. I anticipate it would take at least 3 hours to do this in BizTalk if it was your first time. So this isn’t a knock against BizTalk, as Logic Apps has been built, IMO, for these lightweight scenarios with little friction.
In the short term I think Microsoft Integration architects and developers will have many opportunities like this one where you can choose one tool or the other. For me, developer productivity needs to be part of that equation. Where the systems that you are integrating are hosted will also play a role. In this case, I am connecting to SharePoint Online and SQL Azure so it also doesn’t make sense, IMO, to route all of this info back on-premises only to go back up to the cloud.
We may also see a Hybrid approach where a BizTalk process can call out to a Logic App where you can take advantage of these new Logic App connectors. This was something that Microsoft discussed at 2016 Integrate Event in London.
In a future blog post we will talk about the other half of this solution which is how to create items in SharePoint Online from SQL Azure.