Cnext’s Blog

In a BizTalk environment it is sometimes necessary to create a custom adapter to implement functionality that is not available with the out-of-the-box adapters. This article will handle a small part in the process of implementing your custom adapter, the deployment part.

Also, this article only applies to adapters that use strong named assemblies. Using strong named assemblies has advantages like unique names, versioning, security,… See this link: http://msdn.microsoft.com/en-us/library/wd40t7ad(VS.71).aspx

To install or update your custom adapter you need to do the following steps.

Install the assemblies in the GAC.

You can use a MSI installation for production. For development you can drag your newly built assemblies in the GAC where the BizTalk runtime resides.You do this for both the design-time and run-time assembly.

Registry settings

When the BizTalk administration console starts it will check the registry for the design-time settings (assemblypath,propertynamespace). Therefor it is important to restart your administration console each time you update these. You can generate a registry file (.reg) to insert/update the adapter registry settings using the adapter registration wizard (see this link: http://msdn.microsoft.com/en-us/library/aa578379(BTS.20).aspx). Unfortunately some generated items are incorrect when using strong named assemblies. You can’t choose strong named assemblies when choosing the assemblies that represent your adapter with the Registration Wizard. This leads to values like these in the registry file: “AdapterMgmtAssemblyPath”=”C:\\MyProject\\bin\\debug\\AdapterManagement.dll”This is not the path for a strong named assembly. You can delete the values for the AdapterMgmtAssemblyPath, OutboundAssemblyPath and InboundAssemblyPath. Execute the registry file. You can verify the settings with the registry editor.

Update Database table

If you have not yet added your adapter to the available adapters yet, do this now. This is done by right clicking the adapters node in the administration console and choose new, adapter. Add your custom adapter. When adding your custom adapter, a record will be created in the adm_adapter table in the BizTalkMgmtDb. This record will be read when the runtime (host) starts, the host will load your adapter assembly based on the references in this table. It is not customary to change data in the BizTalk databases manually but we’ll make an exception just this once. Open the adm_adapter table in the BizTalkMgmtDb. Verify if strong named references to your runtime assembly is used. For example the inboundTypeName column should contain MyCompany.BizTalk.FileReceiver, MyCompany.BizTalk.FileReceiverClass, Version=11.4.0.0, Culture=neutral, publicKeyToken=63168444d906e8f6

Activate your adapter

You activate the new version of your adapter by restarting all the host instances that use it. Watch out for changed properties. These should be filled in properly for each receive location or send port that uses your adapter.

In a previous post I wrote about an AS2 problem that I encountered after renaming the BizTalk Groupname.
In this post I digg into another AS2 problem, this time related to context properties.

Lately I have been asked to reconfigure an AS2 configuration to send messages with a predefined filename. This is easily done by selecting Transmit file name in MIME header. This checkbox can be found under the AS2 properties of the sending party. As you can see in the printscreen there is a possibility to point to a context property of which the value will be used as filename.
Simply put the context property shortname between two percent signs. If the context property wouldn’t exist you can choose to suspend the message.

After my first attempt to send a message another AS2 error showed up.

A message sent to adapter “HTTP” on send port “sp_As2ToACC” with URI “http://192.168.254.163:5081/BTSHTTPReceive.dll”
“http://192.168.244.153:5081/BTSHTTPReceive.dll”
” is suspended. Error details: There was a failure executing the send pipeline: “Microsoft.BizTalk.EdiInt.DefaultPipelines.AS2Send, Microsoft.BizTalk.Edi.EdiIntPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ Source: “AS2 encoder” Send Port: “sp_As2ToACC” URI: http://192.168.254.163:5081/BTSHTTPReceive.dll

Reason: The type initializer for ‘Microsoft.BizTalk.AS2.Pipelines.BizTalkPropertyList’ threw an exception.
MessageId: {451AC15C-7545-4487-8979-26E0511BCC3E}
InstanceID: {B8823D47-30F9-4E29-8649-8A965ADA1AF4}

Following the errormessage something went wrong with the initialisation of the BizTalkPropertyList.
This list can be found in the BizTalkMgmt database table bt_DocumentSpec.
The AS2 encoder will query this table to look up the property configured for retrieving the filename.
So what happened? If we open this table we see a lot of columns. But for the AS2 encoder only two seems to matter: clr_namespace and schema_root_name. The error mentioned above is caused when there are two or more property schemes deployed with the same value for these fields. In other words to avoid the AS2 encoder error, the concatenation of these fields must act as a primary key within this table. Also note that the duplicate property does not necessarely have a relation to the configured filename property. Lets have a closer look. Every BizTalk developer knows that every scheme must be made unique by its targetnamespace and rootname. This garantuees that BizTalk will process every message correctly. In the bt_DocumentSpec table this value is stored as ‘msgtype’. In this case we are talking about propertyschemes. So the msgtype field value is constructed by targetnamespace#propertyname. Because a scheme lives in a .Net BizTalk Assembly there is also another .NET namespace associated with the property. Right! clr.namespace.schema_root_name. Do remember that while this is not the fully qualified name of the property it is the value where the AS2 encoder will look for.

For example, the two rows shown in the picture below will cause the error.

I wrote a sql script to easily find duplicates in the bt_DocumentSpec table.

SELECT clr_namespace + ‘.’ + schema_root_name as shortName, COUNT(*)
FROM [dbo].[bt_DocumentSpec]
WHERE xsd_type <> ”
GROUP BY clr_namespace + ‘.’ + schema_root_name
HAVING COUNT(*) > 1

If you find duplicate properties you have to rename the clr_namespace. Do this by opening the propertylist of the propertyschema you want to modify (from within visual studio). There you will find the .Net namespace. Try to keep this namespace unique for all your BizTalk projects.

External assemblies are being called from within BizTalk orchestrations in almost every BizTalk application.
Knowing how to debug them is important. This guideline will show you how you can debug those external assemblies on
your workstation or a remote box using a very simple sample application.

Debugging external assemblies on a local box with one BizTalk host instance sample application

In the sample application an orchestration has been created that calls a method in an external C#.Net assembly.
Both projects are built and deployed. The BizTalk project is deployed on the workstation by using the deploy solution function.
The deploy solution can be used after setting the project deployment configuration properties. The external C#.Net assembly is
installed into the GAC.

Debugging

When your project is deployed and configured, be sure to restart the host instances that host the project you want to debug.
In Visual Studio set breakpoints in your code. To debug you must attach to the process that is running the .Net code.
This process will be the BizTalk host that runs the orchestration calling the .Net code.

In Visual Studio, click the Debug menu and choose Attach to Process.

The BTSNTSvc.exe process, which represents one host instance, is probably running under a different account than your personal account. Therefor you must activate the Show processes from all users checkbox.

If you only have one host instance on the machine you are debugging on, choosing the right process is simple. Later on we will show you what to do when multiple host instances are present. Click attach after selecting the correct process. An error can occur specifying that Transact SQL debugging is not possible. Ignore this.

Now activate your orchestration so that it will call the .Net code. Your breakpoint should be hit and you can debug like any other .Net code.

Remote debugging, multiple host instances

It is not a common thing to do but if it is really necessary you can also debug code that is running on a remote box.

PDB file

A difference between debugging locally and remote is the availability of the PDB file that contains your debug symbols.
These are necessary for your breakpoints to be hit. To make the PDB file available, you need to copy it in the GAC, together with your assembly.

Copy your PDB file out of your external assembly project bin folder and copy it to the machine.
Open a command window and type the following command:

copy <your_pdb_location> c:\windows\assembly\gac_msil\<your_dll>\<version>__<public_key>

When the BizTalk host restarts it will detect the PDB file and load the debug symbols. Later in this article you will be able to see if the symbols are indeed being loaded.

Remote debugging tool

Now the Visual Studio running on your workstation needs to be able to communicate with the remote box to receive the debugging information. To do this, the VS remote debugging monitor needs to be running on the remote box. To activate the debug monitor you need to copy the contents of the C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\Remote Debugger\x86 folder to the remote box and run the msvsmon.exe process. If Visual Studio is installed on the remote box, msvsmon might already be available. This process allows Visual Studio to access the box and exchange debugging information. This is a security leak so only start this process when it is necessary.

When the debug monitor is running you can start to debug. Open the debug menu and choose attach to process.
In the qualifier field you enter the username under which the debug monitor is running followed by the computername, divided by an @.

Determining which process to attach to

Now you need to choose the right BTSNTSvc.exe process by determining the process ID.
You can use the tasklist command to query the processes. Execute the following command in a command prompt on the remote box.

tasklist /SVC /FI “IMAGENAME eq btsnt*”

A list of running BizTalk processes with their hostname and processID is shown.

Start debugging

Start the orchestration on the specified server and you will be able to debug.
On a multiserver environment you will have to stop the host instances on the servers where you are not attached to.
It might be possible that your test instance will be run on the other server.

Troubleshooting

If your breakpoint is not hit you can check if the debug symbols are loaded.
In visual studio, press CTRL+ALT+U. The modules window opens for the process you attached to.
Check if your external assembly is loaded and if the symbols are loaded.

If the symbols are not loaded you can try to also place the PDB file into the folder where you installed the external assembly. Restart the host instance and attach again.

Last week I was working on an AS2 configuration on BizTalk 2009.
The AS2 messages needed to be encrypted and signed.

Previously I went through an AS2 configuration upon BizTalk 2006 and as I expected everything looked familiar to me.
Setting up the encryption was an easy job. Nevertheless, the second requirement, message signing, wasn’t as straightforward as I hoped.

After configuring and enabling a certificate to sign the messages, I received the following error:

“Error: The Signing Certificate has not been configured for AS2 party.”

I checked and double checked every known issue that i could bing. As there are…

• Install the certificate used for signing in the personal certificate store of the appropriate BizTalk host service account.
  

  This installation has to be done while you are logged on with this service account. Otherwise the certificate will be imported in the wrong personal store.
  

• Avoid enabling strong private key protection in the certificate during import in the personal store.
  

  And be sure that the certificate has the private key included.

• …
  

None of these well known problems solved my error. Searching for new inspiration I repeated the installation on a clean standalone development machine. And yes, here I got more success! The signing and encryption both worked. After comparing the two installations I didn’t find any difference in the configuration of the necessary certificates. So if my AS2 configuration is the same then what makes the difference?

An SQL Trace brought the answer. I asked the dba to capture the SQL activity while BizTalk was trying to send an AS2 message. In the trace we found the following sql-statement:

declare @p3 nvarchar(256)
set @p3=NULL
declare @p4 nvarchar(256)
set @p4=N”
exec admsvr_GetGroupSigningCert
@nvcGroupName=N’BizTalk Group’,@nvcHostName=N’BizTalkServerApplication’,
@nvcGroupSignCertName=@p3 output,
@nvcHostSignCertName=@p4 output
select @p3, @p4

This statement wouldn’t have looked wrong to me, if I wouldn’t have changed the name of the BizTalk group.
Because most of the time I maintain different biztalkgroups from one BizTalk administration console.
I rename the different BizTalk groups for easy recognition (Dev. group, Acc. Group and Prod. group).
In the above statement we see that the default groupname “BizTalk Group” is still being used. Hence, no certificate
can be found. On my clean development machine the installation succeeded because I didn’t changed the groupname.
After renaming the groupname back to “BizTalk Group” the problem was solved.

This means that the AS2 pipeline component is using the default groupname “BizTalk Group” hardcoded to search for the signing certificate.
And therefore you cannot rename the BizTalk groupname if you want to make use of AS2 with message signing enabled.

BizTalk has out of the box abilities to expose itself as a web service (exposing as a schema / orchestration) or consume custom web services. The web service wizard is an easy way to setup the web services during development but what if you would like to deploy the web service.

The most obvious way would be to add the virtual directory as a resource to the BizTalk application (for more info follow this link). This way the website will be part of the MSI and will be deployed together with the BizTalk artifacts. An alternative solution would be to use MSDeploy to deploy your web services. MSDeploy is part of the Web Deployment Tool and enables you to package an existing website (including content, configuration, certificates and databases). MSDeploy contains both a command-line interface as a GUI.

Command-line interface example

To package a website the following command should be executed:

Msdeploy.exe –verb:sync –source: iisApp=”Default Web Site\My Sample WebService” –dest:package=”M:\Projects\MySampleWebService.zip”

To import the package on the web server the following command should be executed:

Msdeploy.exe –verb:sync –source:package=”M:\Projects\MySampleWebService.zip” –dest:iisApp=”Default Web Site\My Sample WebService”

GUI interface example

After installation of the Web Deployment Tool a new menu item is available in IIS. Right click the web site/virtual directory that you want to deploy and go to “Manage Packages”.

Select “Export Application” to package the web site/virtual directory to a zip file.

To import a package on a web server select “Import Application“.

Every BizTalk administrator has probably already received complaints from BizTalk operators that operators can see the number of suspended messages but can’t view the message itself. Another common remark might be that the operators cannot use the Orchestration debugger. Debugging in production is not advised but with the debugger you can at least verify where the orchestration has halted.

Off course, for organisations where IT operations needs to comply with security regulations like Sarbanes Oxley or other compliance rules, the Microsoft best practices for BizTalk security apply. These can be found here. When these compliance rules do not apply for your organisation and after checking with the security responsible you can tackle these problems.

 The super operator

There is a way to make it possible for operators to view and save messages with the administration console and let them use the orchestration debugger. Most of the BizTalk security resides in SQL server. In the form of two roles: BTS_OPERATOR for the Operators and BTS_ADMIN_USERS for the BizTalk Administrators. These roles are defined at database level. They can be found in every BizTalk database.

When configuring the BizTalk group these roles are created for operators and administrators with the relevant permissions (securables) on database objects. The Windows groups specified for operators and administrators in the BizTalk group configuration are given a SQL login and granted the accompanying role.Each role has its own securables. These securables are permissions on objects such as stored procedures and tables.

The BizTalk Administrators have a lot more of these securables. Here under you will find the steps to creating a super operator role that delivers extra permissions to operators, for example the permission to save messages.

 
1. Create a windows group for the super operator.

First we need to create a windows local group, if you do not use active directory accounts and groups, or an active directory group.
Add the members who deserved the super operator rights. These members must already be member of the operator windows group.
This because the super operator group is only an extension to the operator permissions.

 
2. Create the SQL login for the super operators.

• Open the SQL management studio and connect to the SQL server that is hosting the BizTalk group databases.
• Open the server security tab and create a new login by right clicking login and selecting new login.
• In the login textbox you specify the group you created in step 1.
• On the user mapping tab you check the checkbox for every BizTalk database.

In this way a user is created for the group in every BizTalk database.

 
 3. Create the super operator role.

A role must be created for the super operator in the necessary biztalk databases. In this scenario we only need to create a role in the BizTalkManagementDB and the BizTalkMessageBoxDB.
For other scenarios it might be possible to create such a role in the BAM databases too.

• In the SQL management studio expand the Messagebox Database and right click on the roles node.
• Select new database role.
• Name this role BTS_SUPEROPERATOR. The owner can be DBO.
• Add the group you created in step 1 to the role members.
• Do the same in the BizTalkManagement database.

 
 4. Adding the securables for saving/viewing messages permissions.

In the messagebox database doubleclick the super operator role. Open the tab securables and add the securables according to the screenshot following these steps:

• Click add.
• Select specific objects, click ok.
• In object types check the stored procedures checkbox, click ok.
• Click the browse button and put a check next to the stored procedures you see in the screenshot.
• Select every securable one by one and grant the role the execute right.

Now you need to add the securables to the role in the management database.
Follow the steps above but this time add the securables seen in the next screenshot.

That’s it. With the new super operator group created we have an extra level of security. This can be really handy because there are only two roles out of the box. Now there are regular BizTalk operators, BizTalk super operators with save permissions and the BizTalk administrators.

 
5. Adding another permission, the Orchestration debugger.

To give the newly created BizTalk super operators this additional permission you just have to add some extra securables to the SQL super operator role. Add these securables to the super operator role in the BizTalkDTADb, and grant the execute right to the role for each securable:

We will continue to search for extra permissions to add to the super operator role. These permissions will be posted soon. If you have also found out which securables accompany certain rights or if you have any questions about this topic, feel free to comment them on this post.

This article will give a short and easy explanation on how to use the WCF-Oracle adapter to retrieve a result set from an Oracle database.

The first thing we need to have on the Oracle database is a stored procedure that returns an object of the type ref-cursor.
This article will give some examples on how to program them. The second thing we need to do is to install an oracle client.
This has to be done so we can add an oracle service that can connect to the database, this service will be used later on to retrieve the metadata.
Installing the client will also add the necessary oracle drivers. In this example we added the service Oracleblog with the right settings for our database.

Now we can start developing.
The first step of the development is to generate the metadata and the request-response schemas.
We start by creating a BizTalk project in Visual Studio. After having created the project, we right-click the project and choose “add generated item”. Now we choose “Consume Adapter Service” to start the wizard. This new wizard was added in Visual Studio by installing the WCF-Oracle adapter.

In the wizard we first choose OracleDBBinding for “Select a binding” then we click “configure” to configure the database settings. The first thing we do is adding the name of the service we created in the oracleclient in the field DataSourceName on the pane URI. After this we can add the required credentials. Now making the connection by clicking “connect” will show all the databases available. We have to find the right stored procedure and add this to your BizTalk project. In our example we added the stored procedure P_SEARCH.

Adding it to your project will give you two things:

The request-response schema
The binding file for your send port

In this example two schemas are generated, this is because one schema is referring to the other. Both request and response schemas are created in the same XSD-file. Now we can create a simple orchestration in which we make a mapping to the request schema and create a request-response port so we can send the request and receive the file with the record set.

Now we deploy the project to add the orchestration to the BizTalk server. The next thing we do is creating the proper send-receive port for sending to the oracle database. We do this by importing the binding file that was generated with the schemas in to our BizTalk application. This will create the send-receive port with the correct action- and database settings. We now make a file receive and send port and bind this to our orchestration.

We are now ready to send the message.

In this post I will give a brief overview of some problems that arise during deployment of BAM activity updates. In particular when partitioning is enabled for the activity the error below can occurre upon regeneration of the view that union the partitions.

“All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists”

When using BAM often the first deployment contains just a few data items and milestones. But when users become aware of the possibilities of this technology they want to build richer queries. Therefore I frequently need to add data items and milestones in my existing activity definitions. Of course business users don’t want to lose the existing BAM data. So I can almost never redeploy the activity definition which would delete all the data.
Now the bm.exe tool provides me with an option to update an activity. In this case no data would be lost. But after playing around for a while with this ‘update-all’ feature I discovered some problems. 
 
Let me explain what happens if one would like to add a data item to an activity which is already deployed and for which a view is created.
 
In my test environment I called my activity “BAMdeployment” and my view “BAMdeploymentView”. After first deployment the activity contains one data item StartData and two milestones Start and Stop. In the second deployment I want to add a StopData item to the activity and view. First I modified my spreadsheet and then exported the updated definition file.
 
Because I don’t want to lose data, I used the update-all command. Seems straightforward but I got the following error.

 
After looking around on the net I found some posts about this error and the solution is to first remove the view before running the update-all command. So after removing the view, the update-all completed without errors. The new data item StopData became visible in my view. Fine! But not for long.
 
The tests I did till now were done on a small set of data. But in a real scenario I probably have already a lot of instances of my activity in the BAMPrimaryImport database. Hence BAM archiving/partitioning becomes an issue.
 
After deploying an activity an SSIS package is created for partitioning and archiving the data. Here you can find the theory about this partitioning. Its all about performance.
 
When I tried to repeat the ‘update-all’ deployment strategy on my activity but now with partitioned tables I ran again into trouble. I got a new error.

 
 
Digging into this I ended up with a view in the BAMPrimaryImport database with the name bam_BAMdeployment_CompletedInstances. This view runs a UNION statement for every partitioned table. My custom view (the one defined in the spreadsheet) is afterwards build upon this one. This guarantee that all the data within my predefined activity window will be shown in the BAM portal.
 
Now the problem is that the partitioned tables are not updated with the new data item StopData. So the regeneration of the view bam_BAMdeployment_CompletedInstances fails. Only the first table bam_BAMdeployment_Completed has the new StopData column. While all the subsequent partitioning tables have not. 
 
To fix this problem all new data items and milestones must be added manually in the partitioning tables. While there will never be more than 254 partitioned tables with every activity, this is definitely an exhausting and error prone job to do. So I wrote a script to help you a little bit. Just fill in the name of the activity and the new data item or milestone you want to add. Be sure that you use the same names as given in the definitionfile of the activity. The script will update every partitioning table. 
 

All at once, the right order of actions to take

1/ Update the activity in the spreadsheet and export the definitionfile.

2/ When partitioned tables exist use the script to add the new items.

3/ Remove the views defined for the activity.

4/ Execute the bm update-all command.


-- Script to update the partition tables after adding a data item or milestone to the activity definition.

EXEC ('ALTER TABLE ' + @instancePartition + ' ADD ' + @column + ' ' + @columnType)
    
FETCH NEXT FROM partition_cursor
INTO @instancePartition
END
END
GO


Personally I found that BAM is one of the most useful features in Microsoft BizTalk Server 2006. It brings down the gap between business and IT. Unfortunally like most BizTalk architects/developers I struggled with deployment on my production environment. If you don’t need to keep the data for historical reasons or analysis then deployment of a BAM flow is easy and straight forward. You can just delete your flow and recreate it again. In case your customer requires you to keep your data then deployment becomes trickier.

For those scenario’s our team created the following check list:

1. Change data type of a Business Data field is not possible. The field needs to be dropped and recreated again.
   
2. Change of a group is not possible. The group needs to be deleted and recreated
   
3. Update will not give an error but nothing will be changed
   

    

The headers of the check list reference the different actions that one can do with the bm.exe tool.

Some examples:

Remove Business Data field from activity

In this example there are 2 definition files:

• SampleBAMDefinitionFile V1.1.1.1.xml: contains the current deployed BAM activity
• SampleBAMDefinitionFile V1.1.1.2.xml: contains the new BAM activity without the Business Data field

In order to update BAM the following commands should be executed:

bm.exe remove-all -DefinitionFile:”SampleBAMDefinitionFile V1.1.1.1.xml”
bm.exe deploy-all -DefinitionFile:”SampleBAMDefinitionFile V1.1.1.2.xml”

Remove Business Milestone item

In this example there are 2 definition files:

• SampleBAMDefinitionFile V1.1.1.1.xml: contains the current deployed BAM activity
• SampleBAMDefinitionFile V1.1.1.2.xml: contains the new BAM activity without the Business Data field

   

In order to remove the Business Milestone from the view only the following commands should be executed:

bm.exe remove-view –Name:SampleBAMView
bm.exe update-all -DefinitionFile:”SampleBAMDefinitionFile V1.1.1.2.xml”

In case the Business Milestone needs to be removed from the activity then the above logic will not work. In this case the following commands should be executed:

bm.exe remove-all -DefinitionFile:”SampleBAMDefinitionFile V1.1.1.1.xml”
bm.exe deploy-all -DefinitionFile:”SampleBAMDefinitionFile V1.1.1.2.xml”