Hi Folks,
I recently needed to automate our file management system. I decided to use the BizTalk SharePoint Adapter that ships with BizTalk 2006 and R2. This article is for advanced BizTalk developers that are comfortable with pipeline components.
Introduction
How the adapter works is that you need to install the Adapter Web Service on the server hosting the SharePoint site where you want BizTalk messages to be delivered. When you run the setup wizard from the BizTalk CD you will notice the Share Adapter option, it is easy to confuse this with an Adapter, but it is not an adapter, the adapter is already installed in BizTalk, the add-on in the CD is the actual web service that the Adapter will communicate with in order to post documents and messages to a SharePoint site.
I am going to show you how to do this the hardcore way, what I mean is, NOT using Orchestrations, I hate them, I really do hate orchestrations.
Below is a diagram showing the high level architectural overview of the communication layers.
Sources: http://technet.microsoft.com/en-us/library/aa558796.aspx
So the option on the CD is actually to install the BTSharePointAdapterWS.asmx Web service.
The problem I found with the SharePoint adapter is the following:
- I have different types of messages that need to be send to SharePoint, and extra column information needs to be populated in the Document Library once BizTalk posts data to the Document Library. The extra column information is different for different messages. I want to avoid having multiple SharePoint Send ports!
- When BizTalk posts messages to the SharePoint server, some of the documents are routed directly to an Archive location while others are sent to an Incoming Folder on SharePoint so that a separate BizTalk process can process the files and send them to the BizTalk workflow application. Upon successfully sending a SharePoint document to the workflow system on BizTalk, the SharePoint adapter must Archive the file into an archive location for users to have access to.
The Receive Adapter for SharePoint is rather nice, however I feel it is still an immature product, and needs some work on it. One of the main reasons is that if you look closer at it, it supports archiving documents once pulled from SharePoint.
This is a fantastic feature! But I was soon to be disappointed as I am with allot of the BizTalk features (Such as the aggregator pattern that does not work properly, seem my blog about it). The problem is that the Archive feature above calls the web service and method FinalizeDocuments:
Now if you look at my requirements above, I wanted extra column info to me sent to SharePoint, the send port for SharePoint can do this, see below:
Which is nice, you mention the custom column name you added in the document library view and then the value. This information for all the columns is then translated into an XML document (More on this later) and then sent to the adapter for processing.
So back the problem, if you choose to Archive your files, the web service method will NOT copy all the custom column information across, so you lose it, and this can spell bad news, when you have views in SharePoint that rely on these columns for filtering e.g. By Country, Region, Brand etc. So I hope Microsoft fixes this problem soon, what they need to do is alter the web service to also manage the document metadata from the columns, this is easy for them to do, since there receive adapter automatically gets this information, I will show you how later.
Ok, so I think I have set the scene here in summary we got some serious issues with the SharePoint adapter and we need to address them.
Overview
What we going to do is this.
- Install the Web Service on the SharePoint Site
- Configure a Dynamic SharePoint Service send port to send documents to SharePoint
- Configure a Receive SharePoint port to pull the documents from SharePoint (But not use the archive feature of the receive port, sinceCon archiving does not store custom column information, we need to setup an extra processing round in BizTalk to send it back to SharePoint for archiving)
- Configure a Dynamic SharePoint service to Archive documents (Setup a filter expressions to subscribe to messages coming from the SharePoint port)
- Configure a send port that subscribes to the same messages as step 4 but routes these to the workflow system
The Pattern is like this
- File Receive Locations to pickup files
- Dynamic send port to send documents to the incoming folder to archive folder (messages in incoming are pulled back into BizTalk, messages sent to archive are not meant to go to workflow system)
- SharePoint Receive Location to pull files from incoming
- Send location to subscribed to pulled messages and send it to workflow
- SharePoint send location to subscribed to pulled messages and send it to Archive
Basically we had to introduce extra ports to compensate the bug in the SharePoint adapter where column information is lost, what we did is develop a custom pipeline that will manage the metadata from the SharePoint site when data is pulled off the site.
This is what the configuration looks like in BizTalk.
TIP: In production make a dedicated receive and send handler for SharePoint, if the SharePoint server is down, the BizTalk process will crash! Another bug with the SharePoint adapter, insufficient error handling! Remember in BizTalk that a receive handler and send handler are actual windows services, so make one dedicated to SharePoint, so when it crashes it does not affect other BizTalk processes! MICROSOFT PLEASE FIX THIS, IF SHAREPOINT IS UNAVAILABLE DON’T CRASH THE BIZTALK HOST INSTANCE IT IS RUNNING UNDER!
So as you can see my two custom pipeline components are located in the receive ports, the one custom component on the file receive port is used to prepare the context data in BizTalk so that the Dynamic Send Port can interpret this at runtime, the other custom component is developed to manage the metadata from SharePoint and remember the custom column values when PULLING from SharePoint.
Here are the send ports.
Notice the AND filter above, I will discuss this later, it is the DEFAULT value when you create a filter, and it can cause problems with Dynamic Send ports!
Notice the filter above matches the filters below, since these to send ports subscribe to the SAME message:
Remember I spoke about the "AND" above, if you go to the Group Hub Page and check the subscriptions for a DYNAMIC Port:
You get this filter on a dynamic port:
Â
This is how we subscribe to messages, THINK ABOUT THIS…. I hope you had a though about it, remember a dynamic port does not have any configuration forms, so to configure the port, you need to do it at runtime, which means you will need to promote the .OutboundTransportType and OutboundTransportType, since the filter is set in the GUI for ReceivePortName == File Management Importer. Where do you promote these in an orchestration or PIPELINE? We will come back to this, but for now remember, when dealing with dynamic ports, you need to manage the conext information manually in an orchestration or pipeline, since I hate orchestrations, I always develop custom pipelines.
A huge clue to configure a dynamic send port is this article:
http://technet.microsoft.com/en-us/library/aa547920.aspx
We will come back this later, for now, I wanted you to keep in mind that configuring dynamic send ports is not trivial, but also not hard, in fact even without any documentation it can be done, by delving into BizTalk.System Application and checking out the schemas, for this one, I had to read this schema:
If you open it, it provides clues on how to configure message context for the dynamic send port. Remember the static port is similar to the dynamic one!
You can read the above schema view to see what properties to promote, for example, LOOK AT FILENAME, when you receive a file from the file adapter, the property is called Receivefilename,
but in WSS adapter it is different, which means you lose the file name info but you can still save it and then manually promote and set this value, I show you how later, for now, I want you to understand the mechanics of dynamic property promotion, in essence what you looking at above is a BUILT IN PROPERTY SCHEMA.
From the above you can see why when a message goes from one adapter to another that context information can be lost, because adapters have different context property names, so a file name in a file adapter is different to a SharePoint adapter, since they different PROPERTY SCHEMA’S! Ok, enough lets get down configuring the web service.
Configure the security groups
I always have my service accounts and permissions in Active Directory, so get these groups up and running in AD.
- Create a Windows Group called: SharePoint Enabled Hosts
Then you add the BizTalk host to the group, this is the service account username used by BizTalk, the one that is used when you configure a BizTalk handler, I chatted about this before, always have a dedicated handler for SharePoint1
- On the SharePoint site where the BTSharePointAdapterWS is going to be installed, give the group SharePoint Enabled Hosts the contributor access
Configure the SharePoint Web Service
On the SharePoint server use the BizTalk R2 CD to install the SharePoint Adapter Service:
Then run the BizTalk Configuration
It will install the web service on the web site:
Edit the web service web.config file by commenting out the remove name element, this will allow you to browse the web service list.
<webServices>
<protocols>
<!–<remove name="Documentation"/>–>
</protocols>
</webServices>
Configure the File Receive Port and Location
Now that you got the web service running, we now need to configure the file receive location
Basically, it is pretty easy to setup the receive location; the hard part is developing the custom pipeline component to prepare the document for the Dynamic SharePoint adapter. I assume you know how to write custom pipeline components, the component I developed is a decoder.
Here is the code for my pipeline, I call a custom external class to manage the metadata, you can do the same if you like. The class I use reads a SQL table to detect the file coming in, by reading the pattern in the file name or the folder name where it came from (You can store this, since a property in the File adapter is the file path). I want to keep this document simple, so I assume you know about developing pipeline components, there is many resources on the net.
The main code is in the execute method.
|
public IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg)
{
IBaseMessage outMsg;
string recieveFileNamePath = pInMsg.Context.Read("ReceivedFileName", "http://schemas.microsoft.com/BizTalk/2003/file-properties").ToString();
string originalFileName = Path.GetFileName(recieveFileNamePath);
////////////////////////////////////////////////////////////////////
LogManager.Log("Preparing to Send File to Sharepoint, Send Files To SharePoint: " + recieveFileNamePath, "General");
////////////////////////////////////////////////////////////////////
try
{
//Link the context of the output message to the input message
outMsg = pContext.GetMessageFactory().CreateMessage();
outMsg.Context = pInMsg.Context;
outMsg.AddPart(pInMsg.BodyPartName, pContext.GetMessageFactory().CreateMessagePart(), true);
//initialize the FileMetaData
string messageID = pInMsg.Context.Read("InterchangeID", "http://schemas.microsoft.com/BizTalk/2003/system-properties").ToString();
FileMetaData fmd = new FileMetaData(originalFileName, messageID);
outMsg.Context.Write("Filename", "http://schemas.microsoft.com/BizTalk/2006/WindowsSharePointServices-properties", fmd.NewFileName);
outMsg.Context.Promote("Filename", "http://schemas.microsoft.com/BizTalk/2006/WindowsSharePointServices-properties", fmd.NewFileName);
outMsg.Context.Write("OutboundTransportType", "http://schemas.microsoft.com/BizTalk/2003/system-properties", "Windows SharePoint Services");
outMsg.Context.Promote("OutboundTransportType", "http://schemas.microsoft.com/BizTalk/2003/system-properties", "Windows SharePoint Services");
outMsg.Context.Write("OutboundTransportLocation", "http://schemas.microsoft.com/BizTalk/2003/system-properties", @"wss://" + fmd.SharePointServer + ":" + fmd.SharePointPortNumber + "/" + fmd.SharePointIncomingDocumentPath);
outMsg.Context.Promote("OutboundTransportLocation", "http://schemas.microsoft.com/BizTalk/2003/system-properties", @"wss://" + fmd.SharePointServer + ":" + fmd.SharePointPortNumber + "/" + fmd.SharePointIncomingDocumentPath);
string ConfigPropertiesXml = @"<ConfigPropertiesXml>
<PropertyName1>SPSID</PropertyName1>
<PropertySource1>" + fmd.SharePointID + @"</PropertySource1>
<PropertyName2>FileFeedSource</PropertyName2>
<PropertySource2>" + fmd.FileFeedSource + @"</PropertySource2>
<PropertyName3>OriginalFileName</PropertyName3>
<PropertySource3>" + fmd.OriginalFileName + @"</PropertySource3>
<PropertyName4>BizTalkMessageID</PropertyName4>
<PropertySource4>" + fmd.BizTalkMessageID + @"</PropertySource4>
<PropertyName5>Region</PropertyName5>
<PropertySource5>" + fmd.Region + @"</PropertySource5>
<PropertyName6>Country</PropertyName6>
<PropertySource6>" + fmd.Country + @"</PropertySource6>
<PropertyName7>Brand</PropertyName7>
<PropertySource7>" + fmd.Brand + @"</PropertySource7>
</ConfigPropertiesXml>";
outMsg.Context.Write("ConfigPropertiesXml", "http://schemas.microsoft.com/BizTalk/2006/WindowsSharePointServices-properties", ConfigPropertiesXml);
////////////////////////////////////////////////////////////////////
LogManager.Log("Writing Context Properties, Send Files To SharePoint: NewFilename=" + fmd.NewFileName + " OriginalFileName:" + originalFileName, "General");
////////////////////////////////////////////////////////////////////
}
catch (Exception e )
{
LogManager.Log("Exception Occured " + e.Message ,"General");
throw new Exception("Exception Occured: " + e.Message);
}
return pInMsg;
}
|
Excuse my class FileMetaData, this is a custom class I developed to manage and store metadata, for this article I won’t delve into it but what it does is basically detects the file feed source and does other business rules which is beyond the scope of this article. But here is an outline of it for interest, from the class below, you can see how much more power I get from using pipelines that orchestrations, and I can use non serializable classes etc.
This is how your BizTalk project might look like
Remember multiple BizTalk projects can be installed in ONE BizTalk application, just update the project properties here:
Here in the pipeline, I set the values of the CUSTOM COLUMNS in SharePoint in this variable:
http://technet.microsoft.com/en-us/library/aa547920.aspx
Here is how the data looks like in the message during routing once in the message box:
What’s really cool, is you can manually force a message to fail by shutting down the web service or changing the name of it, then look at the suspended instance in BizTalk and learn how to populate the context properties, here is the important ones, I circle them for you. This was all done with the pipeline above!
This is getting EXCITING!!!
You see what we did here is PREPARE the file for SharePoint way before it gets there, we did this on the file receive port:
NOW DO YOU UNDERSTAND WHY A FILENAME FROM A FILE ADAPTER DOES NOT know how to find it’s way to the filename in the WSS adapter? Look above, the pipeline we developed promoted and populated the values in for the context in red!
How the WSS adapter works and various others is using an XML template, in this case ConfigPropertiesXML (THE SQL adapter does the same thing, remember my article about it, you could bypass orchestrations and prepare the SQL adapter by manually writing data to the configpropertiesxml, there are many articles on how this is done in an orchestration, but bugger that, let’s do them in the component level, much faster and you can do allot of dynamic value management by using a configuration database, and yes it is super fast when combined with Enterprise Library Caching Block!)
Configure Dynamic send port to incoming folder on SharePoint
Now all you need to do is create a dynamic send port and configure the filter to grab documents from the file receive port, remember the filter I mentioned!
This filter will do this in the background:
Now if you look at the Pipeline we created above the following code gets the filter working!
So it should make sense, since allot of people get stuck trying to route documents to a dynamic port, YOU HAVE to promote or set values for properties, and the best way to find out is to look at the subscription filters in the group hub page!
SharePoint Receive Location to pull files from incoming
Then you create a Receive Location to pull from the incoming folder on SharePoint:
But you need to manage the context information and column information; this is a bit trickier! What I did was I needed to know was:
- Does the default receive adapter store the column information that gets lost if I used the archive feature from the receive location
The answer is yes it does! What I did was I configure the receive adapter to pull messages from SharePoint, and made no subscription for it, to force a failure and looked in the context of the message to see where this info was stored, then I could access it. The easiest way to force a failure was un-enlist my send ports to workflow and archive on SharePoint, so I get a stuck message in BizTalk that was pull from Sharepoint.
And my receive port is running with this configuration:
NOTICE ANOTHER CUSTOM PIPELINE, I show it later, for now, it is IMPORTANT to understand how we get access to column information when pulling a file from sharepoint:
When you pull documents off SharePoint you must specify a view name, I use ALL Documents here since I have a library dedicated to BizTalk polling! Makes document library management easier.
I know drop a file, the file will fail in BizTalk as no active subscriptions are running once the file is pulled from SharePoint:
So in SharePoint the file will go here:
Notice the custom column information.
The receive location will pull the file off SharePoint and suspend.
This is the perfect opportunity to check if the COLUMN INFO is in the message context, if it is, we can write a custom pipeline to get it and store it and then another send port to SharePoint can archive it!!!
WE STYLING THERE IS A FIELD!!
This is the data in the field InPropertiesXml!!!!!!!!!
What I do is click it and press Ctrl-C and then put it in notepad and clean it up a bit so it is readable:
Ok, so you get the idea, I used the BizTalk admin console to check the message context of a freshly baked SharePoint file and then write a pipeline component to get this data and then prepare it for sending to SharePoint by transferring the data to the configpropertiesXMLl! Like this:
|
public IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg)
{
IBaseMessage outMsg;
string sharePointFileName = pInMsg.Context.Read("Filename", "http://schemas.microsoft.com/BizTalk/2006/WindowsSharePointServices-properties").ToString();
////////////////////////////////////////////////////////////////////
LogManager.Log("Preparing to Send File to Workflow and Archive in Sharepoint, Receive Files from SharePoint: " + sharePointFileName, "General");
////////////////////////////////////////////////////////////////////
try
{
//Link the context of the output message to the input message
outMsg = pContext.GetMessageFactory().CreateMessage();
outMsg.Context = pInMsg.Context;
outMsg.AddPart(pInMsg.BodyPartName, pContext.GetMessageFactory().CreateMessagePart(), true);
//initialize the FileMetaData
string messageID = pInMsg.Context.Read("InterchangeID", "http://schemas.microsoft.com/BizTalk/2003/system-properties").ToString();
//Get the MetaData that was pulled off the SharePoint Server, The Adapter stores it in InPropertiesXml context which is not promoted
string inPropertiesXml = outMsg.Context.Read("InPropertiesXml","http://schemas.microsoft.com/BizTalk/2006/WindowsSharePointServices-properties").ToString();
XmlDocument doc = new XmlDocument();
doc.LoadXml(inPropertiesXml);
FileMetaData fmd = new FileMetaData();
foreach (XmlNode node in doc.SelectSingleNode("InPropertiesXml").ChildNodes) //Since all data is stored in a root node structure
{
if (node.Attributes.Count > 0) //Only look at nodes in the XML with attributes, since the metdata is stored in attributes
{
switch (node.Attributes[0].Value)
{
case "Filename":
fmd.NewFileName = node.InnerText;
break;
case "SPSID":
fmd.SharePointID = int.Parse(node.InnerText);
break;
case "FileFeedSource":
fmd.FileFeedSource = node.InnerText;
break;
case "OriginalFileName":
fmd.OriginalFileName = node.InnerText;
break;
case "BizTalkMessageID":
fmd.BizTalkMessageID = messageID; //Assigns a new BizTalk Message ID
break;
case "Region":
fmd.Region = node.InnerText;
break;
case "Country":
fmd.Country = node.InnerText;
break;
}
}
}
fmd.SetFileMetaDataArchive(); //Initilise the object
outMsg.Context.Write("Filename", "http://schemas.microsoft.com/BizTalk/2006/WindowsSharePointServices-properties", fmd.NewFileName);
outMsg.Context.Promote("Filename", "http://schemas.microsoft.com/BizTalk/2006/WindowsSharePointServices-properties", fmd.NewFileName);
outMsg.Context.Write("OutboundTransportType", "http://schemas.microsoft.com/BizTalk/2003/system-properties", "Windows SharePoint Services");
outMsg.Context.Promote("OutboundTransportType", "http://schemas.microsoft.com/BizTalk/2003/system-properties", "Windows SharePoint Services");
outMsg.Context.Write("OutboundTransportLocation", "http://schemas.microsoft.com/BizTalk/2003/system-properties", @"wss://" + fmd.SharePointServer + ":" + fmd.SharePointPortNumber + "/" + fmd.SharePointArchiveDocumentPath);
outMsg.Context.Promote("OutboundTransportLocation", "http://schemas.microsoft.com/BizTalk/2003/system-properties", @"wss://" + fmd.SharePointServer + ":" + fmd.SharePointPortNumber + "/" + fmd.SharePointArchiveDocumentPath);
string ConfigPropertiesXml = @"<ConfigPropertiesXml>
<PropertyName1>SPSID</PropertyName1>
<PropertySource1>" + fmd.SharePointID + @"</PropertySource1>
<PropertyName2>FileFeedSource</PropertyName2>
<PropertySource2>" + fmd.FileFeedSource + @"</PropertySource2>
<PropertyName3>OriginalFileName</PropertyName3>
<PropertySource3>" + fmd.OriginalFileName + @"</PropertySource3>
<PropertyName4>BizTalkMessageID</PropertyName4>
<PropertySource4>" + fmd.BizTalkMessageID + @"</PropertySource4>
<PropertyName5>Region</PropertyName5>
<PropertySource5>" + fmd.Region + @"</PropertySource5>
<PropertyName6>Country</PropertyName6>
<PropertySource6>" + fmd.Country + @"</PropertySource6>
<PropertyName7>Brand</PropertyName7>
<PropertySource7>" + fmd.Brand+ @"</PropertySource7>
</ConfigPropertiesXml>";
outMsg.Context.Write("ConfigPropertiesXml", "http://schemas.microsoft.com/BizTalk/2006/WindowsSharePointServices-properties", ConfigPropertiesXml);
}
catch (Exception e )
{
LogManager.Log("Exception Occured " + e.Message ,"General");
throw new Exception("Exception Occured: " + e.Message);
}
return pInMsg;
}
|
SharePoint send location to Archive
Ok, then all you do is create a new dynamic send port with filters to get messages from this SharePoint port.
Here is the filter in the subscriptions page in GHP.
Send location to subscribed to pulled messages and send it to workflow
With same filters as above, but the not dynamic so they look like this, notice NO AND IN THIS, since it is not dynamic!!!!
SharePoint Libraries
Ok, so now in SharePoint, the final check is to see if the columns are populated in the archive folder!
IT SURE IS!
And the incoming library is now empty J
Conclusion
That’s all folks, We covered allot here, but I hope this article will give you a deeper understanding of modifying context properties to effectively setup dynamic routing, you wondering how the column data is populated, I have a database table that the custom file management class calls to detect the country, region and brand info etc, this all comes from the Data Access Layer class, outlines above in the class diagram, this is beyond the scope of this article, but it does prove that you can route documents and dynamically set properties and EVEN transform flat files to XML using a sophisticated custom class which takes text data and transform them to XML, without using the sluggish BizTalk Mapper, maybe in another blog I will cover a universal way to translate flat files to XML used in BizTalk, we will see, hope you enjoyed it!
Here is a sneak at the SQL table used to configure a file feed, all this is set within a pipeline by using caching, data access layer and a file metadata class as well as a custom file translation design pattern to convert data from flat file to xml, it is extremely fast, a 10MB file will take 2-3 seconds to process in a pipeline, if this is done in an orchestration it would take much longer from 30 seconds to minutes. What I like about a custom file mapper, is I have total contol over encoding.
|
USE [FileManagement]
GO
/****** Object: Table [dbo].[FileFeeds] Script Date: 06/15/2008 12:53:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FileFeeds](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[FileFeedSource] [nvarchar](255) NOT NULL,
[StringIdentifierInFileName] [nvarchar](50) NULL,
[FolderName] [nvarchar](255) NOT NULL,
[FileType] [nvarchar](50) NOT NULL,
[Delimiter] [char](1) NULL,
[Encoding] [nvarchar](25) NOT NULL,
[SharePointServer] [nvarchar](100) NOT NULL,
[SharePointPortNumber] [int] NOT NULL,
[SharePointIncomingDocumentPath] [nvarchar](255) NOT NULL,
[SharePointArchiveDocumentPath] [nvarchar](255) NULL,
[ContainMultipleSources] [bit] NOT NULL,
[DefaultSourceName] [nvarchar](255) NULL,
[FileMappingXML] [xml] NULL,
[CreationDate] [datetime] NOT NULL CONSTRAINT [DF_FileFeeds_CreationDate] DEFAULT (getdate()),
[Region] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[Brand] [varchar](255) NULL,
CONSTRAINT [PK_FileFeeds] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
|
Seems to me Microsoft rushed the development of the WSS adapter, else they would have noticed that the static port does not retain column info when you use the intrinsic archive feature! Maybe they will fix this, who knows, a Feature or a Bug?
Romiko Derbynew 