Building a Leave Application Workflow System using BizTalk Server 2006 and SharePoint Server 2003
 
Published: 23 May 2006
Unedited - Community Contributed
Abstract
In this article Naveen demonstrates how to create a simple workflow system using BizTalk Server 2006 and Sharepoint portal server.
by Naveen Karamchetti
Feedback
Average Rating: 
Views (Total / Last 10 Days): 46436/ 85

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 the boss for their input or approval.

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 a 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 this 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 sent back to the employee 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 employee’s 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 an 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 Database 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 Database 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

A Pipeline (optional)

Schema

The Leave Application Schema encapsulates all the relevant information required for leave processing.  Designing a schema is the 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 an 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 employee's folder.  Refer to the Figure 6.

Figure 6

 

The “ProcessLeaveApplications” Orchestration performs the following:

The BizTalk engine monitors the manager's folder in SharePoint then checks to see 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 the 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 “TIMEDOUT” and routed back to the employee who has raised this request, 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, but only when it receives the Leave request message and retrieves the manager name of the employee.

Listing 1: 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.  Do not 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 above figure 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 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 for 1 day.  This means that the previous day's 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 given below shows a "TIMEDOUT" message in Joe’s folder.

Figure 10

InfoPath – Editing an XML document with InfoPath

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 that are logged in the Event Viewer for the "JoeLeaveApplication" have been "TIMEDOUT" all through.

Figure 12

Downloads

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.

Summary

In this article you have learned how to design and develop a simple workflow system using BizTalk Server and SharePoint Server with the help of diagrams and code samples.



User Comments

Title: Cannot download Samples   
Name: Ash
Date: 2012-03-25 9:21:00 AM
Comment:
I cannot download the samples, get 404 - File or directory not found error
Title: biztalk development   
Name: biztalk programmers india blog
Date: 2011-12-07 2:43:43 AM
Comment:
Spot on with this write-up, I truly think this website needs much more consideration. I’ll probably be again to read much more, thanks for that info.
Title: doing MCA project on leave management using java   
Name: Ruchi
Date: 2010-09-24 1:23:06 AM
Comment:
Hi,
Can you provide sample of simple leave application without using sharepoint and infopath ?
Say for example Employee can raise a leave appliation through website and the leave application routed to manager for his approval. Manager login in site and check is workitems. He may approve or reject like.
It's simple and straightforward to understand
My email is rm_08817@yahoo.com
Title: good   
Name: vardhman
Date: 2010-05-29 5:13:45 AM
Comment:
thankx
Title: Workflow   
Name: rajendra
Date: 2009-10-26 2:07:04 AM
Comment:
Great one.
Title: Request for simple Leave Application Management System   
Name: Zanele
Date: 2009-05-08 9:07:50 AM
Comment:
Hi,
Could you please provide me with an up and running above mentioned system but not the executable because I need to view the databases as well as the coding. Thak you.

my email address: ngcobozanele1@yahoo.com
Title: How to install in WSS 3.0   
Name: Jeff Cheung
Date: 2008-10-23 11:42:07 PM
Comment:
Hi,
May I know that how to install the download file in WSS 3.0?
Would you please send me a simple instruction for it? Thanks a lot.

My email address is : jeffcheung@lexibook.com
Title: Request for simple leave application without using sharepoint.   
Name: kumaran
Date: 2008-10-01 1:33:48 AM
Comment:
Hi,
Can you provide sample of simple leave application without using sharepoint and infopath ?
Say for example Employee can raise a leave appliation through website and the leave application routed to manager for his approval. Manager login in site and check is workitems. He may approve or reject like.
It's simple and straightforward to understand.
Regards,
kumaran
Title: Nice but need Help   
Name: Arun
Date: 2008-09-01 1:24:20 AM
Comment:
Nice Article. But my task is to develop the same within Sharepoint but not with BizTalk. Could you please guide me with this to develop the same simpler and easy in Sharepoint portal
My email id is: arunbalait@aim.com, arunbalait@hotmail.com
Title: Development of BizTalk application   
Name: Mike Wassermann
Date: 2008-08-01 4:26:14 PM
Comment:
I need a little help with the Sharepoint side of the article, I don't have any experience with Sharepoint and don't understand how to configure the sharepoint stuff to get the BizTalk stuff to work. Any detail steps on setting up sharepoint to get the code to work will be helpful.

