BizTalk Application to look up a value in the database and return true or false message RRS feed

  • Question

  • Am given a task:

    The task is to recieve a message with field or element called CodeSet and after recieving this message i must lookup into the data base table to find out if this Codeset  exist, if it does not  i must return a message with a field or elentment saying true or false.

    This must be a BizTalk project.

    Please assist

    thanks and regards

    Lesibana Chokoe

    Wednesday, January 24, 2018 9:59 AM

All replies

  • You have to options:

    1st) My preferred option would be a message-only solution:

    Create a Map where source schema is your received message and destination is the message to return. In the map use a combination of "Database Lookup", "Value Extractor" and "Value Mapping" functoids in order to look up for the Codeset and return one result or another.

    Make a Receive Port with Inbound Map using the recently created map.

    After you have created the message to return, make a Send Port with a filter to subscribe for the Message Type of your destination schema.

    2nd) Use a Orchestration in order to receive and send the desired messages. There you can use a map as described before or you can use custom C# code to have the job done.

    Wednesday, January 24, 2018 12:15 PM
  • Here is what you can do 

    1) Create a Schema which accepts the input paramter say UserId

    2) Create a stored procedure which will return 1 or 0 ( or true or false) based upon the existence of the UserID

    The stored procedure can look something like belo

    Create procedure CheckSentimentScore
    @UserId nchar
    set nocount on;
    Declare @Exist int 
    	if exists(select SentimentScore from UserFeedBackAnalysis
    						where UserID =  @UserId)
    			set @Exist = 1
    			Set @Exist = 0
    	return @Exist

    3) Consume the stored procedure on the BizTalk project using the Consume Adapter Service. Refer 

    BizTalk – SQL Integration : Part 1: WCF CUSTOM Adapter 

    WCF-SQL Adapter Stored Procedure  

    Refer following screen shot generated which shows the schemas generated for above stored proc.

    4) Map the ID from the input to the id in the request schema generated.

    5) Map the response from SQL to the output of BizTalk project

    6) Create an orchestration that will handle all above steps.

    Mandar Dharmadhikari

    Wednesday, January 24, 2018 12:33 PM
  • Hi Lesibana

    Any Update for us on the issue?

    Mandar Dharmadhikari

    Tuesday, January 30, 2018 10:05 AM