Thursday, August 1, 2013

Polling data from SQL using WCF-SQL Adapter in BizTalk 2010 - TypedPolling(From Multiple table)

Starting from where I had left:

http://tech-findings.blogspot.co.uk/2013/07/insert-records-in-sql-server-using-wcf.html

Now it's time to look at how we can poll data from multiple tables - SQL.

Scenario: We need to send an Acknowledgement against the purchase order we  got and to do so we need to  poll data from two tables viz. Header and Detail table.

Let's start:

1. Right click the project and  select Add-->Add Generated Items
2. Then select "Consume Adapter Service"
3. Now the Consume Adapter Service wizard wants us to provide it the required information, so firstly select "sqlbinding" against the label Select a binding.
4. Click the Configure button, on security tab select the Client credential type ( I have used windows as it is my login type to SQL )
5. On URI Properties tab:
      i.Type the name of the server where your database is located ( I have used "." as the database resides on same machine)
     ii. Provide the name of the database you want to use.
    iii. In front of InboundId (It is mandatory for Typed polling) , type the name whatever you want so as to  identify the purpose of polling. It acts  as a unique identifier which helps in avoiding conflict when you poll same tables for some other purpose. Here am using "Ack" as the data polled will be used for generating Acknowledgement and  I will be polling from same for generating invoice and for that I will use "Inv" as InboundId.
   iv. I have left InstanceName as blank because am using the default instance of SQL (If need to use some other instance then provide the name of that instance)

6.On Binding Properties tab:
    i. Select TypedPolling  from the dropdown list in front of InboundOperationType
   ii. To check whether the data that we are interested to poll is present, we query in the table and for that            we  use PolledDataAvailableStatement, I have use following query:


          select count(*) from HDR4201 where ACKFlag is NULL


  iii. After checking is done for available data next is to poll it and for that we use PollingData , I have used following query (also stored procedure can be used):

  Select                  H.OrderID,H.OrderDate,H.BilltoID,H.BillToName,H.BillToCity,H.BillToState,H.BillToPostal,H.BillToCountry,H.ShipToID,H.ShipToName,H.ShipToCity,H.ShipToState,H.ShipToPostal,H.ShipToCountry,H.Comments,H.TotalAmount,H.TotalTax,H.Currency,H.DOCO,D.RequestDate,D.Quantity,D.UnitOfMeasure,D.ItemID,D.UnitPrice,D.LineComment FROM HDR4201 as H,DTL4211 as D Where H.OrderID=D.OrderID AND H.DOCO=D.Doco AND H.OrderDate=D.OrderDate AND H.ACKFlag is NULL;update HDR4201 set ACKFlag = 'Y' where ACKFlag is NULL

iv.How frequent we want to check the table and poll data if available can be set using PollingIntervalsInSeconds (default is 30 seconds)



v. Click ok 

7. On the first page click the Connect Button and do the following:
i. Select  Service (Inbound operations) as contract type under Select Contract Type
ii. Now under the select a category pane click the forward slash (../) and you should see the next pane i.e. Available categories and operations get populated
iii. Select TypedPolling and click Add button just below the pane.
iv.Filename prefix is optional but it's good to enter a value as it helps in categorizing the schema which will be generated. (I have used PolledData) . Thus my schema will have a name: PolledData_Ack.xsd
v.Click ok and you should see an XSD and an Orchestration added to your project.

8. Well you can go ahead and use that orchestration to receive the polled data and do further processing (will be posting about it in next post).
9. For this post I will create a receive port which will get polled data and a send port which subscribe to the message from the receive port.
10. Sign the project, build and deploy the project.
11. Open BizTalk admin Console and under your application go to the Receive Ports and create new receive port with :
i. Receive Location having Transport Type set as WCF-SQLAdapter
ii. Click configure button next to it, and repeat the process from above step 5- 7 
ii. Receive pipeline as XMLReceive 
12. Now create a send port with a Transport type as File and provide a location and : 
add a filter on the send port:  BTS.ReceivePortName=="ReceivePortName"
13. Now test the application. The polled data should look like below:




Will keep on posting as an when I find something to share!!!!!!!!!!!!

14 comments:

  1. Thank you, This is straight away, Among all articles.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. hi,
    i am unable to generate schema,its is showing as unnamedcolumn0,here i used polling statement to execute a pocedure,could you please check this

    ReplyDelete
  4. i m not unable to poll from stored procedure

    ReplyDelete
  5. i m unable to excute stored procedure in polling statement,can u please suggest me..

    ReplyDelete
  6. i m unable to excute stored procedure in polling statement,can u please suggest me..

    ReplyDelete
  7. Where exactly you are facing problem? At which step? Did you miss some steps - please go through the steps once again.

    ReplyDelete
  8. Hello friends Iam new to biztalk , I need a scenario like retreiving data from database by using procedure which has only select statement and convert that to flat file by using biztalk. Can any one please help me out.

    ReplyDelete
  9. Hi Mahesh,

    Please help me, I have to poll the data from 15 Tables, Table by Table, should not join in to single select statement. For example, if I have 15 Tables to poll, I have to poll Table1 and store as XML and i have to poll Table 2, then 3, 4, 5...
    Multiple Table polling by looping, how to do it dynamically poll the data Table by Table...
    Please provide me the solution? its very urgent...

    Thanks
    Vinoth

    ReplyDelete
  10. Hi Vinoth,

    Below is the link -
    http://tech-findings.blogspot.com/2015/04/fetching-data-from-multiple-tables.html

    ReplyDelete
  11. Hi Mahesh,
    Thank you for your post.
    When polling the information, I need to separate the details table information in separate element as 1 order can contain multiple lines.


    Order123
    Central, Hyderabad


    ItemID1
    Item Description1
    3


    ItemID2
    Item Description2
    5


    ReplyDelete
  12. Hi Venu,

    I have written an article for that : http://social.technet.microsoft.com/wiki/contents/articles/18845.biztalk-server-2010-grouping-and-debatchingsplitting-inbound-messages-typedpolled-from-wcf-sql-adapter.aspx

    ReplyDelete
  13. Thank you Mahesh.... the article is great and easy to follow.

    ReplyDelete