This is an very basic and simple task, but I did not find any useful article to explain step by step process of extracting schema and Configuration part in BizTalk side while consume Oracle Table/View/Stored Proc. Apparently BizTalk receive location configuration.
After three days of trail I could achieve this simple task. Hope this can give you a clear picture of how to extract a Table data using simple SQL Statement in BizTalk.
Make sure you install BizTalk Adapter Pack 2.0 and Oracle Client before you start this example.
To install BizTalk Adapter Pack 2.0 :
http://www.microsoft.com/biztalk/en/us/adapter-pack.aspx
Basic configuration settings for Oracle Database :
http://rajwebjunky.blogspot.com/2010/07/biztalk-adapter-pack-working-with.html
Apparently configure the Oracle Host & Host Instance at BizTalk Admin Console.
Task: I am going to perform a retrieve data from LoanMaster and result to be converted into XML file and drop into my Local Folder, program flow described in below flow chart.
Step1: First you need to generate Table/View/Stored Proc schema in BizTalk application, This is an standard procedure which we follow for SQL Server or any other database.
However for Oracle database it has additional configuration settings to be done and you should generate a schema for POLLINGSTMT operation. Otherwise you will end up with many errors.
Right click your project and select “Add Generated Items” and select “Consume Adapter Service” from Left pane and select “Consume Adapter Service” from Right pane and Click OK
Key things to follow while Generating the Schema for POLLINGSTMT
1. Polling ID = 23 (can be any number but same number should mention in
ReceiveLocation configuration settings)
2. PollingStatement = Select * from LoanMaster
3. Select the contract type as Service (Inbound operation)
4. Click Root (/) and select POLLINGSTMT operation in right list boxall the above steps shown in below screenshots
Step2:Finally you can find a Schema to be generated in you Project and that will have a POLLINGSTMT Node along with you will also find POLLINGSTMTRECORD, ArrayOfPOLLINGSTMTRECORD nodes. I am not going to discuss about other two nodes in this article.
while Creating a corresponding message at Orchestration view (Message_1) assign the POLLINGSTMT node as your Incoming Message.
Step3: Orchestration looks like below
Step4: Receive Location1
At BizTalk Receive Location side you need to perform same configuration settings which you have mentioned in Step-1
Polling ID =23 ( what ever number you mentioned in Step1- same Number should use here)PollingStatement = Select * from LoanMasterNow Build and test your application. Hope you might get some warning messages, ignore it and check your Local Folder for Output.
At BizTalk Receive Location side you need to perform same configuration settings which you have mentioned in Step-1
Polling ID =23 ( what ever number you mentioned in Step1- same Number should use here)PollingStatement = Select * from LoanMasterNow Build and test your application. Hope you might get some warning messages, ignore it and check your Local Folder for Output.
Reach me if you need any clarifications @ raj.webjunky@yahoo.com
No comments:
Post a Comment