none
How to load Senderpartyname,ReceiverId sender ID fields into sql RRS feed

  • Question

  • Hi,

    I have to insert senderparty name,receiverid,senderid and transation number into database.My  question i am doing a map for the rest of the fields which should be inserted into SQL but these fields will not be coming from any of the source schema fields.Is there any other way to extract these fields from the incoming file and load them into required fields and then insert

    the values into SQL.

     

    Thanks

     

     


    sri
    Monday, November 7, 2011 3:46 PM

All replies

  • Are you using X12, Edifact or?

     

    Morten la Cour

    Monday, November 7, 2011 6:22 PM
  • I am using x12
    sri
    Monday, November 7, 2011 6:26 PM
  • You have to make an orchestration to extract the information, since they are written to the context, and not in the actual message.

    -On your destination schema make the fields distinguished so you can access them.

    -Make a reference to Microsoft.BizTalk.EDI.Baseartifacts

    -Make a map mapping from X12 to SQL (or even better from X12 to an internal format, and then have a map from internal->SQL on your send port).

    -Make a construct message shape (construct SQL or Internal message) lets call it DestMessage.

    -Call the map

    -Message Assign:

    DestMessage = X12Message;

    DestMessage.SenderpartyName = X12Message(Edi.SenderPartyName);

    and so on

     

    Morten la Cour

    Monday, November 7, 2011 6:35 PM
  • It says the field can not promoted since its occuring multiple times in the document only nodes which will be unique

    can be promoted.is there any work around


    sri
    • Edited by BTS_LEARNER Monday, November 7, 2011 7:13 PM
    Monday, November 7, 2011 7:12 PM
  • Why would something like SenderParty etc. that are placed on the header of a document be placed in a multi-occurance record?

    Morten la Cour

    Monday, November 7, 2011 7:43 PM
  • I am also not sure that schema is generated from a database table.so its not giving me the option to change that.
    sri
    Monday, November 7, 2011 7:51 PM
  • Then change it from maxoccurs = unbounded to 1

    Morten la Cour

    Monday, November 7, 2011 7:53 PM
  • Its not giving me the option to change from unbounded to 1
    sri
    Monday, November 7, 2011 8:01 PM
  • Is it the new WCF SQL adapter that has generated the schema?

    Are you using Polling, TypedPolling or XmlPolling?

     

    Morten la Cour

    Monday, November 7, 2011 8:06 PM
  • ya this schema is generated using a new wcf -sql adapter.the inbound operation is specified as polling.
    sri
    Monday, November 7, 2011 8:19 PM
  • Sorry got it wrong. You are sending not receiving SQL! :-)

    Are you inserting directly into a table or calling a SP?

    Mortne la Cour

    Monday, November 7, 2011 8:20 PM
  • I am inserting directly into a table
    sri
    Monday, November 7, 2011 8:21 PM
  • Do you have a file called sqlBinding_dbo.xsd (or similar)

    Open it and choose ArrayOf[TableName]

    This will hold a record called [TableName] and here you should be able to change maxoccurs to 1

    Morten la Cour

    Monday, November 7, 2011 8:24 PM
  • Ya i changed the max occurs to one on the array of [TableName] and i was able to promote the fields which

    i want as distinguished.But on one of the fields i need to perform a conditional check before inserting the values

    into the database.Can i acheive that in the same message assignment shape.The condition is

    if ISA07=zz then load one value into destination else if ISA07=01 load another value into destination.


    sri
    Monday, November 7, 2011 9:11 PM
  • ANother thing is my destination message name is SQLData and my X12 schema corresponding message name is InBound message.I was trying to assign the sameway which you suggested as

    SQLData = InBoundmessage;

    but i am getting an error cannot implicitly convert message schemas_Inbound_x12_850 to message table operation dbo.tableInsert


    sri
    Monday, November 7, 2011 9:17 PM
  • No you need to create a map first mapping from X12 to SQL (or an internal message type). Sorry I wasn't clear.

    Call a map (X12->Dest)

    And then is a message assignment shape:

    You DON'T need the Dest = X12 then.

    Just do the 

    Dest.SenderParty = X12(Edi.Sender....); And so on.

     

    Morten la Cour



    • Edited by la Cour Monday, November 7, 2011 9:24 PM
    Monday, November 7, 2011 9:21 PM
  • I am done with the map already so i need to call a map and then below the map a message assignment shape right
    sri

    • Edited by BTS_LEARNER Monday, November 7, 2011 9:55 PM
    Monday, November 7, 2011 9:44 PM
  • Now i am getting a persistance exception when i changed and i am trying to insert

     

     

    Exception type: PersistenceException

    Source: Microsoft.XLANGs.BizTalk.Engine

    Target Site: Void Commit()

     

    A batch item failed persistence Item-ID ddac290e-4899-4748-a96c-86e57f8a7ad4 OperationType MAIO_CommitBatch Status -1061151960 ErrorInfo An error occurred when accessing the part data or one of its fragments. The part data or fragment may not exist in the database. .

     

    Exception type: PersistenceItemException

    Additional error information:

    Failed to publish (send) a message in the batch. This is usually because there is no one expecting to receive this message. The error was An error occurred when accessing the part data or one of its fragments. The part data or fragment may not exist in the database. with status -1061151960.

     

    Exception type: PublishMessageException


    sri
    • Edited by BTS_LEARNER Monday, November 7, 2011 9:56 PM
    Monday, November 7, 2011 9:55 PM
  • Hi morten,

    I tried out the way which you mentioned.But i am getting the following error

    Inner exception: A failure occurred while evaluating the distinguished field Rows.InboundChase824.EDITradingPartner against the message part data. The message part data does not contain at least one of the nodes specified by the XPath expression (listed below) that corresponds to the distinguished field. The cause for this error may be that the message part data has not been initialized or that the message part data does not conform to the message part schema. Ensure that the message part data is initialized correctly.

    Do you have any idea about the error.I am promoting the fields which i want as distinguished do i need to do anything more.Please respond.

     


    sri
    Tuesday, November 8, 2011 12:03 AM
  • I tried to pass a dummy value to the map and then use the distinguished fields to assign the values but i am getting an exception

     

    The result set for the XPath expression contains more than a single node.

     

    Exception type: AmbiguousXPathException

    Source: Microsoft.XLANGs.Engine

    Target Site: System.String GetXPath(System.String)

    The following is a stack trace that identifies the location where the exception occured

    Do you have any idea how to resolve this error

     

     


    sri
    Tuesday, November 8, 2011 3:16 PM