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.
