I have done a lot of BizTalk-SharePoint Integration in the past and ran into a situation recently that surprised me. There wasn’t an easily identifiable resolution online so I have decided to document this for the benefit of others.
We have a process that requires a user to approve a financial summary document in SharePoint. Once the document has been approved, BizTalk will then fetch the details behind those financial transactions, from another source system, and send them to SAP.
In the past I have leveraged SharePoint views as a way for BizTalk to pickup messages from a SharePoint document library. The way to achieve this is to rely upon meta data that can be populated within a SharePoint document library column.
Adding a custom column to a document is very simple. Under the library tab we will discover the Create Column label. We can simply click this button and then add a column and related properties as required.
With our custom column created, we can now create a view for BizTalk to “watch”. In our example we were dealing with an approval workflow. We can create our custom column called Status and then when BizTalk initially publishes this financial summary document(for users to approve), we can use the SharePoint adapter to populate this column with a value of Pending. After a user has reviewed the document, that Status value can be changed to Approved.
Since we don’t want BizTalk to move Pending documents we will create a view that will only show Approved documents. To create a custom View we can once again click on the Library tab and then click on Create View
For our purposes a Standard View will be sufficient.
We need to provide a View Name and can also indicate where we want this column to be positioned.
Tip – In my experience I have experienced odd behavior with spaces in the name of SharePoint entities. My advice is to avoid spaces in names where possible.
Lastly, since we only want Approved documents to show up in this field we need to add a filter.
Within our filter we want to Show items only when the following is true:
Status is equal to Approved
We can now save our view and test it. To test it we will upload two documents. One will have the Status of Approved and the other will have a Status of Pending. When we click on All Documents we should see both documents.
When we click on our view for BizTalk, which in this case is called BizTalkMoveView we will only see our Approved document.
From a SharePoint perspective we are good and we can now create our SharePoint Receive Location in BizTalk. For the purposes of this blog post I am using a Send Port Subscription; I will receive the message from SharePoint and then send it to a File folder.
In our BizTalk Receive Location configuration we are going to use the Client OM which in this case is the SharePoint Client Object API. This allows us to communicate with SharePoint without having to install any agents on a SharePoint Server.
We also need to configure our SharePoint Site URL, Source Document Library URL and View Name
When we enable our Send Port and Receive Location we should receive 1 file in our File Folder right? WRONG! Both files were picked up and moved to our file folder even though we have a View enabled.
If we go back to SharePoint we will discover both documents are gone.
The issue is that for some reason, BizTalk 2013 is not using/respecting the View Name property that is available in the Receive Location Configuration.
The resolution is to install BizTalk 2013 CU 2. The download and more details about CU2 can be found here.
Before you install, the recommended approach from Microsoft is:
- Stop all host instances
- Stop SQL Server Agent which is responsible for running the internal BizTalk jobs
- Perform a Database Backup
Running the CU2 exe is pretty straight forward and only takes a couple minutes. I wasn't prompted for a reboot but decided to go that route regardless.
After applying the CU, I uploaded two documents again. One had a Status of Approved while the other had a Status of Pending.
Our BizTalkMoveView is also displaying our file correctly
When we enable our Receive Location we will discover that only our Approved file has moved.
Our document that was in a Pending state remains in SharePoint as expected.
BizTalk 2013 was the first version that had support for the SharePoint Client Object model. So I am not sure if this bug only impacts when you are using the Client OM within the BizTalk Receive Location. I do know that in previous versions of BizTalk that this was not an issue. However those versions of BizTalk relied upon the SharePoint Adapter Service being installed on the remote SharePoint Server. Using the Client OM is the way to go as it also allows you to communicate with SharePoint Online/Office365.