Semaphore Media and Development

There will be a time when BizTalk databases need to be migrated to a new SQL server i.e. hardware upgrade or configuring a new cluster. Before restoring the SSODB database, the original master secret need to be restored. In a SQL cluster environment, make sure to check off “Use Network Name for computer name” option on the general panel of the Enterprise SSO service cluster resource. If this is not checked off, then you’ll most likely run into the following error when trying to choose the cluster name as the SSO server:

“Could not contact the SSO server XXX. Check that SSO is configured and that the SSO service is running on that server.”

When working with BAM, if you try to run the BAM_AN_ SSIS package in SQL Server 2008 in order to load the activity data to the cube and get “the binary code for the script is not found” error, then it is most likely due to missing SQL Server component. Make sure to install the Client Tool SDK and Client Tool Backward Compatibility from the SQL Server 2008 installation bits. Then undeploy the existing BAM view and redeploy.

At some point all BizTalk developer will run into this pesky issue. You try to create a new BizTalk project and you get an “… project creation failed” error in Visual Studio. This is because, perhaps after a VS update or other installation, the “PossibleProjectExtensions” attribute in Windows registry get replaced by the default value. BizTalk project extension, btproj, went missing.

To solve this issue, run regedit and goto the following:

For Win32 OS:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\9.0\Projects\{FAE04EC0-301F-11d3-BF4B-00C04F79EFBC}]

For Win64 OS:
KEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\VisualStudio\9.0\Projects\{FAE04EC0-301F-11d3-BF4B-00C04F79EFBC}

Add btprojc to PossibleProjectExtensions setting. So it should look something like this:
“PossibleProjectExtensions=”csproj;btproj”

Relaunch VS and you should be able to create BizTalk project now.

The interface need to first check to see if a folder already exists on FTP server. If not it needs to create the folder first before the FTP adapter sends the message. After spending a day trying all sorts of methods, I finalized on the following approach:

1. create a helper class that establish a TCP connection using System.Net.Sockets to the FTP server.
2. After logging in, issue a MKDIR command. Use Try..Catch block to trap for any error and evaluate if the return status code = 550, which indicates that the folder already exists.
3. If a new folder is created, makes sure that the folder has the proper permission by issuing “SITE CHMOD ”
4. upload the message via FTP adapter

When selecting a recordset for update or delete from a DB2 table using the DB2 adapter, you may run into the following error when attempting to execute the update/delete statement:

” <DB2 table> ?<DB2 schema> ?3 in *N not valid for operation.”

This is because the table is not journaled and that DB2 adapter expects to run in transactional mode. To resolve this error, simply add ” FOR UPDATE WITH NC” to the end of your select statement (NC = no commit). Alternatively, enable journaling in the DB2 table.

My client has a new requirement that calls for saving the inbound AS2-based message in it’s original file name, and that the file must be saved to a specified file folder if the inbound file name’s first 14 characters matches a string pattern.

Reading this post http://blogs.msdn.com/biztalkb2b/archive/2009/08/05/as2-filename-preservation.aspx it implies that the AS2Receive pipeline component should take care of extrapolating the file name from AS2′s MIME header and writes it to EDIINT.PreservedFileName context property.  However this is not the case; this context property doesn’t exist during my testing.  Perhaps it is only written when using AS2EdiReceive pipeline component (my client is only using AS2 but not EDI)?

The filename does show up in AS2PayloadContentType context property.

Not able to verify if AS2EdiReceive component will promote the PreservedFileName property, I decided to create a custom pipeline component that parses the filename from  AS2PayloadContentType property value, promote the first 14 characters to a new context property via property schema, and write the parsed file name to ReceivedFileName so that %SourceFileName% macro can use it in the send port configuration.

Here’s the code for the Execute method in the custom pipeline component:

