|
Home > Archive > BizTalk Server Applications Integration > April 2006 > Can BizTalk monitor a value in a database column?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Can BizTalk monitor a value in a database column?
|
|
|
| Hello, I have a requirement to trigger a orchestration if inventory
becomes available for a product.
My DB table structure is
Product_ID & QTY
I need to monitor the QTY field and when it changes, I need to trigger
an orchestraction with an XML message containing PROUCT_ID and QTY.
Can BTS monitor specific column? If yes, could you give me outline on
how this can be accomplished? Thanks for the response...Ram
| |
| Lucas R. Vogel 2006-04-27, 7:25 am |
| One approach I can think of to this situation is based on the assumption you
can add either a timestamp column, or a boolean flag, indicating that the row
has been updated. With the timestamp you can poll to see if the last update
time is less than your polling interval; with your boolean column you simply
check to see if the row is 'dirty', at which point you create a kind of
"round-robin" orchestration that performs processing based on the 'dirty'
record and updates it as clean upon completion of the process.
If changing the table schema is not an option, you can create a .NET console
application, configured as a Scheduled Task, that polls the row on a set
interval and checks the value against its last polled value (serialized
within the application). If the value is different from the last polled
value, you can have your .NET app create and submit a message into BizTalk
and have your business process start that way.
Two approaches - I'm sure there are more, but these should get you up and
running fairly quickly...
Hope this helps!
Lucas
--
===========
Lucas R. Vogel
"Ram" wrote:
> Hello, I have a requirement to trigger a orchestration if inventory
> becomes available for a product.
> My DB table structure is
>
> Product_ID & QTY
>
> I need to monitor the QTY field and when it changes, I need to trigger
> an orchestraction with an XML message containing PROUCT_ID and QTY.
>
> Can BTS monitor specific column? If yes, could you give me outline on
> how this can be accomplished? Thanks for the response...Ram
>
>
| |
| Adriaan Grobler 2006-04-28, 1:20 am |
| You can use the Biztalk SQL adpater which calls a stored proc. you need to
add an extra column to the table which will be the control field to ensure
you do not grab the same record twice.
The stored proc should look somthing like this :
CREATE PROCEDURE cp_GetStuff
AS
DECLARE @ProcessDate DateTime
SET @ProcessDate=GetDate()
Update <%TableName%> Set DateProcessed=@ProcessDate Where DateProcessed is
NULL
SELECT <%Columns%>FROM <%Table%> WHERE DateProcessed=@ProcessDate FOR XML
AUTO, ELEMENTS
GO
Configure the SQL Adapter to poll ever couple of minutes
"Ram" wrote:
> Hello, I have a requirement to trigger a orchestration if inventory
> becomes available for a product.
> My DB table structure is
>
> Product_ID & QTY
>
> I need to monitor the QTY field and when it changes, I need to trigger
> an orchestraction with an XML message containing PROUCT_ID and QTY.
>
> Can BTS monitor specific column? If yes, could you give me outline on
> how this can be accomplished? Thanks for the response...Ram
>
>
|
|
|
|
|