Introduction
This article describes how to design and develop simple
workflow systems using BizTalk Server and Sharepoint Server. InfoPath is also
used for editing XML documents.
Human Workflow – what does it mean?
A Workflow is a movement of a document or a task across
several people. In a Human workflow several humans communicate using a business
process. This business process is implemented as a workflow. For example, an
article (document) could be routed to several people for a review in a
publishing company. In this article we shall consider a "Leave Approval
Workflow". This leave application raised by the employee is routed to the
manager and his boss for their inputs or approvals.
Role of BizTalk, Sharepoint and InfoPath
BizTalk Server 2006 – BizTalk is used to compose the
workflows and determine the routing of the documents. It is also used to make
database related changes pertaining to the workflow.
Sharepoint Server 2003 – Sharepoint is used as a repository
for the leave application documents. It also consists of several views for
BizTalk to pickup the right kind of documents.
InfoPath 2003 – InfoPath 2003 is used for editing XML
instance documents from the XML schemas.
Target Audience
This article uses BizTalk 2006, SQL Server 2005, Visual
Studio 2005, Sharepoint Server 2003 and InfoPath 2003. It is expected that
readers of this article have considerable working experience in these products.
Leave Workflow System
Figure 1
In this article, we shall be
considering a workflow system for Leave Approval process. An Employee raises a
leave request by placing a document in a folder. This document is then picked
up by the system and routed to the manager of the employee. The manager can
either "Approve" or "Reject" the leave request. In the case, when the manager
is on leave or not available, the leave request would then get routed to the
manager’s boss for his approval. The leave request would get "TimedOut" and is
sent back to the employee, in the case when the manager and his boss fail to
respond to the leave application. Refer to the Figure 1.
Database Design
The database consists of two tables, the "Employee" table and
the "EmployeeLeaveHistory" table.
The column names and the primary keys are shown in the
Figure 2.
Figure 2
Points to note:
- When an employees leave request gets APPROVED, the number of
leaves requested gets deducted from the SickLeaveBalance or the
EarnedLeaveBalance, based on the LeaveType.
- If the number of leaves requested is greater than the available
leaves, the leave request is automatically REJECTED, before being routed to the
manager of the employee.
- The Employee Leave History table maintains the record of the
leaves taken by the employee. A row is inserted into this table, when a leave
request is APPROVED.
Stored Procedures
Several stored procedures have been written for the common
tasks of querying and inserting records into the tables shown in Figure 2. The
stored procedures list is shown in Figure 3.
Figure 3
Database Library
A Database library is used to interact with the SQL Server
database. This DB library is referenced by the BizTalk project and is called
directly by the Orchestration to perform database related operations. This
database library must be present in the Global Assembly Cache (GAC), since all
assemblies referenced by BizTalk must be placed in GAC. The class diagram for
the DB Library is shown in Figure 4.
Figure 4
BizTalk Artifacts
A BizTalk solution generally consists of the following
- A Schema – LeaveApp.xsd
- A Property Schema (For exposing the schema properties into an
orchestration and ports)
- An Orchestration – RouteLeaveApplications.odx & ProcessLeaveApplications.odx
- An Pipeline (optional)
Schema
The Leave Application Schema encapsulates all the relevant
information required for leave processing. Designing a schema is first step in
any BizTalk project. Refer to the leave application schema in Figure 5. The
elements of the schema are self explanatory.
Figure 5
Orchestrations
An Orchestration is essentially a Business Process. In this
example the Orchestration Workflow has been split into two parts. The first
part routes the Leave Application submitted by the employee to the manager. The
second part is responsible for picking up the APPROVED/REJECTED/TIMEDOUT leave
requests and takes appropriate actions.
A message in an Orchestration binds to a schema, in this
case the "LeaveApp" schema shown in the Figure 5. There are two messages in the
orchestration, one is incoming message and another is the outgoing message.
The "RouteLeaveApplications" Orchestration performs
the following ...
- The Orchestration picks up the Leave Request message from the
Sharepoint folder.
- The message is validated against the number of leaves available
from the Employee table. In the case where no leaves are available an exception
is raised by the stored procedure. This exception is caught by the
Orchestration and the message is sent back to the requested employees folder.
Refer to the Figure 6.
Figure 6
The "ProcessLeaveApplications" Orchestration performs
the following.
- The BizTalk engine monitors the managers folder in Sharepoint and
checks to if the following conditions have occurred, and picks up the message.
- Leave Application message Status field set to either "APPROVED"
or "REJECTED".
- Leave Application message Status field set to default value
"REQUEST", is unchanged after the elapsed timeout.
- The message picked up in the previous step is routed to the
manager’s boss, in this case to Jack for Approval. In case Jack does not
approve it and the elapsed timeout occurs, the message is again picked up and
its status is set to "TIMEOUT" and routed back to the employee who has raised
this request and in this case it is "JOE". Refer to Figure 7.
Dynamic Ports
The BizTalk ports which are used to interact with Sharepoint
are Dynamic ports. BizTalk can determine the destination folder, only when it
receives the Leave request message and retrieves the manager name of the
Employee.
Code Listing for a TIMEDOUT message processing
LeaveAppOut = LeaveAppIn;
LeaveAppOut(LeaveWorkflowSystem.Status) = "TIMEDOUT";
LeaveAppOut(LeaveWorkflowSystem.Comments) = "Leave request timed out even after escalation.";
destinationFolder = LeaveAppIn(LeaveWorkflowSystem.EmpName);
SendLeaveAppPort(Microsoft.XLANGs.BaseTypes.Address) =
"wss://win2k-2005-pc:80/sites/BizTalkDemos/" + destinationFolder + "";
NOTE: The last line in the code, specifies the destination
of the message by the setting the value in the Dynamic port. Don’t worry about
the Sharepoint path hard coding, this can be moved into an environment
variable.
Figure 7
Integration with Sharepoint
The BizTalk integration with Sharepoint is the most
important section of this article. The Windows Sharepoint Services adapter
integrates a Document Library with the BizTalk System.
Document Libraries
A Document Library is just a repository for a file. It also
provides additional features like Check-in, Check-out and version control.
Figure 8
The Figure 8 shows the various document libraries created
for the employees in the Organization. IMPORTANT NOTE: When the document
library is created ensure that its schema template is that of the leave
application schema, otherwise the leave application properties cannot be
processed by Sharepoint.
Views
Figure 9
There are two views shown in the Figure 9.
- TimedOutForms – This view shows only the leave applications which
are "TIMEDOUT". This is done by setting the filter properties on the view to
display the leave applications which have been pending since 1 day. This means
that previous days pending leave applications shall be "TIMEDOUT" today.
- ApprovedRejectedDocs – This view shows only the leave
applications which are either "APPROVED" or "REJECTED".
Note: The views are used by BizTalk to pickup the
leave application messages. Those messages which do not come into the view are
ignored by BizTalk.
The Figure 10 shows a TIMEDOUT message in Joe’s folder.
Figure 10
InfoPath – Editing an XML document with InfoPath
The Figure 11 shows the filling up of the
"JoeLeaveApplication" XML document using InfoPath.
Figure 11
Event Viewer – Can be used to monitor the Workflow.
Notice the messages in Figure 12, logged in the Event Viewer
for the Joe Leave Application which been TIMEDOUT all through.
Figure 12
About the downloadable code…
- Unzip and Restore the database backup
file EmployeeDB.zip into the SQL Database Server on your box. This has been
created on a SQL Server 2005 box.
- Unzip the BizTalk project zip file with
the folder names in the C:\ drive. Using the Bindings
file to setup the ports. Edit the BizTalk project in Visual Studio 2005 and
update the Sharepoint connection strings.
- Add the User Environment variable "
DBLIB.EMPLOYEEUTIL" with the value as the Database Connection String.
- Place the LeaveRequestMessage in the
LeaveApplication Document Library folder and check the Event Viewer.
