none
Pooling Data on schedule base using WCF-SQL Adapter RRS feed

  • Question

  • There is a requirement to pooling data from SQL Server in certain times like 10:00 AM,2 PM,5 PM .Usually for pooling data we use WCF-SQL Adapter.So how to acheive this requirement useing  WCF-SQL Adapter(receive)

    Regards BizTalkWorship

    Monday, February 9, 2015 6:24 AM

Answers

  • Unfortunate the scheduled BizTalk receive Adapters (incl. both WCF-SQL and SQL) do not hold such logic.

    They are schedule based, which means that they can be set to poll every hour, every 2nd. hour and so on. But this is not on a fixed time.

    Also all Receive Locations holds the ability to set up a "Service Window", where a time interval can be set up, within where the polling will happen. 

    These things combined will not meet your requirements, but what you can do is implement some logic in the Stored Procedure you are polling from so that it will only release data on the given times, and then set up a relative short polling interval on the Receive Location.

    Morten la Cour

    • Marked as answer by Angie Xu Monday, February 16, 2015 3:07 AM
    Monday, February 9, 2015 6:40 AM

All replies

  • Unfortunate the scheduled BizTalk receive Adapters (incl. both WCF-SQL and SQL) do not hold such logic.

    They are schedule based, which means that they can be set to poll every hour, every 2nd. hour and so on. But this is not on a fixed time.

    Also all Receive Locations holds the ability to set up a "Service Window", where a time interval can be set up, within where the polling will happen. 

    These things combined will not meet your requirements, but what you can do is implement some logic in the Stored Procedure you are polling from so that it will only release data on the given times, and then set up a relative short polling interval on the Receive Location.

    Morten la Cour

    • Marked as answer by Angie Xu Monday, February 16, 2015 3:07 AM
    Monday, February 9, 2015 6:40 AM
  • We need to pull data from 3 receive locations with single ReceivePort.all 3 locations using WCF-SQL Adapter.

    The schedule to Be like below

    1. Interface 1-  10 AM ,12 PM,2 PM 

    2. Interface 2 - 10 AM

    3.Interface 3- 3 PM,5 PM

    The above scenario to be executed in Cluster Environment. The Host Instance of WCF-SQL Adapter is windows CLustered.same host instance mapped for all 3 locations

    Please some advise how to implement schedule for those scenarios


    • Edited by BTSworship Monday, February 9, 2015 9:00 AM
    Monday, February 9, 2015 8:55 AM
  • Hi, Please check the scheduled task adapter available on code plex. Thanks

    JB

    Monday, February 9, 2015 9:03 AM
  • Hi,

    you can do this using the BizTalk schedule task adapter.


    Bali ... Mark As Answer or Vote As Helpful if this helps.

    Monday, February 9, 2015 10:11 AM
  • Hi BTS,

    BizTalk is not a job scheduler, there is the option to schedule receive location (start date, end date and time) and send port start and end time (service window). But there is no flexibility when it comes to scheduling at specific time on a daily, weekly or monthly basis.

    Your best bet is to use BizTalk Scheduled Task Adapter.

    Download the adapter from : http://biztalkscheduledtask.codeplex.com/

    Installation guide : http://sandroaspbiztalkblog.wordpress.com/2012/06/12/biztalk-scheduled-task-adapter-4-0-introduction-and-installation-process/

    Article that intends to show how we can schedule a process using the BizTalk Scheduled Task Adapter.

    BizTalk Server : Scheduling Orchestration using Trigger Message

    For other option refer: Implementing scheduling requirements in BizTalk solutions

    Rachit

     


    Please mark as answer or vote as helpful if my reply does


    Monday, February 9, 2015 10:13 AM
    Moderator
  • As noted, BizTalk is generally not a job scheduler.

    There are two ways I would consider approaching this, both using the SQL Agent* as the scheduler.

    A) Create a Flag table with a field indicating data is ready for source A, B and C.  Create Agent Jobs that set this field to 1 at the appropriate times.  Configure the WCF SQL Adapter to poll at a reasonable rate, every 60 seconds perhaps.  Use the polledDataAvailableStatement to query this value.

    B) Use an SSIS Package scheduled by the SQL Agent to export the data to an Xml file where is is picked up be a FILE Adapter Receive Location.

    *The Windows Scheduler an equally good scheduler, but the SQL Agent is already used in the stack.

    Monday, February 9, 2015 1:25 PM
    Moderator