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.