Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010- Composite Operation (Message Transformation Pattern)

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] [nchar](50) NOT NULL,
[BillToCoun07y] [nchar](50) NOT NULL,
[ShipToName] [nchar](50) NOT NULL,
[ShipToCity] [nchar](10) NOT NULL,
[ShipToState] [nchar](50) NOT NULL,
[ShipToPostal] [nchar](50) NOT NULL,
[ShipToCoun07y] [nchar](50) NOT NULL,
[Comments] [nchar](999) NULL,
[TotalAmount] [decimal](18, 2) NOT NULL,
[TotalTax] [decimal](18, 2) NOT NULL,
[Currency] [nchar](10) NOT NULL,
[DOCO] [bigint] IDENTITY(1000,1) NOT NULL,
[INFlag] [char](1) NULL,
 CONSTRAINT [PK_HDR4201] PRIMARY KEY CLUSTERED 
(
[OrderID] ASC,
[OrderDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Detail Table Create Script:

CREATE TABLE [dbo].[DTL4211](
[Doco] [bigint] NOT NULL,
[LineNumber] [float] NOT NULL,
[OrderID] [nchar](50) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[RequestDate] [date] NOT NULL,
[Quantity] [int] NOT NULL,
[UnitPrice] [decimal](18, 2) NOT NULL,
[Tax] [decimal](18, 2) NOT NULL,
[TotalLineAmount] [decimal](18, 2) NOT NULL,
[UnitOfMeasure] [nchar](4) NOT NULL,
[ACKFlag] [nchar](1) NULL,
[SNFlag] [nchar](1) NULL,
[CurrentDate] [datetime] NOT NULL,
[ACKSentDate] [datetime] NOT NULL,
[ShipmentDate] [datetime] NOT NULL,
[ItemDescription] [nchar](50) NULL,
[LineComment] [nchar](50) NULL,
[ItemID] [nchar](20) NOT NULL,
 CONSTRAINT [PK_DTL4211] PRIMARY KEY CLUSTERED 
(
[Doco] ASC,
[LineNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 Stored Procedure to insert in Header Table:
CREATE PROCEDURE  [dbo].[uspInsertOrderHeader]
@RequestDate date,
@OrderID nchar(50),
@OrderDate nchar(50),
@BillToID nchar(50),
@ShipToID nchar(50),
@BillToName nchar(50),
@BillToCity nchar(50),
@BillToState nchar(50),
@BillToPostal nchar(50),
@BillToCoun07y nchar(50),
@ShipToName nchar(50),
@ShipToCity nchar(50),
@ShipToState nchar(50),
@ShipToPostal nchar(50),
@ShipToCoun07y nchar(50),
@Comments nchar(999),
@TotalAmount decimal(18,2),
@TotalTax decimal(18,2),
@Currency nchar(10)
AS
BEGIN

SET NOCOUNT ON;

INSERT INTO HDR4201(RequestDate,OrderID,OrderDate,BillToID,ShipToID,BillToName,BillToCity,BillToState,BillToPostal,BillToCoun07y,ShipToName,ShipToCity,ShipToState,ShipToPostal,ShipToCoun07y,Comments,TotalAmount,TotalTax,Currency)
Values(@RequestDate,@OrderID,@OrderDate,@BillToID,@ShipToID,@BillToName,@BillToCity,@BillToState,@BillToPostal,@BillToCoun07y,@ShipToName,@ShipToCity,@ShipToState,@ShipToPostal,@ShipToCoun07y,@Comments,@TotalAmount,@TotalTax,@Currency);

 END

 Stored Procedure to insert in Detail Table:
CREATE PROCEDURE [dbo].[uspInsertOrderDetail] 
@LineNumber float,
@OrderID nchar(50),
@OrderDate nchar(50),
@RequestDate date,
@Quantity int,
@UnitPrice decimal(18,2),
@Tax decimal(18,2),
@TotalLineAmount decimal(18,2),
@UnitOfMeasure nchar(4),
@CurrentDate datetime,
@ACKSentDate datetime,
@ShipmentDate datetime,
@ItemDescription nchar(50),
@LineComment nchar(50),
@ItemID nchar(20)
AS
BEGIN

SET NOCOUNT ON;
DECLARE @doco bigint;
set @doco = (SELECT DOCO FROM HDR4201 WHERE OrderID = @OrderID AND OrderDate =@OrderDate);

INSERT INTO DTL4211(Doco,LineNumber,OrderID,OrderDate,RequestDate,Quantity,UnitPrice,Tax,TotalLineAmount,UnitOfMeasure,CurrentDate,ACKSentDate,ShipmentDate,ItemDescription,LineComment,ItemID)
Values(@doco,@LineNumber,@OrderID,@OrderDate,@RequestDate,@Quantity,@UnitPrice,@Tax,@TotalLineAmount,@UnitOfMeasure,@CurrentDate,@ACKSentDate,@ShipmentDate,@ItemDescription,@LineComment,@ItemID);

  END

 Cool ... Now to make it happen actually ... we need to consume the service which is leveraged by WCF-SQL Adapter

 1.Create BizTalk Project (Name it what you desire)
2.Create schema for the incoming Purchase Order

 3.In schema shown above , ShipTo and BillTo have same s07ucture
4.Now we need the destination schema and let's add it:
  •  Right click on  the project and select Add->Add Generated Items
  • A wizard will pop out and will give you options to select from it.
  • Select Consume Adapter Service ( you can also opt for Add Adapter)
  • Difference is very small and should be opted as per requirement:
  • Consume Adapter Service : Creates schema(XSD) as per adapter metadata
  • Add Adapter Metadata: Creates orches07ation and schema as per WSDL
  • And both will make you go through same Wizard (Shown Below)
  • In Security tab select Windows from drop down list 
  • In URI Properties tab in front of Server
  • 1.Enter "dot(.)" (If your SQL server is on  same machine) 2. Else the name of the server machine
  • I front of InitialCatalog mention your database name





  • After you say OK on the configure adapter page , click connect . And as shown above all the details are filled .
  • It tells you the type of binding ( sqlBinding) , URI(which you configured), con07act type (Client(Outbound operations )) and all the categories and operations available on URI that we configured.
  • As it can be seen above , I have added the operations which I need for inserting into tables from Procedures category. 
  • I have given a prefix (Its not mandatory) which will be prefixed to the schema's we are about to create.
  • After I click OK , two xsd get added to my project(shown below)


6. As you can see we get four root elements here.Now I want to insert OrderHeader and OrderDetails in both the table at once.To do so, I would need a dedicated schema record for Request and for Response. 
So lets add it in the two records namely OrderRequest and OrderRequestResponse.In OrderRequest record add uspOrderInsertHeader and uspOrderInsertDetail.Make sure that you change the max occurrence property of uspOrderInsertDerail to unbounded.

7.The reason for doing this is as we will have multiple line item in a single order.Also we are inserting both header and detail records together .

8.Under OrderRequestResponse add uspOrderInsertHeaderResponse and uspOrderInsertDerailResponse
also make max occurrence property of uspOrderInsertDerailResponse to unbounded.

Note: You can name any thing you want but remember the second record name should be suffixed with Response. Because SQL sends response with  request name suffixed with "Response" 
 example: Suppose you submit request with name "A" then  SQL will send response as "AResponse"



9.Remember to set MaxOccur as unbounded for "uspInsertOrderDetail" , "uspInsertOrderDetailResponse" as this will be multiple times.
10.Now we are ready with the resources required for mapping, lets do it:
11.Do test your map , it is best practice and does help in reducing problems which might arise later.
12. Now Deploy the project.
13. The flow is :
  •  Purchase Order is received at receive port.
  • A map which we saw above will be applied where we 07ansform incoming message to 07aget(SQL) form.
  • The message is published in the MessageBox, thereafter look up for subscription is done and copy of this message is sent to subscriber ( here it will be Send Port which we will create in some time)
14.Now its time to configure the application
  • Create one way Receive port with a file location where you want the input message to be picked
  • And add the map which will 07ansform IncomingOrder to SQL expected form.

  • Create one Send port of type Static One Way Type 
  • Select WCF-SQL Adapter as a Transport type
  • Select XMLTransmit 














  • Click configure
  • Perform same operation as we did at step 4
  • Do not forget to Add Action and here it should be "CompositeOperation".
  • Click ok 


      •

      • Add filter on the send port:  BTS.ReceivePortName==ReceiveOrderFile
      • Adding filter means creating subscription, so here our send port is subscribing  to the messages which are published by our receive port "ReceiveOrderFile" in MessageBox.


      <07>

      16. Start the application and test it .
      Will keep on posting as an when I find something to share!!!!!!!!!!!!






      Related Post 

    • BizTalk Server: Multiple XML files to Single FlatFile Using File Adapter
    • BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL database
    • BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL database - Part 2
    • Is it possible to have Map Chaining on the Port Level
    • Promoting custom context property using Pipeline Component
    • Custom ZipReceivePipeline to Unzip Multi-Type Messages
    • Grouping and debatching Inbound Messages from WCF SQL Adapter - BizTalk 2010
    • Polling data from SQL using WCF-SQL Adapter in BizTalk 2010 - TypedPolling(From Multiple table)
    • Grouping XML Messages using custom XSLT- BizTalk 2010
    • Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010 - Composite operation(Multiple Records insertion)
    • Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010- Composite Operation (Message Transformation Pattern)
    • Debatching(Splitting) XML Message - BizTalk 2010
    • Debatching(Splitting) XML Message in Orches07ation using DefaultPipeline - BizTalk 2010


      • ServerLess360



      Comments

      Post a Comment

      Popular posts from this blog

      The request has both SAS authentication scheme and 'Bearer' authorization scheme. Only one scheme should be used

      Image
      While doing testing after doing a POC on Securing Logic App with Azure Active Directory authentication , where I have put logic app behind APIM and before passing the request to logic app, apim does validation of the token.  I was encountered with an error "The request has both SAS authentication scheme and 'Bearer' authorization  scheme.  Only one scheme should be used." Why it happened After validating the token which is part of the header i.e. Authorization, APIM forwards the request as it is to backend. As  Logic app is configured as  back end , it's url already consist of SAS signature plus the request also has Authorization section and this is the problem. By default every request endpoint on a logic app has a Shared Access Signature (SAS) in the endpoint's URL, which follows this format: https://<request-endpoint-URI>sp=<permissions>sv...
      Read more

      Getting Started with Logic Apps - AS2

      Image
      What is AS2? Every enterprise requires some kind of product, service or counselling from another enterprises thus there happens a B2B(business to business) communications, some of the communication is unique whereas most of the communications are common for most of the enterprises e.g., Purchase Order. But every enterprise has there own way(format) of sending the messages, leading to difficult management when number of partners increased.  Considering this the Enterprise leaders decided to have some standards/rules defined across the communication leading to B2B protocol standards which provide guidelines for trading partners to follow when conducting business between enterprises. EDI X12, EDIFACT, TRADACOM etc. AS2 stands for Applicability Statement 2 and is a B2B messaging protocol used to transmit Electronic Data Interchange (EDI)/Business documents from one organization to another. So EDI standards define how to format data and AS2 specifies how to securely transport...
      Read more

      How to Debug and Trace request in Azure APIM - Portal, Postman, RequestBin

      Image
      Introduction  APIM is a great option to expose API's with out of box features for applying restrictions, preprocessing, postprocessing etc. You can leverage existing API's,  by importing it and it gets added as a new API with all the operations associated with it. And based on the requirements you  apply policies at the stage/level (Inbound,Backend/Outbound) and you are ready to use the API. Before it is shared, we do some testing to make sure everything is working as per the expectation and this is where Debugging and Tracing request becomes important. APIM does provide a way to Trace a call with the help of  Ocp-Apim-Trace http header. So whichever request/call is to be traced, it needs to include this in header  with the value set to true and it has dependency on another header, so that also needs to be passed i.e.  Ocp-Apim-Subscription-Key Note: The api on which Tracing is to applied , it requires the subscription key to be en...
      Read more