There are situations in my integration architecture where I need to stage certain messages that come through my integration layer due to a possible relationship with the staged message to another message that may come through later on, although never guaranteed. To offer more detail, the publishing application manages an 'EmployeeInfo' base of all employees within the organization. A handful of these employees MAY be assigned to certain roles, we'll label as 'EmployeeRole', within the organization. At any given time, I receive the EmployeeInfo message when an employee record is added, modified or deleted from the source application so I do maintain a full staging environment of all of these records and maintain the current state of the data.
For the sake or argument, an EmployeeInfo record contains simply the contact information for the employee in question, but no assignment information as far as what role they are assigned to, or even what division the assignment belongs to....we have seven separate divisions, and yes, they may be assigned to multiple divisions. Here's a peek at how the first staging table looks with some data for demonstration:
Employee_Information
first_name last_name phone cell emp_id expired active
---------------------------------------------------------------------------------------------------------------
Rich Wallace 4805551212 4805551234 2549 0 1
John Dude 4805554321 4805552121 2550 0 1
My Employee_Information staging table is updated anytime I receive an EmployeeInfo feed per a SP call via Biztalk Server and this data is maintained throughout the history of the employee. Now that I have my details, we see that there is no way to know, if an employee is assigned to a role, where the employee data is to link to for the subscribing applications that be need to know where the employee is assigned. Thus, the information staging solution...as again, the employee may NEVER be assigned to a role but I'll never know that.
I have a second staging table, called Employee_Assignment, which is updated anytime the publishing application publishes an actual assignment. It is this message that will tell me what division(s) the employee is assigned to and what the direct assignment actually is so I can finally send the required message along to the subscribed applications. Here is the second staging table:
Employee_Assignment
emp_id division_id corp_code expired active
----------------------------------------------------------------------------------
2549 1 60 0 1
2549 1 80 0 1
2549 2 60 0 1
2550 1 60 0 1
In the Employee_Assignment table, what's going on here is that the 'Rich Wallace' employee is assigned to three (3) roles, two roles in division 1 and another role in division 2 and 'John Dude' is assigned to a single role in division 1. With these staging tables in place, it is now up to the integration layer to make the necessary relationships and build the feeds for the subscribing applications. Maybe not the correct solution in a 'perfect' world, but...
Now we need to build the stored procedure that will extract the necessary data from these tables when either an EmployeeInfo record or EmployeeRole record comes through as to maintain any updates to either record and ensure that our subscribers are given accurate data as well. Since SQL Server plays fairly nice with XML, and Biztalk loves to cuddle with XML, this is actually pretty easy when we use SQL Server's 'FOR XML' clause in our SELECT statement. Since everything involed here revolves around an employee ID value, I can use a parameterized procedure to only pull the data I need and not rely on a polling solution, which can get ugly.
In my procedure, I have the @emp_id parm exposed as I only want to get any/all assignments for the employee that is tied to the incoming EmployeeInfo/EmployeeRole record. There is an INNER JOIN on these tables to ensure I get all the necessary data, and if there is no matching Employee_Assignment record for the Employee_Information record, I obviously don't need to send anything for the subscribers as they ONLY care about valid assignments. Note here, that the Employee information is only saved at the time of the assignment so they don't all have an Employee base table with possible useless data. Here is the base query used in the procedure, prior to any XML logic:
Query:
SELECT
division_id,
first_name,
last_name,
phone,
cell,
email,
emp_id,
corp_code,
CASE
WHEN employee_expired = 0 AND employee_active = 1
AND assignment_expired = 0 AND assignment_active = 1
THEN 1 ELSE 0
END as record_active
FROM
Employee_Information I
INNER JOIN
Employee_Assignment A ON I.emp_id = A.emp_id
WHERE
division_id IS NOT NULL
AND
I.emp_id = 2549
Results
---------------------------------------------------------------------------------------------------
1 Rich Wallace 4805551212 4805551234 2549 60 1
1 Rich Wallace 4805551212 4805551234 2549 80 1
2 Rich Wallace 4805551212 4805551234 2549 60 1
Now we have our results from the staging tables, but this doesn't do us any good as far as Biztalk is concerned (unless you choose to write custom helper classes) so we need to get them into XML for submission to Biztalk. Since we have more than one table in this query and we need the XML records to truly keep a 'transaction' based format, the best method when using SQL's FOR XML clause it to tack on the RAW attribute. This will 'flatten' the JOIN result into a single XML per record, rather than creating a parent/child heirarchy if using the AUTO attribute. Sooo, let's add the XML logic to the end of the query and see what we get:
Query:
SELECT
division_id,
first_name,
last_name,
phone,
cell,
email,
emp_id,
corp_code,
CASE
WHEN employee_expired = 0 AND employee_active = 1
AND assignment_expired = 0 AND assignment_active = 1
THEN 1 ELSE 0
END as record_active
FROM
Employee_Information I
INNER JOIN
Employee_Assignment A ON I.emp_id = A.emp_id
WHERE
division_id IS NOT NULL
AND
I.emp_id = 2549
FOR XML RAW, ELEMENTS
Results
<row>
<division_id>1</division_id>
<first_name>Rich</first_name>
<last_name>Wallace</last_name>
<phone>4805551212</phone>
<cell>4805551234</phone>
<email>somewhere@isp.com</email>
<emp_id>2549</emp_id>
<corp_code>60</corp_code>
<record_active>1</record_active>
</row>
<row>
<division_id>1</division_id>
<first_name>Rich</first_name>
<last_name>Wallace</last_name>
<phone>4805551212</phone>
<cell>4805551234</phone>
<email>somewhere@isp.com</email>
<emp_id>2549</emp_id>
<corp_code>80</corp_code>
<record_active>1</record_active>
</row>
We now have the results of our query in XML format...two concerns though:
- The XML records have the root node of 'row', which isn't very descriptive when it will come to how you may want to handle the messages in Biztalk if you're using message based routing.
- The actual XML result is not well formed as there are no wrapper tags around the group of messages
In regards to concern #1, I utilize message based routing and subscriptions within Biztalk by using direct bound filters on my orchestrations and ports, and the message type of 'row' just isn't descriptive enough for me to organize my schemas. Thankfully, we can control the name of the root node of each message within the query very easily by changing the last line of the query from:
FOR XML RAW, ELEMENTS
to:
FOR XML RAW ('Employee_Assignment'), ELEMENTS
For concern #2, this is where the SQL Adapter can help us out since it provides us with the wrapper when the SQL Adapter calls the stored procedure. Then, when the XML document is retrieved and placed into the message box, you have the option of processing the message in your desired fashion.
Good luck!
Posted
08-22-2008 11:38 AM
by
Rich Wallace