I'm working on a ticketing system application and it's database is managed external by a client.

I want to integrate this ticketing system application with my internal sql database.

The ticketing system doesn't have a date and time column where you can specify in a store procedure to pull only recent ticket that gets created, therefore it pulls all the records from the ticketing system and dumps all the data in my internal sql database. The client cannot change it's database based on it's policy so I'm stuck polling all records every time a new ticket gets created.

Is there a chance that Biztalk can pull these records, sends them to Biztalk database then have a receive location pointing to Biztalk database then pulling only recent record then sending it to my internal sql database? Can this be done? Does this even make sense? Any hint?

There are several possible patterns.

See this Wiki Article and the section on Polling a Database We Don't Own: http://social.technet.microsoft.com/wiki/contents/articles/24803.biztalk-server-sql-patterns-for-polling-and-batch-retreive.aspx

However, how you can do this depends on a number of factors.

There are several possible patterns.

See this Wiki Article and the section on Polling a Database We Don't Own: http://social.technet.microsoft.com/wiki/contents/articles/24803.biztalk-server-sql-patterns-for-polling-and-batch-retreive.aspx

However, how you can do this depends on a number of factors.

• Marked as answer by Chris7887 17 hours 27 minutes ago

There are several possible patterns.

See this Wiki Article and the section on Polling a Database We Don't Own: http://social.technet.microsoft.com/wiki/contents/articles/24803.biztalk-server-sql-patterns-for-polling-and-batch-retreive.aspx

However, how you can do this depends on a number of factors.

• Marked as answer by Chris7887 Monday, March 30, 2015 1:36 PM

Hi Chris,

In your case specifically I would advise you to stay away from calling SQL directly from BizTalk. Instead go with MSMQ integration.

Refer: http://stackoverflow.com/questions/8853754/using-msmq-when-you-already-have-sql-server-and-biztalk

http://geekswithblogs.net/LeonidGaneline/archive/2011/01/06/biztalk-and-sql-alternatives-to-the-sql-receive-adapter.-using.aspx