In some scenarios, it is required to store the message which is received from sender along with 07ansforming it to the destination format.  One such scenario is to store the RawXml (Whole Incoming XML Message) in database as it is. To demons07ate this, used the Scenario : Order Message is received and we are to store the OrderId and the whole Order in database. For that I have created a TestDb, a table Order with two fields Id and RawXML. Input Schema: For the purpose of demo, I have kept only three fields in the input schema, and ID field is marked as distinguished as it will be used while cons07ucting Outbound Message. Destination Schema: I have a table created in TestDb database in to which the Order Id of the incoming message and the Order as it is received  i.e. whole incoming Message is inserted. To do so we need to create a destination schema for BizTalk but based on already defined table thus we would use Add Generated Items -> Consume...

Configuring the Application and Testing After Developing and  deploying the application start with creating ReceivePort with a File based receive location. Send port which will send data to SQL, in this case you need to use WCF-SQL adapter or WCF-Custom adapter can be used. 1. PassThru pipeline is used as no processing is to be done . 2. SQL binding is to be selected as WCF-Custom adapter is used, if WCF-SQL was used then it would have already been there. After both Receive port and Send Port are created, bind it to the Orches07ation. Right click the orches07ation and configure. Testing After done with configuration, time to test the application. Start the application and drop the sample Order File at Receive Location and you should be able to see in SQL as shown below. Related Post   BizTalk Server: Multiple XML files to Single FlatFile Using File Adapter BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL...

It is a very common scenario where  a Purchase Order is received and data within it is inserted in multiple tables , viz. Header, Item, Address tables etc. How to insert in SQL using WCF-SQL adapter can be seen at : http://tech-findings.com/2013/07/insert-records-in-sql-server-using-wcf.html http://tech-findings.com/2013/07/insert-records-in-sql-server-using-wcf_5.html  And when it comes to send Ack , Ship notification or Invoice we need to poll data from different tables with the help of unique Id (most of time its the OrderId along with some other field) which minimizes the risk of duplicate en07ies. How to poll from SQL using WCF-SQL adapter can be seen at: http://tech-findings.com/2013/08/polling-data-from-sql-using-wcf-sql.html Scenario for this post:   We polled data for order and as it has many items ,thus we get multiple  set of  repeating nodes with each node having Header data(which is common for all items) and Item data. But we want...

In this post we will see how to Insert records in SQL tables without using Orches07ation . The 07ansformation of the message is done at Receive port. The idea to implement this s07uck me , when I was composing my other post(so most of the part will be common ) :  http://tech-findings.com/2013/07/insert-records-in-sql-server-using-wcf.html Scenario is I receive Purchase Order and need to insert in SQL tables viz. 1. Header table(Parent) 2. Details table(Child) First thing is to create Tables and Stored Procedures which we will call to insert values in the tables. Header Table Create Script: CREATE TABLE [dbo].[HDR4201]( [RequestDate] [date] NOT NULL, [OrderID] [nchar](50) NOT NULL, [OrderDate] [datetime] NOT NULL, [BillToID] [nchar](50) NOT NULL, [ShipToID] [nchar](50) NOT NULL, [ACKFlag] [char](1) NULL, [SNFlag] [char](1) NULL, [BillToName] [nchar](50) NOT NULL, [BillToCity] [nchar](50) NOT NULL, [BillToState] [nchar](50) NOT NULL, [BillToPostal] [...

As said in the last post of mine( http://tech-findings.com/2013/06/no-valid-adapters-installed-on-machine.html )... I am back with the progress. Allright so lets begin.... Scenario is I receive Purchase Order and need to insert in SQL tables viz. 1. Header table(Parent) 2. Details table(Child) First thing is to create Tables and Stored Procedures which we will call to insert values in the tables. Header Table Create Script: CREATE TABLE [dbo].[HDR4201]( [RequestDate] [date] NOT NULL, [OrderID] [nchar](50) NOT NULL, [OrderDate] [datetime] NOT NULL, [BillToID] [nchar](50) NOT NULL, [ShipToID] [nchar](50) NOT NULL, [ACKFlag] [char](1) NULL, [SNFlag] [char](1) NULL, [BillToName] [nchar](50) NOT NULL, [BillToCity] [nchar](50) NOT NULL, [BillToState] [nchar](50) NOT NULL, [BillToPostal] [nchar](50) NOT NULL, [BillToCoun07y] [nchar](50) NOT NULL, [ShipToName] [nchar](50) NOT NULL, [ShipToCity] [nchar](10) NOT NULL, [ShipToState] [nchar](50) NOT NULL, ...