Execute Stored Procedure only Once, hard code value

Question

I have 50 Biztalk packages and I need to log in a separate table when the package executes.

I am very aware that Biztalk database keeps a log, therefore don't provide that as a solution.

My table definition:

CREATE TABLE [dbo].[BiztalkLog]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [BiztalkPackageID] [int] NULL,
    [RunDate] [datetime] NULL,

    CONSTRAINT [PK_BiztalkLog] 
       PRIMARY KEY CLUSTERED ([ID] ASC)
           WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
                 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
                 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

My stored procedure:

CREATE PROCEDURE [dbo].[InsertBiztalkLog]
    @BiztalkPackageID AS INT
AS 
    INSERT INTO dbo.BiztalkLog(BiztalkPackageID)
        SELECT @BiztalkPackageID
GO

Questions:

1. How do I hard code the BiztalkPackageID value inside the orchestration? So when the stored procedure executes once, it will know which value to pass.

2. How do you execute a stored procedure only once inside an orchestration?

Answer 1

Create an instance of your Stored Procedure Call XML message; since this is a very small and simple message, I'd set it up as a template like so in a C# helper class:

public static string biztalkLogTemplate = @"<ns0:BiztalkLog xmlns:ns0=""http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo"">
  <ns0:BizTalkPackageID>{0}</ns0:BizTalkPackageID>
</ns0:BiztalkLog>";

public static XmlDocument GetBiztalkLog(int packageId)
{
   XmlDocument xdoc = new XmlDocument();
   xdoc.LoadXml(string.Format(biztalkLogTemplate, packageId));
}

Then, in an orchestration add a message (msgLogProc) set to use the schema for your stored procedure, and in a construct message expression:

msgLogProc = GetBiztalkLog(1); // whatever ID this orchestration is supposed to use.

You can then set up a port in the orchestration and bind it (direct or static) to the send port for your SQL procedure. Use a Send shape to send the msgLogProc to that port. If you do this send once before your loop shape, the procedure will only get called once per orchestration execution. If you need it to be once by some different standard (only the first execution of a day or a batch?), you need a different pattern. You'd likely want to handle this on the SQL side though, persisting whether or not the orchestration has already logged something for that particular batch (maybe using an anti-join, i.e. INSERT ... WHERE NOT EXISTS (...).

There are several other ways to construct messages in orchestrations, but I find this to be the most straightforward to deal with for small scenarios like this one.