I start off
the process with either a BizTalk Scheduler (http://biztalkscheduledtask.codeplex.com/releases/view/50363) or a manual file drop of
the XML message.
The manual file drop is to allow
the SQL
Job to call a "File Copy" SSIS step to copy
the trigger file
for the next process and allows SQL
Job to be linked back into BizTalk processing.
The Process Trigger XML looks like
the following. It is basically
the configuration hub of
the business process
<ns0:MsgSchedulerTriggerSQLJobReceive xmlns:ns0="urn:com:something
something">
<ns0:IsProcessAsync>YES</ns0:IsProcessAsync>
<ns0:IsPermissionRequired>YES</ns0:IsPermissionRequired>
<ns0:BusinessProcessName>Data Push</ns0:BusinessProcessName>
<ns0:EmailFrom>
[email protected]</ns0:EmailFrom>
<ns0:EmailRecipientToList>
[email protected]</ns0:EmailRecipientToList>
<ns0:EmailRecipientCCList>
[email protected]</ns0:EmailRecipientCCList>
<ns0:EmailMessageBodyForPermissionRequest>This message was sent to request permission to start
the Data Push process.
The SQL Job to be run is WeeklyProcessing_DataPush</ns0:EmailMessageBodyForPermissionRequest>
<ns0:SQLJobName>WeeklyProcessing_DataPush</ns0:SQLJobName>
<ns0:SQLJobStepName>Push_To_Production</ns0:SQLJobStepName>
<ns0:SQLJobMinToWait>1</ns0:SQLJobMinToWait>
<ns0:PermissionRequestTriggerPath>\\server\ETL-BizTalk\Automation\TriggerCreatedByBizTalk\</ns0:PermissionRequestTriggerPath>
<ns0:PermissionRequestApprovedPath>\\server\ETL-BizTalk\Automation\Approved\</ns0:PermissionRequestApprovedPath>
<ns0:PermissionRequestNotApprovedPath>\\server\ETL-BizTalk\Automation\NotApproved\</ns0:PermissionRequestNotApprovedPath>
</ns0:MsgSchedulerTriggerSQLJobReceive>
Every node of this schema was promoted to a distinguished field so that
the values can be used
for decision making in
the orchestration.
The first decision made is on
the "IsPermissionRequired" field.
If permission is required (IsPermissionRequired=="YES"), BizTalk will use
the configuration info in
the XML trigger to format
the email message. Here is
the snippet of how
the email message is constructed.
SQLJobEmailMessage.EmailBody
= new Eai.OrchestrationHelpers.XlangCustomFormatters.RawString(
MsgSchedulerTriggerSQLJobReceive.EmailMessageBodyForPermissionRequest +
"<br><br>" +
"By moving
the file, you are either giving permission to
the process, or disapprove of
the process." +
"<br>" +
"This is
the file to move: \"" + PermissionTriggerToBeGenereatedHere +
"\"<br>" +
"(You may find it easier to open
the destination folder first, then navigate to
the sibling folder to get to this file)" +
"<br><br>" +
"To approve, move(NOT copy)
the file here: " + MsgSchedulerTriggerSQLJobReceive.PermissionRequestApprovedPath +
"<br><br>" +
"To disapprove, move(NOT copy)
the file here: " + MsgSchedulerTriggerSQLJobReceive.PermissionRequestNotApprovedPath +
"<br><br>" +
"
The file will be IMMEDIATELY picked up by
the automated process. This is normal. You should receive a message soon that
the file is processed." +
"<br>" +
"Thank you!"
);
SQLJobSendNotification(Microsoft.XLANGs.BaseTypes.Address) = "mailto:" + MsgSchedulerTriggerSQLJobReceive.EmailRecipientToList;
SQLJobEmailMessage.EmailBody(Microsoft.XLANGs.BaseTypes.ContentType) = "text/html";
SQLJobEmailMessage(SMTP.Subject) = "Requesting Permission to Start
the " + MsgSchedulerTriggerSQLJobReceive.BusinessProcessName;
SQLJobEmailMessage(SMTP.From) = MsgSchedulerTriggerSQLJobReceive.EmailFrom;
SQLJobEmailMessage(SMTP.CC) = MsgSchedulerTriggerSQLJobReceive.EmailRecipientCCList;
SQLJobEmailMessage(SMTP.EmailBodyFileCharset) = "UTF-8";
SQLJobEmailMessage(SMTP.SMTPHost) = "localhost";
SQLJobEmailMessage(SMTP.MessagePartsAttachments) = 2;
After
the Permission request email is sent,
the next step is to generate
the actual Permission Trigger file. A correlation set is used here on SQLJobName and a newly generated GUID field.
<?xml version="1.0" encoding="utf-8"?><ns0:SQLJobAuthorizationTrigger xmlns:ns0="somethingsomething"><SQLJobName>Data Push</SQLJobName><CorrelationGuid>9f7c6b46-0e62-46a7-b3a0-b5327ab03753</CorrelationGuid></ns0:SQLJobAuthorizationTrigger>
The end user (the human intervention piece) will either grant permission
for this process, or deny it, by moving
the Permission Trigger file to either
the "Approved" folder or
the "NotApproved" folder. A parallel Listen shape is waiting
for either response.
The next set of steps decide how
the SQL Job is to be called, or whether it is called at all. If permission denied, it simply sends out a notification. If permission is granted, then
the flag (IsProcessAsync) in
the original Process Trigger is used.
The synchonous part is not really synchronous, but a loop timer to check
the status within
the calling stored procedure (for more information, check out my previous post: http://geekswithblogs.net/LifeLongTechie/archive/2010/11/01/execute-sql-job-synchronously-for-biztalk-via-a-stored-procedure.aspx) If it's async, then
the sp starts
the job and BizTalk sends out an email.
And of course, some error notification:
Footnote:
The next version of this orchestration will have an additional parallel line near
the Listen shape with a Delay built in and a Loop to send out a daily reminder if no response has been received from
the end user.
The synchronous part is used to gather results and execute a data clean up process so that
the SQL Job can be re-tried. There are manu possibilities here.