Mike
Title: Deployment of Biztalk application   
Name: Ranjeet
Date: 2008-05-12 10:55:07 AM
Comment:
hi
how can we coneect Biztalk application to C# application. please help me. i m new in biztalk server2006.

Thanks in aadvance
Title: simplified Sample Application in BizTalk Server 2006   
Name: Ranganath
Date: 2008-04-23 7:04:59 AM
Comment:
HI,

This is the First Time i am learning the BizTalk server 2006.

I want simplified sample Application in it.

i.e. Step By Step
Title: Good Example   
Name: Guru Raam
Date: 2008-03-04 7:12:56 AM
Comment:
Hi,

I'm new to BizTalk, this example is very useful to me.

Thanks
Title: Error   
Name: Nikunj R
Date: 2007-06-16 4:45:39 AM
Comment:
After deployment i stuck with following error ,
event id:5753
when putting .xml file receive location i got the error:
The published message could not be routed because no subscribers were found. This error occurs if the subscribing orchestration or send port has not been enlisted, or if some of the message properties necessary for subscription evaluation have not been promoted. Please use the Biztalk Administration console to troubleshoot this failure.
My email id is:nikunj_18july@yahoo.co.in
Title: Regarding Leave Sample   
Name: Nikunj R
Date: 2007-06-16 3:21:52 AM
Comment:
I am very impress with your comment, i wants to know how to set sharepoint connection in application
mi id: nikunj_18july@yahoo.co.in
Title: Help needed   
Name: Malathi
Date: 2007-03-24 12:31:56 AM
Comment:
I am new to Biztalk and I tried to launch this leave work flow system, but not able to lauch successfully, Can you please help me to launch this? I restored the database, created the infopath template but not able to build and deploy the application. It would be nice if you give me the step by step instruction to launch this.



Thanks in Advance

Regards

Malathi
Title: Implement Leave Request Case Study   
Name: Pouyan
Date: 2006-11-22 7:26:30 AM
Comment:
Hopfully i could use this case study and after struglying a lot i could configuring and launch the system. Thank you very much to provide this title.
If any one faces a problem to use this sample i can share my experiences with him. Contact me by this E-Mail:
PSepah@Yahoo.com
Title: Business process development stages   
Name: Jon
Date: 2006-11-07 9:58:03 AM
Comment:
Very entertaining issue. I haven't heard of this one. It will be necessary to visit you on a thicket!
Title: Good work   
Name: Harry Tsavdaris
Date: 2006-11-04 5:59:05 PM
Comment:
I am new in Biztalk and Sharepoint and you example is very useful.
Thanks.
Title: Need more details how to run the sample solution   
Name: Abhijit Mahato
Date: 2006-11-01 2:37:40 AM
Comment:
Hi,
This article is really very good. From last couple of months i was just searching any article about the integration of BizTalk and Share point server. Actually i am using BizTalk 2004 and Sqlserver 2000. Could you please provide me some more details about this, so that i can use it.

Thanks in advance
Abhijit Mahato
email : abhi_dev2@rediffmail.com
Title: How to Deploy   
Name: Pouyan
Date: 2006-10-09 3:13:21 AM
Comment:
I still try to use your sample. I am new in using Biztalk and visual stuadio 2005. Would you plesae provide me a little more detail document to deploy your sample on my computer? I have the following questions:
1-How can i edit the Sharepoit connection ?
2-How can i add the "DBLIB.EMPLOYEEUTIL" variable (When i start to Deploy i get SQL-Connection Error)
By the way i installed SQL-Server 2005 and Sharepoint and it seems there is no problem to use your sample.
Thanks a lot in advance.
Pouyan
E-Mail: pouyan.sepahvand@asiacell.com
Title: SQL-Server Problem   
Name: Pouyan
Date: 2006-10-07 7:47:40 AM
Comment:
Dear Naveen Karamchetti,

I am very interested in to you your sample. Actually i am new in using BizTalk. My problem is to use SQL-Server with SP4 instead of SQL-Server 2005. Are there any way to use this sample by SQL-Server2000. I couldn't restor SQL-Server backup file.

All the best
Pouyan
Title: Workflow   
Name: Steven
Date: 2006-05-30 1:28:45 AM
Comment:
Workflow using BizTalk has been explained very well in this article. Unfortunately BizTalk HWS for workflow is complicated to use, and hence this could be the best way.
Title: Good   
Name: Ram Potturi
Date: 2006-05-26 11:30:30 PM
Comment:
Thank you very much for good article.
Thanks,
Ram

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-04-16 10:36:01 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search