Forum Replies Created
-
AuthorPosts
-
June 1, 2009 at 8:28 AM in reply to: SQL Adapter – returning only the first response….not returning all responses #22546
You are sending multiple stored procedure calls to the adapter at once.
The adapter returns one body part per query it has run.
BizTalk will only show you the first message body part. You will need to iterate through the parts in the message to access all results.
I have come across a very similar issue, and didnot like the idea of pipelines within BizTalk. You can actually do this using the following cheat \ workaround that uses correllation tokens to acheive the same thing!
Check out this blog for deatils
A solicit response port will return messages to the receive part of the send\receive (solicit response) port.
If you wish to then drop this message to a file you would need to create another “one way” send port that uses filters to subscribe to the specific message properties of the returned message.
If I have understood your request properly you are asking which properties you should subscribe to (add to the file send port filter)
This would likely be the bts.messagetype and maybe the receive port and maybe the bts.sendportid, but I would suggest you trap the message in the admin console (maybe by having no subscribers to the returned message) then inspect the properties of the returned message to identify likely candidates.
Hope this is of help 🙂
Sure, can you please give me your email address?
I was just using Visual Studio to genenerate the XSLT. However there’s a trick of doing this though. After setting up the BTM (map) file, simply map one of the field from source schema to any field of destination schema. Then you right click on the BTM field in the “Solution Explorer” and choose “Validate Map”, then you should be able to see some mesage in the “Output” view. In the “Output” view, you should be able to see a link to the output XSLT generated base on the map file.
Hope this helps.
Hi,
There’s 2 types of properties in the context of a message
- properties based on values of a specific field of your chosen schema (MessageDataPropertyBase) and
- properties not base on any fields of your chosen schema (MessageContextPropertyBase) [Example of this is BTS.MSMQ.Label, BTS.FILE.SourceFileName etc.]
Property #2 (based on message field data) are automatically written and promoted into message context by BTS. You can promote property #1 (not based on any message field data) inside an orchestration using expression shape, for example YourMessage(Namespace.PropertyName) = “PropertyValue”.
According to your decription above, it seems like you need to work with MessageContextPropertyBase property. The problem with this type of property is that the value you assign inside the orchestration is not promoted, they are only written to the message context. Because of this, you can’t use tis property for routing.
However, you can create a custom pipeline componenent that promote the properties. Jon Flander has created a custom component that “you can place in any stage of either a Receive or Send pipeline, and you can configure it with a collection of Context properties (simple written properties or promoted properties). The component will just blindly put whatever values you configure it with into the context” You probably need to download the source code and make small modification to the code to suit your needs.
Hopefully this helps.
Thanks Guo Ming Li!
Do you have sample project for this by any chance that you can email it to me? Also, do you use any tool to generate xslt?
Thanks Sanjay! I already tried that. Only issue I had was linking records between both source schemas by their primary key IDs. ID gets correctly populated from first source as many records are in first source but for phone numbers, only first record gets populated in destination for all the records.
Hi,
Directly mapping 2 source schema to 1 destination schema is not possible directly. You first create an orchestration and drop the transformation shape. Specify two input schemas and one output schema by selecting new map in drop down. By selecting ok a map will be created with two source and one destination schema.
Hope this will answer your query.
Regards,
Sanjay Ahuja
Hi, You can use a custom XSL for your map. here is an example:
1. I created the following input schemas:
<?xml version=”1.0″ encoding=”utf-16″?>
<xs:schema xmlns:b=”http://schemas.microsoft.com/BizTalk/2003″ xmlns=”http://BizTalk_Server_Project1.CustomerName” targetNamespace=”http://BizTalk_Server_Project1.CustomerName” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>
<xs:element name=”Customers”>
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs=”unbounded” name=”Customer”>
<xs:complexType>
<xs:sequence>
<xs:element name=”CustomerID” type=”xs:string” />
<xs:element name=”Name” type=”xs:string” />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>——————————————
<?xml version=”1.0″ encoding=”utf-16″?>
<xs:schema xmlns:b=”http://schemas.microsoft.com/BizTalk/2003″ xmlns=”http://BizTalk_Server_Project1.CustomerPhone” targetNamespace=”http://BizTalk_Server_Project1.CustomerPhone” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>
<xs:element name=”Customers”>
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs=”unbounded” name=”Customer”>
<xs:complexType>
<xs:sequence>
<xs:element name=”CustomerID” type=”xs:string” />
<xs:element name=”PhoneNumber” type=”xs:string” />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>——————————————-
The destination schema is as such:
<?xml version=”1.0″ encoding=”utf-16″?>
<xs:schema xmlns:b=”http://schemas.microsoft.com/BizTalk/2003″ xmlns=”http://BizTalk_Server_Project1.CustomerRecord” targetNamespace=”http://BizTalk_Server_Project1.CustomerRecord” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>
<xs:element name=”Customers”>
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs=”unbounded” name=”Customer”>
<xs:complexType>
<xs:sequence>
<xs:element name=”CustomerID” type=”xs:string” />
<xs:element name=”Name” type=”xs:string” />
<xs:element name=”PhoneNumber” type=”xs:string” />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>—————————————————–
The map is setup to use a custom XSLT file:
——————————————————-
here is the test input data:
<ns0:Root xmlns:ns0=”http://schemas.microsoft.com/BizTalk/2003/aggschema”>
<InputMessagePart_0>
<ns1:Customers xmlns:ns1=”http://BizTalk_Server_Project1.CustomerName”>
<Customer>
<CustomerID>CustomerID_0</CustomerID>
<Name>Name_0</Name>
</Customer>
<Customer>
<CustomerID>CustomerID_1</CustomerID>
<Name>Name_1</Name>
</Customer>
<Customer>
<CustomerID>CustomerID_11</CustomerID>
<Name>Name_11</Name>
</Customer>
</ns1:Customers>
</InputMessagePart_0>
<InputMessagePart_1>
<ns2:Customers xmlns:ns2=”http://BizTalk_Server_Project1.CustomerPhone”>
<Customer>
<CustomerID>CustomerID_0</CustomerID>
<PhoneNumber>PhoneNumber_0</PhoneNumber>
</Customer>
<Customer>
<CustomerID>CustomerID_1</CustomerID>
<PhoneNumber>PhoneNumber_1</PhoneNumber>
</Customer>
<Customer>
<CustomerID>CustomerID_2</CustomerID>
<PhoneNumber>PhoneNumber_2</PhoneNumber>
</Customer>
</ns2:Customers>
</InputMessagePart_1>
</ns0:Root>—————————-
and here is the actual custom XSL file:
<?xml version=”1.0″ encoding=”UTF-8″?>
<xsl:stylesheet xmlns:xsl=”http://www.w3.org/1999/XSL/Transform” xmlns:msxsl=”urn:schemas-microsoft-com:xslt” xmlns:var=”http://schemas.microsoft.com/BizTalk/2003/var” exclude-result-prefixes=”msxsl var s2 s0 s1 userCSharp” version=”1.0″ xmlns:s2=”http://schemas.microsoft.com/BizTalk/2003/aggschema” xmlns:ns0=”http://BizTalk_Server_Project1.CustomerRecord” xmlns:s0=”http://BizTalk_Server_Project1.CustomerPhone” xmlns:s1=”http://BizTalk_Server_Project1.CustomerName” xmlns:userCSharp=”http://schemas.microsoft.com/BizTalk/2003/userCSharp”>
<xsl:output omit-xml-declaration=”yes” method=”xml” version=”1.0″ />
<xsl:template match=”/”>
<xsl:apply-templates select=”/s2:Root” />
</xsl:template>
<xsl:template match=”/s2:Root”>
<ns0:Customers>
<xsl:variable name=”var:namecount” select=”count(InputMessagePart_0/s1:Customers/Customer)” />
<xsl:variable name=”var:phonecount” select=”count(InputMessagePart_1/s0:Customers/Customer)” />
<xsl:call-template name=”loopName”>
<xsl:with-param name=”index”>1</xsl:with-param>
<xsl:with-param name=”ncount”><xsl:value-of select=”$var:namecount” /></xsl:with-param>
<xsl:with-param name=”pcount”><xsl:value-of select=”$var:phonecount” /></xsl:with-param>
<xsl:with-param name=”id”><xsl:value-of select=”string(InputMessagePart_0/s1:Customers/Customer[1]/CustomerID/text())” /></xsl:with-param>
<xsl:with-param name=”name”><xsl:value-of select=”string(InputMessagePart_0/s1:Customers/Customer[1]/Name/text())” /></xsl:with-param>
</xsl:call-template></ns0:Customers>
</xsl:template>
<xsl:template name=”loopName”>
<xsl:param name=”index” />
<xsl:param name=”ncount” />
<xsl:param name=”pcount” />
<xsl:param name=”id” />
<xsl:param name=”name” />
<xsl:if test=”$index <= $ncount” >
<xsl:call-template name=”loopPhone”>
<xsl:with-param name=”pindex”>1</xsl:with-param>
<xsl:with-param name=”ppcount”>
<xsl:value-of select=”$pcount” />
</xsl:with-param>
<xsl:with-param name=”cname”>
<xsl:value-of select=”$name” />
</xsl:with-param>
<xsl:with-param name=”nid”>
<xsl:value-of select=”$id” />
</xsl:with-param>
<xsl:with-param name=”pid”><xsl:value-of select=”string(InputMessagePart_1/s0:Customers/Customer[1]/CustomerID/text())” /></xsl:with-param>
<xsl:with-param name=”phone”><xsl:value-of select=”string(InputMessagePart_1/s0:Customers/Customer[1]/PhoneNumber/text())” /></xsl:with-param>
</xsl:call-template>
</xsl:if>
<xsl:if test=”$index <= $ncount” >
<xsl:call-template name=”loopName”>
<xsl:with-param name=”index”>
<xsl:value-of select=”$index + 1″ />
</xsl:with-param>
<xsl:with-param name=”ncount”>
<xsl:value-of select=”$ncount” />
</xsl:with-param>
<xsl:with-param name=”pcount”>
<xsl:value-of select=”$pcount” />
</xsl:with-param>
<xsl:with-param name=”id”><xsl:value-of select=”string(InputMessagePart_0/s1:Customers/Customer[$index+1]/CustomerID/text())” /></xsl:with-param>
<xsl:with-param name=”name”><xsl:value-of select=”string(InputMessagePart_0/s1:Customers/Customer[$index+1]/Name/text())” /></xsl:with-param>
</xsl:call-template>
</xsl:if>
</xsl:template>
<xsl:template name=”loopPhone”>
<xsl:param name=”pindex” />
<xsl:param name=”ppcount” />
<xsl:param name=”cname” />
<xsl:param name=”nid” />
<xsl:param name=”pid” />
<xsl:param name=”phone” />
<xsl:if test=”$pindex <= $ppcount” >
<xsl:variable name=”var:v3″ select=”userCSharp:LogicalEq($pid , $nid)” />
<xsl:if test=”$var:v3″>
<Customer>
<CustomerID>
<xsl:value-of select=”$nid” />
</CustomerID>
<Name>
<xsl:value-of select=”$cname” />
</Name>
<PhoneNumber>
<xsl:value-of select=”$phone” />
</PhoneNumber>
</Customer>
</xsl:if>
</xsl:if>
<xsl:if test=”$pindex <= $ppcount” >
<xsl:call-template name=”loopPhone”>
<xsl:with-param name=”pindex”>
<xsl:value-of select=”$pindex + 1″ />
</xsl:with-param>
<xsl:with-param name=”ppcount”>
<xsl:value-of select=”$ppcount” />
</xsl:with-param>
<xsl:with-param name=”cname”>
<xsl:value-of select=”$cname” />
</xsl:with-param>
<xsl:with-param name=”nid”>
<xsl:value-of select=”$nid” />
</xsl:with-param>
<xsl:with-param name=”pid”><xsl:value-of select=”string(InputMessagePart_1/s0:Customers/Customer[$pindex+1]/CustomerID/text())” /></xsl:with-param>
<xsl:with-param name=”phone”><xsl:value-of select=”string(InputMessagePart_1/s0:Customers/Customer[$pindex+1]/PhoneNumber/text())” /></xsl:with-param>
</xsl:call-template>
</xsl:if>
</xsl:template>
<msxsl:script language=”C#” implements-prefix=”userCSharp”><![CDATA[public bool LogicalEq(string val1, string val2)
{
bool ret = false;
double d1 = 0;
double d2 = 0;
if (IsNumeric(val1, ref d1) && IsNumeric(val2, ref d2))
{
ret = d1 == d2;
}
else
{
ret = String.Compare(val1, val2, StringComparison.Ordinal) == 0;
}
return ret;
}
public bool IsNumeric(string val)
{
if (val == null)
{
return false;
}
double d = 0;
return Double.TryParse(val, System.Globalization.NumberStyles.AllowThousands | System.Globalization.NumberStyles.Float, System.Globalization.CultureInfo.InvariantCulture, out d);
}
public bool IsNumeric(string val, ref double d)
{
if (val == null)
{
return false;
}
return Double.TryParse(val, System.Globalization.NumberStyles.AllowThousands | System.Globalization.NumberStyles.Float, System.Globalization.CultureInfo.InvariantCulture, out d);
}
]]></msxsl:script>
</xsl:stylesheet>—————————–
Hope this helps
May 30, 2009 at 2:50 AM in reply to: how to take arguments from a web page to uze them in an orchestration? #22532thank you for your suggestions, i have final exams this week (started last week) so i can’t spend any time on this project for this week,after my exams i will continue to this post.
thanks.
Hi,
If you haven’t figured this out already, remove your BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION statements from the code in your stored procedure, as using transactions in the SP cause problems when interfacing with BizTalk.
Cheers.
Daniel.
Hello Greg,
Thank you for your promt answer! That was the solution for my problem serialized scope+ delevery notification on the send port.
I use that solution because i work with untyped messages (without xsd). The response message from the webservice is an systemxml document. So i can receive messages with diferent structures in the same orchestration. In that case i think that is dificult to get data directly to sql using sql adapter because i don’t have the message xsd schema.
I also think that another solution is to send the response message of the first web service to the request message of the second web service using the message assignment shape. In this way i will avoid the writing of the response message data to disk.
Best Regards
Alex
Best bet would be a check on the MSDN BizTalk site …
http://msdn.microsoft.com/en-us/biztalk/default.aspx
It’s got installation guide and other things … been updated to BTS 2009, but there was an equivalent for BTS 2006 R2 that I used to create my virtual
The virtual is Win2003, VS2005, 2 Gb Mem, 30Gb disk
I have to ask the obvious question, why do you write the message to a file and then read it back via a web service before storing it in SQL. Why not write to a file and then store the same message in SQL?
Given there is a rational explanation for this, you need to block the orchestration on the send shape to the File send port. You can do this using delivery notification. Add a scope around the send shape and set Synchronized to true. Set Delivery Notification on the send port to Transmitted
-
AuthorPosts