public Microsoft.BizTalk.Message.Interop.IBaseMessage Execute(Microsoft.BizTalk.Component.Interop.IPipelineContext pc, Microsoft.BizTalk.Message.Interop.IBaseMessage inmsg)
{
IBaseMessagePart bodyPart = inmsg.BodyPart;
IBaseMessageContext context = inmsg.Context;
string httpHeader = "";
string filename = "";
string prefix = "";
if (bodyPart != null)
{
try
{   // determine which context property to read the filename string (user configurable in port configuration)
switch (_readContextProperty)
{
// strip out file path in the filename, parse the substring, and promote it as FileNamePrefix (in property schema) so that it can be used for msg routing
case "PreservedFileName":
filename = Path.GetFileName(context.Read("PreservedFileName", "<a href="http://schemas.microsoft.com/BizTalk/2006/as2-properties").ToString">http://schemas.microsoft.com/BizTalk/2006/as2-properties").ToString</a>());
prefix = filename.Substring(_startPos, _length);
break;
case "AS2PayloadContentType":
httpHeader = context.Read("AS2PayloadContentType", "<a href="http://schemas.microsoft.com/BizTalk/2006/as2-properties").ToString">http://schemas.microsoft.com/BizTalk/2006/as2-properties").ToString</a>();
filename = Path.GetFileName(httpHeader.Substring(httpHeader.IndexOf("name=") + 5));
prefix = filename.Substring(_startPos, _length);
break;
default:
filename = Path.GetFileName(context.Read("ReceivedFileName", "<a href="http://schemas.microsoft.com/BizTalk/2003/file-properties").ToString">http://schemas.microsoft.com/BizTalk/2003/file-properties").ToString</a>());
prefix = filename.Substring(_startPos, _length);
break;
}
context.Write("ReceivedFileName", "<a href="http://schemas.microsoft.com/BizTalk/2003/file-properties">http://schemas.microsoft.com/BizTalk/2003/file-properties</a>", filename);
context.Promote("FileNamePrefix", "<a href="https://XXXXX.Pipelines.PipelinePropertySchema">https://xxx.Pipelines.PipelinePropertySchema</a>", prefix);   // promote prefix for routing purpose
}
catch (Exception ex)
{
System.Diagnostics.EventLog.WriteEntry("BizTalk - GetAS2FileNamePrefix Pipeline Component Error", ex.ToString());
throw ex;
}
}
return inmsg;
}

I then created and deployed a property schema so that the pipeline component can promote the file name prefix string as a bona-fide context property.

<?xml version="1.0" encoding="utf-16" ?>
 <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns="http://XXXXX.Pipelines.PipelinePropertySchema" targetNamespace="https://XXXXX.Pipelines.PipelinePropertySchema" xmlns:xs="http://www.w3.org/2001/XMLSchema"></div>
<xs:annotation>
 <xs:appinfo>
<b:schemaInfo schema_type="property" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" />
</xs:appinfo>
</xs:annotation>
 <xs:element name="FileNamePrefix" type="xs:string">
<xs:annotation>
 <xs:appinfo>
<b:fieldInfo propertyGuid="92ce386d-71bb-4d7a-8f2e-e76a0e229120" rootTypeName="FileNamePrefix" />
</xs:appinfo>
</xs:annotation>
</xs:element>
</xs:schema>

In the receive location I configured the new receive pipeline with the following:
Finally a new send port is created with the following filter criteria:

Filter: XXXXX.Pipelines.FileNamePrefix == <file name prefix> And
EdiIntAS.IsAS2MdnResponseMessage == False And
EdiIntAS.AS2From == XYZ

Then I created a new receive pipeline artifact and added the following components

Decode Stage: AS2Decode
Disassemble Stage: AS2Disassemble
Validate Stage: <new custom pipeline component>

Once the pipeline is deployed, set the following value in the pipeline’s runtime properties:

Length: 14
readContextProperty: AS2PayloadContentType
startPos: 0

I ran into a challenge having to map a flattened file data, from SAP’s RFC call, to a hierarchically structured canonical schema.

Source:

<YITAB>

<RecType />

<CDATA />

</YITAB>

Canonical:

<Header>

<Detail>

<DetailSpecialInstruction />

</Detail>

<HeaderSpecialInstruction />

</Header>

rules:

1. if RecType = 001 then header record; if RecType = 002 then Detail Record

2. if RecType = 003 and substring(CDATA,1,5) <> “00000″, then it is a DetailSpecialInstruction record and substring(CDATA,1,5) = the related detail record line number

3. if RecType = 003 and substring(CDATA,1,5) = “00000″, then it is a HeaderSpecialInstruction record

As you can see, this is a complicated transformation exercise. Not only does the map needs to convert the flattened dataset to a hierarchical structure with related detail record and special instruction record grouped under the corresponding parent node, but also the key that ties the special instructions (both detail and header) are embedded inside the CDATA string.

I can’t make it this transformation happen visually inside the map, so I had to create a custom XSLT to implement these rules.

<?xml version=”1.0″ encoding=”utf-8″?>
<xsl:stylesheet version=”1.0″
xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”
xmlns:ns0=”http://Rev.Eai.DeliveryNote.Schemas.DeliveryNoteSAPGrouped”
xmlns:ns1=”http://Microsoft.LobServices.Sap/2007/03/Rfc/”
xmlns:ns2=”http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/”
>
<!– Defining the key used for locating PickTicketNum –>
<xsl:key name=”rows-by-CPKTNO” match=”/ns1:Y_DELIVERY_NOTEResponse/ns1:ITAB/ns2:YITAB” use=”ns2:CPKTNO” />
<xsl:template match=”/”>
<ns0:Groups>
<!– Looping the unique categories to get a group for –>
<xsl:for-each select=”/ns1:Y_DELIVERY_NOTEResponse/ns1:ITAB/ns2:YITAB[count(. | key('rows-by-CPKTNO', ns2:CPKTNO)[1]) = 1]”>
<!– Creating a new group and set the numberOfRows –>
<xsl:call-template name=”group”>
<xsl:with-param name=”parentCPKTNO” select=”ns2:CPKTNO” />
</xsl:call-template>
</xsl:for-each>
</ns0:Groups>
</xsl:template>
<xsl:template name=”group”>
<xsl:param name=”parentCPKTNO” />
<DeliveryNote CPKTNO=”{ns2:CPKTNO}”>
<!– Loop all the rows within each PickTicketNum  –>
<xsl:for-each select=”key(‘rows-by-CPKTNO’, ns2:CPKTNO)”>
<xsl:if test=”current()/ns2:CRECTYPE = ’001′”>
<!– Create header record –>
<HeaderRecord>
<xsl:call-template name=”body”>
</xsl:call-template>
</HeaderRecord>
</xsl:if>
</xsl:for-each>
<xsl:for-each select=”/ns1:Y_DELIVERY_NOTEResponse/ns1:ITAB/ns2:YITAB”>
<xsl:if test=”current()/ns2:CRECTYPE = ’002′ and current()/ns2:CPKTNO = $parentCPKTNO”>
<DetailRecord>
<xsl:call-template name=”body”>
</xsl:call-template>
<xsl:call-template name=”DetailspecialInstruction”>
<xsl:with-param name=”parentDetailCPKTNO” select=”$parentCPKTNO” />
<xsl:with-param name=”parentDetailLineNbr” select=”substring(current()/ns2:CDATA,1,5)” />
</xsl:call-template>
</DetailRecord>
</xsl:if>
</xsl:for-each>
<xsl:call-template name=”HeaderspecialInstruction”>
<xsl:with-param name=”parentCPKTNO” select=”$parentCPKTNO” />
</xsl:call-template>
</DeliveryNote>
</xsl:template>
<xsl:template name=”DetailspecialInstruction”>
<xsl:param name=”parentDetailCPKTNO” />
<xsl:param name=”parentDetailLineNbr” />
<xsl:for-each select=”/ns1:Y_DELIVERY_NOTEResponse/ns1:ITAB/ns2:YITAB”>
<xsl:if test=”current()/ns2:CRECTYPE = ’003′ and current()/ns2:CPKTNO = $parentDetailCPKTNO and substring(current()/ns2:CDATA, 1,5) = $parentDetailLineNbr”>
<DetailspecialInstruction>
<xsl:call-template name=”body”>
</xsl:call-template>
</DetailspecialInstruction>
</xsl:if>
</xsl:for-each>
</xsl:template>
<xsl:template name=”HeaderspecialInstruction”>
<xsl:param name=”parentCPKTNO” />
<xsl:for-each select=”/ns1:Y_DELIVERY_NOTEResponse/ns1:ITAB/ns2:YITAB”>
<xsl:if test=”current()/ns2:CRECTYPE = ’003′ and current()/ns2:CPKTNO = $parentCPKTNO and substring(current()/ns2:CDATA, 1,5) = ’00000′”>
<HeaderspecialInstruction>
<xsl:call-template name=”body”>
</xsl:call-template>
</HeaderspecialInstruction>
</xsl:if>
</xsl:for-each>
</xsl:template>
<xsl:template name=”body”>
<CFROMSID>
<xsl:value-of select=”ns2:CFROMSID”/>
</CFROMSID>
<CFROMTABLE>
<xsl:value-of select=”ns2:CFROMTABLE”/>
</CFROMTABLE>
<CTOSID>
<xsl:value-of select=”ns2:CTOSID”/>
</CTOSID>
<CTOTABLE>
<xsl:value-of select=”ns2:CTOTABLE”/>
</CTOTABLE>
<CKEY>
<xsl:value-of select=”ns2:CKEY”/>
</CKEY>
<CDTCREATE>
<xsl:value-of select=”ns2:CDTCREATE”/>
</CDTCREATE>
<CDATALEN>
<xsl:value-of select=”ns2:CDATALEN”/>
</CDATALEN>
<CDATA_OFFSET>
<xsl:value-of select=”ns2:CDATA_OFFSET”/>
</CDATA_OFFSET>
<CRECTYPE>
<xsl:value-of select=”ns2:CRECTYPE”/>
</CRECTYPE>
<CCOMPANY>
<xsl:value-of select=”ns2:CCOMPANY”/>
</CCOMPANY>
<CDIV>
<xsl:value-of select=”ns2:CDIV”/>
</CDIV>
<CPKTNO>
<xsl:value-of select=”ns2:CPKTNO”/>
</CPKTNO>
<CDATA>
<xsl:value-of select=”ns2:CDATA”/>
</CDATA>
<CDTPROCESS>
<xsl:value-of select=”ns2:CDTPROCESS”/>
</CDTPROCESS>
<CDTARCHIVE>
<xsl:value-of select=”ns2:CDTARCHIVE”/>
</CDTARCHIVE>
<STATUS>
<xsl:value-of select=”ns2:STATUS”/>
</STATUS>
<MSGNO>
<xsl:value-of select=”ns2:MSGNO”/>
</MSGNO>
</xsl:template>
</xsl:stylesheet>

When calling a RFC function via a two-way WCF send port in BizTalk, you may notice that there are not result returned.  In this case, take a look at the generated RFC schema and verify if there is <ITAB> node in the schema.  If it exists, then you need to pass in an empty <ITAB> node in order to instruct WCF adapter to return the table result back.

For example:

<ns0:Y_DELIVERY_NOTE xmlns:ns0="http://Microsoft.LobServices.Sap/2007/03/Rfc/" xmlns:ns3="http://Microsoft.LobServices.Sap/2007/03/Types/Rfc/">

<ns0:DELPKMS>N</ns0:DELPKMS>

<ns0:PRNODE>01</ns0:PRNODE>

<ns0:SCHDTYPE>2</ns0:SCHDTYPE>

<ns0:SCHEDULE>N</ns0:SCHEDULE>

<ns0:SIMULATE>N</ns0:SIMULATE>

<strong><ns0:ITAB /></strong>

</ns0:Y_DELIVERY_NOTE>

This is also documented in this post http://blogs.msdn.com/adapters/archive/2008/01/10/when-i-execute-bapis-i-don-t-get-back-any-data-help.aspx

There are a plethora of blogs and help files on the net on the subject of WCF adapter and ESB.  However I couldn’t find any info on how to setup a BRE policy called by an ESB itinerary to configure a dynamic WCF-custom send port using SAPBinding.  After hours of trial and hair-jerking moments, I finally figured out how to do it.

In the Actions window of the BRE policy, set the following:

Set End Point Outbound Transport Type to     WCF-Custom

Set End Point Config to      BindingType=sapBinding&UserName=<SAP username>&Password=<password>&BindingConfiguration=<binding name=”SAPBinding” receiveIdocFormat=”Rfc”  enableSafeTyping=”true”  enableBizTalkCompatibilityMode=”true”  sendTimeout=”00:10:00″ openTimeout=”00:10:00″ closeTimeout=”00:10:00″ />

Set End Point Outbound Transport Location to sap://CLIENT=100;LANG=EN;@a/<host>/10?RfcSdkTrace=False&AbapDebug=False

Set End Point WCF Action to      http://Microsoft.LobServices.Sap/2007/03/Rfc/<rcf function name>

This is cool.  Now I can send message to SAP through a dynamic send port by  implementing a BRE resolver in an itinerary Messaging Extender shape without having to resort to using orchestration to set the binding info.

Although this is not directly related to BizTalk, but I do run across this issue many times since I mainly work with virtual images that host various configuration types of BizTalk environment.

I was able to convert an existing Virtual PC image to VMWare image via VMWare’s Converter. However, upon launching the converted VMWare image, the mouse is not responding inside the image.

Apparently Virtual PC installed it’s own mouse driver which conflicts with WMWare’s mouse driver that’s installed with WMWare Tools (vmtool). Therefore when the images gets converted to VMWare image the vmtool was not able to take control of the mouse driver. I took the following steps to resolve the mouse driver issue.

1. Unistall vmtool and reboot
2. Browse to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Contro l\Class{4D36E96F-E325-11CE-BFC1-08002BE10318}
3. Remove the value msvmmouf from the UpperFilters Regvalue, then reboot.
4. Reinstall vmtool