Semaphore Media and Development

I found a great article on how to configure auto deploy VS database project after build using TFS 2010.

http://www.codewrecks.com/blog/index.php/2010/01/04/deploy-a-database-project-with-tfs-build-2010/

One more note: If you want to manually execute the deployment script generated by MSBuild using SQL Mgmt Studio, make sure to set it to SQL Command Mode first or else the :setvar line will error out. To set the mode, goto Query->SQLCMD mode in SQL Mgmt Studio’s menu.

While working on a small POC project using BizTalk 2009, I encountered an issue where MQSC adapter isn’t showing up in BizTalk Admin Console. By following the steps described in the blog post I was able to fix the issue.

http://blog.nimbo.com/biztalk-2009-websphere-mq-communication-on-windows-2008-64-bit/

Apparently the adapter isn’t getting registered under WOW6432Node in registry. Reinstall WebSphere MQ (client-based) option and then import the following reg file solved the issue.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{CF8B4D15-5D2B-4c62-90DB-16804D2454B9}]
@=”MQSC Adapter Class”
“AppID”=”{401D2C40-DAA9-4a63-8D42-9BF98B04F616}”

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{CF8B4D15-5D2B-4c62-90DB-16804D2454B9}\BizTalk]
@=”BizTalk”
“TransportType”=”MQSC”
“Constraints”=dword:00003d0b
“ReceiveLocation_PageProv”=”{2DE93EE6-CB01-4007-93E9-C3D71689A280}”
“TransmitLocation_PageProv”=”{2DE93EE6-CB01-4007-93E9-C3D71689A282}”
“OutboundProtocol_PageProv”=”{2DE93EE6-CB01-4007-93E9-C3D71689A283}”
“OutboundEngineCLSID”=”{6811E17F-A4C2-44BB-B4D5-D69962E58E87}”
“InboundEngineCLSID”=”{538F8B3D-EE7B-4459-A123-45CD5C3B671B}”
“InboundTypeName”=”Microsoft.BizTalk.Adapter.Mqsc.MqscReceiver, Microsoft.BizTalk.Adapter.MQSC, Version=7.0.2300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35, Custom=null”
“InboundAssemblyPath”=”"
“OutboundTypeName”=”Microsoft.BizTalk.Adapter.Mqsc.MqscTransmitter, Microsoft.BizTalk.Adapter.MQSC, Version=7.0.2300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35, Custom=null”
“OutboundAssemblyPath”=”"
“PropertyNameSpace”=”http://schemas.microsoft.com/BizTalk/2003/mqs-properties”
“ProtocolAlias”=”mqsc://”
“ReceiveHandlerPropertiesXML”=””
“SendHandlerPropertiesXML”=””
“ReceiveLocationPropertiesXML”=””
“SendLocationPropertiesXML”=””
“AliasesXML”=”mqsc://”
“AdapterMgmtTypeName”=”Microsoft.BizTalk.Adapter.Mqsc.MqscAdapterManagement”
“AdapterMgmtAssemblyPath”=”C:\\Program Files\\Microsoft BizTalk Adapters for Host Systems 2.0\\system\\Microsoft.BizTalk.Adapter.MQSCAdmin.dll”

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{CF8B4D15-5D2B-4c62-90DB-16804D2454B9}\Implemented Categories]

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{CF8B4D15-5D2B-4c62-90DB-16804D2454B9}\Implemented Categories\{7F46FC3E-3C2C-405B-A47F-8D17942BA8F9}]
@=”"

How to generate a polling statement schema using adapter wizard:

1. in the binding properties tab of wizard, set:
InboundOperationType: Polling
EnableBizTalkCompatibilityMode: True
PolledDataAvailableStatement
PollingStatement:

2. Click connect and select “Service (Inbound Operations)”

3. Instead of clicking on a schema, click on the top branch i.e “/”

4. In the “Available categories and operations” box, scroll all the way down, select “POLLINGSTMT” operation, and click Add button.

5. Click OK to generate the schema

Installing Oracle’s ODAC drivers can be a struggle since the installation guide, from both Oracle and Microsoft, does not spell out the gotcha’s. After a few days of testing, we finally got it right. Here are the steps taken to ensure that both versions are supported by BizTalk 2010.

1. Download both 32-bit and 64-bit of Oracle Instant Client (http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html)

2. First install the 64-bit version. This is important! If you install 32-bit version version first then you’ll run into a slew of errors during installation.

3. When prompted for installation options, choose custom install.

4. Select the following components:
MTC
Oracle Net
Admin Assistant
Oracle Counter for Windows Performance
OLE
OLE DB
ODBC
Data Provider for .NET

5. After 64-bit client is installed, install 32-bit client next.

6. For 32-bit client install, choose the same components as 64-bit install. Make sure to install to a different file folder than the 64-bit folder.

7. Choose ignore if there is a warning regarding the existence of MTC

8. After both installation is completed, verify the following components are installed.

Oracle.DataAccess 2.112.1.0 (X86 and AMD64)
Policy.2.102.Oracle.DataAccess (X86 and AMD64)
Policy.2.111.Oracle.DataAccess (X86 and AMD64)

9. If the 64-bit version of Policy.2.102.Oracle.DataAccess or Policy.2.111.Oracle.DataAccess is missing in GAC, goto \product\11.2.0\client_64\odp.net\PublisherPolicy\2.x\ folder and GAC it. (make sure to run GACUTIL as Administrator to avoid policy permission issue)

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