Monday, May 11, 2015

/nSoftware Powershell Adapter for BizTalk Server

 

In the past I have blogged about /n Software and their SFTP Adapter here and here.  Hard to believe one of those posts goes back to 2007. One thing that /nSoftware continues to do is add new adapters to their suite.  In this case it is the Powershell Adapter.

Can’t say that a Powershell Adapter previously was on my radar until a scenario was brought to me.  We have a very specialized piece of software that does “analysis” (I will leave at that for now).  This software is essentially a library that has been wrapped around an exe.  This exe will receive a series of parameters including a path to a file that it will use to perform its analysis on.

A suggestion was brought up about calling this exe using Powershell.  While I am sure we could call this from .Net the Powershell warranted some investigation.  So sure enough in a web search, /nSoftware comes up with an offering and sure enough we had it installed in all of our environments.

Since BizTalk is going to deliver the flat file used as an input to this process, I decided to check out the Powershell Adapter and allow BizTalk to orchestrate the entire process.  For the purpose of this blog post I will over-simplify the process and focus more on a POC than the original use case.

As part of the POC I am going to receive an xml file that represents our Parameter data.  We will then send this same message out through a Send Port that is bound to the /nSoftware Powershell adapter.

In order to help illustrate this POC, I have a console application that will simply receive 3 parameters and then write the contents to a file in my c:temp folder.  The reason why I am writing to a file is that when I call this exe from Powershell I don’t see the console window being displayed.  Perhaps there is a way to do that but I didn’t look for a solution for that.

namespace PowerShellPOC
{
    class Program
    {
        static void Main(string[] args)
        {

            string[] lines = { args[0], args[1], args[2] };
            // WriteAllLines creates a file, writes a collection of strings to the file,
            // and then closes the file.
            string filename = DateTime.Now.ToString("ddMMyyyymmhhss") + ".txt";
            System.IO.File.WriteAllLines(@"C:\temp\" + filename, lines);

           
        }
    }
}

 

In hindsight, I should have just built a send port subscription but here is my orchestration.

image

Using a standard FILE – receive location

image

On the Send things start to get a little more interesting. We will create a Static One-Way port and select the nSoftware.PowerShell.v4 Adapter.

image

Within our configuration we need to provide a Port Name (which can be anything) and our script.

image

If we click on the Script ellipses we can write our PowerShell script.  In this case we are able to retrieve our message that is moving through our Send Port and pass it into our executable.

image

If we only wanted some data elements we can also use $param3 = $xml.arguments.ReturnType

In this case “arguments” is our root node of our BizTalk Message and “ReturnType” is a child node in our XML Document.

When we go to process a file we will find that our text file has been created and it contains our 3 parameters; 2 that are hard coded and our BizTalk Messsage Payload.

image

Conclusion

When I think of BizTalk, I don’t necessarily think of Powershell.  But there will always be times when you need to perform some function that is a little bit off mainstream.  What I do like about this approach that there was no additional custom dev required to support the solution and we can use the actual BizTalk message in our Powershell script.

I am still exploring the capabilities of the adapter but after a dialog with the /nSoftware team I understand that remote Powershell scripts can be run and we can also use Dynamic ports and Solicit Response ports if we want to return messages from our PowerShell script to BizTalk.

For more details please check out the /nSoftware website.

Saturday, May 9, 2015

BizTalk 2013 SharePoint Adapter not respecting SharePoint 2013 View Name

 

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.

Background

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. 

image

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

 

image

For our purposes a Standard View will be sufficient.

image

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.

image

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

image

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.

image

When we click on our view for BizTalk, which in this case is called BizTalkMoveView we will only see our Approved document.

image

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

image

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.

image

If we go back to SharePoint we will discover both documents are gone.

image

Issue

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.

Resolution

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.

image

Our BizTalkMoveView is also displaying our file correctly

image

When we enable our Receive Location we will discover that only our Approved file has moved.

image

image

Our document that was in a Pending state remains in SharePoint as expected.

image

Conclusion

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.