BizTalk Connect

Azure BizTalk FlatFile Decoder to XML Map

2017-01-08T08:35:00.000-08:00

In the previous post we created the Flat File XML from the FlatFile decoder.
http://biztalkconnect.blogspot.no/2017/01/azure-biztalk-flatfile-decoder.html
Now we will map it to the XML Schema.

So first we need to create the map in Visual Studio.
Once the map is created( you can test it using the FlatFIle XML we got back in the last post).
Test map will not work with Flatfile in native format...it has to be XML Input...its buggy.

Once you have created the map you need to upload it to the Azure Portal(Integration Account).
Azure doenot support .btm file.
So you need to Debug the map and upload the .xsl file it creates to your (Integration Account).

So lets upload the .xsl file to Azure(Integration Account).

Now we need to go to the Logic App we created last and Add the "Transform" shape to it where we will call this map to convert the FlatFile Xml to another mapped Xml Output.
Important here is to configure the map.
Content: Input to Map: which will be output(XML) from Flat File Decoding.
Map: Select the map we just uploaded to our Integration Account. It wil show in the dropdown window if the Integration Account is linked to the Logic App.

Run the Logic App. It will show some Errors but dont worry all is well until now.

Now we will test the map using Postman with the same Flat File input.
In the map I am concetenatiing FirstName and LastName.
Just a note: There are many BizTalk functoids like Database Functoids missing in the toolbox...seems Microsoft is still adding stuff to the map.

Voila it works. The FullName contains the FirstName and LastName.
I noticed Richard used some Azure Functions to call the maps.I did not needed any. Looks like I need to look into Azure functions or seems Microsoft has changed the Transform Shape recently.

Finally I have uploaded the Schemas/map/Test file out here.
Just download it and test it using Azure.
https://sites.google.com/site/knishtechweb/home/downloads/IntegrationAccount1.7z?attredirects=0&d=1

Azure BizTalk FlatFile Decoder

2017-01-07T15:59:00.000-08:00

Hi,
Recently one of my new probable customer wanted to convert CSV file to Xml.
So I recommended Biztalk but they dont have an On Premise Biztalk Server and dont plan one.
So I recommended using BizTalk in Auzre as a Service the so called "Enterprise Integration".
The only catch was I never worked with it so had no idea what it could offer.
So here we start looking into what challenges I faced.
I went through some good articles from Richard and Steve which pretty much gave me a head start but could not achieve what they achieved in the blog...thats why I am not an MVP ;) .
https://seroter.wordpress.com/2016/09/09/trying-out-standard-and-enterprise-templates-in-azure-logic-apps/
http://blogs.recneps.org/post/Processing-a-flat-file-with-Azure-Logic-Apps
https://docs.microsoft.com/en-us/azure/app-service-logic/app-service-logic-enterprise-integration-flatfile

So kepeing things short I will just show you few do's and dont's which you should be careful with.
So the scenario here is to first just convert a flat file to Xml.

1. First thing is to download the "Microsoft Azure Logic Apps Enterprise Integration Tools for Visual Studio 2015 2.0"
https://www.microsoft.com/en-us/download/details.aspx?id=53016

Here in Visual Studio -> New Project it looks similar to BizTalk but with only 3 options when you try to "Add New Items" after creating the project.

As a BizTalker one clearly thinks of the Flat File wizard....yes we have it here.
The Flat File Schema is actually the Flat File Wizard.
Sandro has explained it better here
https://sandroaspbiztalkblog.wordpress.com/tag/flat-file-schema/

So i did next is created the Flat File Schema called EmpCsv.xsd

If you want to can create the map and map the FF Schema to Xml schema also.
But I will not show the mapping in Azure in this blog post.

Very important

Dont try to validate the FlatFile Schema.
Dont try to Test the map using Flatfile csv input.

It fails as its buggy.
So if you want to test the map you can test it using Flat file Xml as an input.
So how to get the Xml from the Schema?
Thats what I used 1 complete evening finding out.

So lets assume everything works and we are ready to deploy to Azure.
Remember I have not tested the map...I dont know if the Flat file validates against the Schema.

So now its Azure time.
1.Here we need to first create an "Integration Account" where we will add our Schemas and Map.

2. We will add the Schemas and maps to the Integration Account

3. Now we will create a new blank "Enterprise Integration" Logic App.

4. Associate the "Integration Account" to created Logic App.
You can go to properties of the Logic App created and link it.

5. Now we start creating the Logic App in the Logic App Designer.
Add the Request Shape.

In the Request shape only the important thing is the makred in yellow(URL). Need to copy it later on when we want to send the flatfile for testing.
Nothing else need to be done here in body. Leave it blank.

Click on the Next Step -> "Add an Action" and Add the "Flat file Decoder" Component.
We need to fill 2 boxes here
1. Content
Click on the Content textbox it should popup the option as to use body(Output from the Request Step) as input .
2. Schema Name:
If the Integration Account is connected...it should show up the Schemas we added to the Integration Account.
Select the FlatFile Schema which we want to convert to Xml(Think of it as Flat file pipeline decoder stage: Select Schema and done converted to Xml).

Click on the next Step and Add "Response" Shape which will return us the Flatfile in XML format.
We need to fill only the Body with output from Flat File Decoder.

This is how it should look like.
Copy the URL from Request shape.

2. Testing
Now we are ready to test.
Click "Run" on the Logic App Menu to start the App.
It will show some error in the FlatFile decoder but dont worry it wont cause any problem.

You need to get a testing tool form here
https://www.getpostman.com/

I used Chrome addon.
So you need to paste the URL
Select "Post"
Click on "Body"
Click on "raw"
Paste the Flaltfile in the TextBox and Click Send.
The Response will be shown below.

So we converted the FlatFile to Xml.
Finally I have uploaded the Schemas/map/Test file out here.
Just download it and test it using Azure.
https://sites.google.com/site/knishtechweb/home/downloads/IntegrationAccount1.7z?attredirects=0&d=1

In the next Post I will show how to map this FlatFile to a Xml file and return it.
http://biztalkconnect.blogspot.no/2017/01/azure-biztalk-flatfile-decoder-to-xml.html

Errors: BizTalk MQSeries Adapter

2014-10-22T12:18:00.000-07:00

Error No1:

Error:
Unable to load file C:\Program Files (x86)\Microsoft BizTalk Server 2013\MQSAgent.dll
Process Name: dllhost.exe
Comsvcs.dll file version: ENU 2001.12.8530.16385 shp during component registration. File does not exist.

Solution:

This is not that dangerous Error. So dont waste much time on it.

Error No2:

Error:
The adapter "MQSeries" raised an error message. Details "The ProgID MQSAgent2.MQSAgent was not recognized by the system. Please try reinstalling.".

Solution:

MQSAgent2 is not installed. Run the installation of MQSeries Agent from BizTalk installation.

Error No3:

Error:
The adapter "MQSeries" raised an error message. Details "The adapter has encountered an 'Access Denied' error while attempting to contact the COM+ object on the MQSeries server. Ensure the BizTalk account is added to the Role on the MQSAgent COM+ application.".
Solution:
The IBM MQ service is stopped or the User account does not have access to the IBM MQ Queue or the BizTalk service account does not have access to MQSAgent2.
Add the BizTalk Account to the creator role on the MQSAgent2 Com+ Service component.

Error No4:

Error:
When we click the dropdown of Queue Manager we get this error "QueryQueueManagers".

Solution:
This is not that serious error. Just type in the Queue name manually and click Ok.

Error No5:

Error:
The Adapter is not installed or configured correctly on the server localhost.

Solution:
The MQSAgent2 is not installed/configured correctly.
Install the MQSeries Agent from the BizTalk installation software setup.exe.

Error No6:

Error:
Faulting application name: dllhost.exe, version: 6.1.7600.16385, time stamp: 0x4a5bc6b7
Faulting module name: KERNELBASE.dll, version: 6.1.7601.18409, time stamp: 0x53159a86
Exception code: 0xc06d007e
Fault offset: 0x0000c42d
Faulting process id: 0x1930
Faulting application start time: 0x01cfe9fe626a902f
Faulting application path: C:\Windows\SysWOW64\dllhost.exe
Faulting module path: C:\Windows\syswow64\KERNELBASE.dll
Report Id: a01bddd5-55f1-11e4-b5d9-005056bc0ee6
Faulting package full name: %14

Faulting package-relative application ID: %15

Error:
The system has called a custom component and that component has failed and generated an exception. This indicates a problem with the custom component. Notify the developer of this component that a failure has occurred and provide them with the information below.
Server Application ID: {C691D827-19A0-42E2-B5E8-2892401481F5}
Server Application Instance ID:
{A704A9B9-7982-4319-AF54-33CACC90E65C}
Server Application Name: MQSAgent2
The serious nature of this error has caused the process to terminate.
Exception: C06D007E
Address: 0x76FFC42D

Reason:
Dll Crash Error every 3 seconds after activating Receive location. This error occurs if you manually executed MQSConfigWiz.exe the from the BizTalk installation folder.
You should always execute it via BizTalk Setup.exe and activate "MQSeries Agent".

Solution:
Uninstall all IBM products(MQ Server, MQ Client or MQ Fix pack if any).
Repair BizTalk (dont worry you wont loose you applications/Configuration).
Install IBM MQ Server.
Install MQSeries Agent(very important) from BizTalk setup.exe.
Configure accounts properly.
Try again by acitvating the Receive location.

Error No7:

Error:
Error encountered as BizTalk attempts to retrive a message from MQSeries, description = Error encountered on Queue.Get Queue name = TestQ Reason code = 2354. hresult = 0XC0C1100C. This error will also be reported on the BizTalk server.

or
27.11.2014 11:14:26 - Process(7120.11) User(xxxxxx) Program(amqzlaa0.exe) Host(xxxxxx) Installation(Installation1) VRMF(7.1.0.3) QMgr(TestQM)
Domain controller unavailable.
WebSphere MQ was unable to contact the domain controller to obtain information for user 'network service@NT AUTHORITY'.
Ensure that a domain controller for the domain on which user 'network service@NT AUTHORITY' is defined is available. Alternatively, if you are using a computer which is not currently connected to the network and have logged on using a domain user ID, you may wish to log on using a local user ID instead.

Solution:
Set Transaction Support=False in the BizTalk MQ Series Receive location properties.

Error No 8:

Error:
Entity 'username@domain' has insufficient authority to access object QueueManager.

Reason:
I got this error when I tried to create a new Queue Manager after installing MQ Server. We are missing rights on the MQ Server. I was surprised as I used the same user to install MQ Server.

Solution:
Add the user to the local mqm group which gets created automatically after installing MQ Server.

Using MQSeries Adapter in BizTalk 2013

2014-10-21T02:26:00.000-07:00

Recently I wanted to Integrate BizTalk 2013 with a queue on IBM MQ Server 7.1.
The only blog which helped me a bit was from Steve Melan. Steve tried to help me fix the issues over Teamviewer but the system gave such wierd errors like dll crashing with not much error info.
I managed to fix it after working on a single dll crash error for 3 days.
Thanks Steve for writing so much on MQ Server and BizTalk Integration which atleast gives some insight of whats going on inside the hood but he missed writing a simple how to document which a new beginner( in IBM world) like me can follow blindly. That what I asked for first when I got in touch with him...so my life could be made easy.
I am writing this post so that it can help others and contact me if you have any issues.
I will defintly try to help you out as Steve tried to help me out on short notice over phone/Teamviewer/Skype meetings.

Scenario:
Server details:
Windows 2008 R2 SP1
BizTalk 2013 with CU2.
BizTalk is configured with remote SQL Server 2008 R2 SP2.

Plan is to install IBM Webshpere MQ on BizTalk Server so BizTalk will send messages to Local IBM MQ Server which will transfer messages to remote IBM MQ Server queue on Linux Server.
There are 2 ways to Integrate BizTalk with IBM MQ Server.
1. Using Inbulit MQSeries Adapter.
2. MQSC BizTalk Adapter from Host Integration Server Installation.

I integrated it using the Inbulit BizTalk MQSeries Adapter.
So here is what was installed to make it work
1.Installed BizTalk 2013.
2.Configured BizTalk 2013.
3. Installed IBM MQ Server 7.1.0.3 on BizTalk Server.
4. Installed MQSeries Agent from the BizTalk installation.
5. Configured Receive location to pick messages from queue using MQseries Adapter.

1. Installed BizTalk 2013.
MQSeries Adapter gets installed by default with installation of BizTalk 2013.
When I installed BizTalk 2013 I could not figure out as to why MQSeries agent is grayed out.
It was missing some prerequisites....but no clue as to what it was.
It is very important that we are able to configure MQSeries Agent.
When you install MQSeries Agent it installs MQS Agent2 Com+ service in Component Services via which the MQSeries Adapter communicates with IBM MQ Server.

Image from MSDN link below

Structure of the MQSeries Adapter
So now you understand how important it is to configure MQSeries Agent....we do not need the MQ Client on the BizTalk Server.
When it was grayed out I tried directly installing MQSeries Agent by executing the file MQSConfigWiz.exe from BizTalk installation source folder.
It creates the component but you will get wierd errors like dll crashing.

3. Install IBM MQ Server on BizTalk Server.
I installed a trial version of MQ Server 7.1 which was downloaded from IBM website.
IBM MQ Server 7.1.0

Important document if you get Access Denied Error which I am sure you will get.
IBM MQ on Windows

Network Configuration:
Select No if its a standalone machine(BizTalk Server) without any domain controllers.

Select yes if your BizTalk Server is in a environment where you have network and domian controllers. Normally it is yes as we always have BizTalk Server in network domain.

Click on Launch IBM Websphere MQ Installer. It is just click next process...same as we install Microsoft products.

If you are trying on a local pc with no domain select "No". I selected yes to show you what is next.

Important here is to type in the correct Domian Username.
I entered the same username under which the BizTalk Host was configured to run under.

I could not identify first if its a MQ client or Server which was installed... but if you look at comments "IBM websphere MQ for windows".

Next I added a test message to the queue.

4. Now we will install the MQSeries Agent. If it is still grayed out means you did not install MQ Server correctly or you are missing some prerequistes :) . Nobody knows the exact set of prerequisites needed. Google will not help much here.
This is where I was stuck at and nothing helped...I had MQ Client and MQ Fix pack all on the server....So I uninstalled everything from IBM and only installed IBM MQ Server 7.1.

I entered the same username under which the BizTalk Host was configured to run under.

Once configured successfully it will create a MQSAgent2 in Component Services.
This MQSAgent2 Com+ service acts as a bridge between the MQSeries Adapter and IBM MQ Server. It should always be started.
To edit the configuration you should uncheck "Disable Changes" in Properties->Advanced .
Microsoft MQSAgent link
I did not do any changes to MQSAgent2.

Once the receive location is configured it will pick the messages from the queue.

It looks so simple right?...It took me 1 week to make this simple thing work :)
The BizTalk host account should have full access to the Queue.

In the next post I will focus on all the Errors I received and how to fix them.
MQSeries Errors
Finally thanks to our fellow BizTalker Steve Melan for the moral support he gave me when I saw wierd errors like dllhost.exe crashing with not much error info/RPC failing etc.

Failed to create the SQL database Source="SSO"

2014-09-20T14:35:00.000-07:00

Recently I tried to configure BizTalk 2010 on which Visual Studio 2010 was already installed.
I could not configure SSO and got the following error message in the log file.

Failed to create the SQL database Source="SSO" HelpID=""><Exception Message="(0xC0002A21) An error occurred while attempting to access the SSO database.

It also said to check the eventlog for more details but there was no error in Eventlog.

Finally I traced the issue that it was because of the missing folders on the SQL Server machine so SQL Server could not create the databases and log files.
I found it out by using SQL Profiler and activating all types of Events.

I knew of many SSO Errors so tried everything like registering SSOSQL.dll and other fixes but never imagined it could be because of SQL not been configured properly.

BizTalk 2013 R2 database migration from SQL 2012 to 2014

2014-08-25T03:07:00.000-07:00

On my Virtual PC which had BizTalk 2013 R2 configured with SQL 2012 as database server I thought of installing SQL 2014 and try using the new features of Column Store and see how it can help run the Integrations faster.
In this post I will show you how I migrated my existing BizTalk databases from SQL 2012 to 2014 which is installed on the machine.

Take Backup of all BizTalk databases from SQL 2012 and Restore it on to SQL 2014.

Create Scripts of all BizTalk SQL Jobs from SQL 2012 and execute it on to SQL 2014.

Create Scripts of all BizTalk SQL Logins from SQL 2012 and execute it on to SQL 2014.

Update the SampleUpdateInfo.xml file found in the following location with info of the current and the new SQL Server. For me current SQL 2012 was on KIWI server and the New SQL 2014 Server is on KIWI\SQL14.
C:\Program Files (x86)\Microsoft BizTalk Server 2010\Bins32\Schema\Restore

Run the UpdateDatabase.vbs file which is found in the same folder which updates the Server references in the tables on the new SQL Server.

Run the UpdateRegistry.vbs file which is found in the same folder which updates the System Registry key references on the Server.

Finally I went into BizTalk Admin Console to check if the references were updated successfully and yes it did update everything.

Finally I would give credit to the following old Microsoft document which still holds true for the new versions of BizTalk\SQL Server ;).
How to Restore Your Databases

Upgrading bug: BizTalk 2010 to BizTalk 2013 R2

2014-07-09T04:19:00.000-07:00

Hi,
Upgrading has been made easy with newer versions of BizTalk. I recently upgraded the BizTalk software from 2010 version to 2013 R2.
Here are few screenshots....Important is the last screenshot.. :)

Here it talks about Pre-Actions required.
Stop WorldWide Web Publishing Service.
Start Enterprise SIngle SignOn Service.

Since I had selected to install Developer Tools also...it gives me an Error with missing prerequisites.
I installed Visual Studio 2013 and then continued on.

"Buckle your seatbelts....there are no Airbags to save you in this crash ride".
Microsoft will not rollback the databases if the Upgrade fails.
I did ensure that I had a backup of all the databases.

Forgot to stop RuleEngine Update Service.

And Finally the surprise...........

Seems Microsoft forgot to rename the Folder from "BizTalk Server 2010" in Program Files to "BizTalk Server 2013 R2" but product got upgraded successfully.

Hope they will fix this soon ;).

BizTalk 2013 Installation Bug: Enterprise Single Sign-On Error

2014-01-27T05:26:00.003-08:00

While Installing BizTalk I received the following Error that Enterprise Single Sign-On should be installed manually.

Well this had not occured while installing previous versions of BizTalk. So I was a little bit curious as to what am I missing on the Server.

So I started installing the redistributable prerequisites manually as this is what BizTalk installation does first.

What I noticed is

After installing .Net Framework 4.5 from the .cab file I tried to run the BizTalk installation again as normal and selected the .cab file I downloaded.

And it worked fine and BizTalk 2013 was installed successfully with Enterprise Single Sign-On and all other features.

So seems Enterprise Single Sign-On installation needs .Net Framework 4.5 to be in place before it can be installed and BizTalk Installation is messing it up somewhere while installing the redistributable prerequisites.

BAM configuration failure in BizTalk 2013

2014-01-27T04:18:00.000-08:00

HI,
Seems the BizTalk 2013 Installation software has not been tested properly.
I got many errors which I could google and fix it but this one was odd about BAM configuration failed with following error.

ERROR: Encountered error while executing command on SQL Server "".A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)The system cannot find the file specified (Microsoft.BizTalk.Bam.CfgExtHelper.Utility)
[ Error BAMConfigWizExt]

Solution was to install the latest Cumulative update and try again without Server restart and it worked fine and BAM was configured correctly.
BizTalk Cumulative Update Link

Till now Core Product configuration(SSO,Group,Runtime) was not messed up by Microsoft in all versions (2004 till 2010) I installed/configured. I normally used to install Cumulative update after I am done configuring default BizTalk features or maybe it was a wrong way of doing it :) .

ItemTemplate missing

2012-12-31T23:30:00.000-08:00

I had this wierd Error where I could not add a new Orchestration to my project.

I did not find the template for it when I clicked Add New Item.

Luckily I remembered I had installed a hotfix a day before and that could have messd the Template bindings.

So I knew I had to install only the templates again.

I did it by running the following command as mentioned in the link below and it helped.

http://msdn.microsoft.com/en-us/library/ms241279(v=vs.80).aspx

devenv.exe /InstallVSTemplates

I restarted Visual Studio and my templates were back again.

BizTalk 2013 Beta released!

2012-11-21T06:06:00.002-08:00

The BizTalk Server Team recently announced the arrival of the new beta version of the next generation of Integration Product of BizTalk Server called BizTalk Server 2013.
Its a major release lined up with SQL 2012,Visual Studio 2012,Windows 2012.
For more information read here.
http://blogs.msdn.com/b/biztalk_server_team_blog/archive/2012/11/06/announcing-biztalk-server-2013-beta.aspx

BAM Add-In Excel Issue : Unspecified Automation Error

2012-10-03T00:51:00.000-07:00

"Unspecified Automation Error" when Opening Excel using BAM Addin.

Fixit Available
There is a fixit available for this problem:
Office 2010 w SP1 - http://support.microsoft.com/kb/2597986
Office 2007 w SP2 or SP3 - http://support.microsoft.com/kb/2687441
Office 2003 w SP3 or Office 2003 Web Components w SP3 - http://support.microsoft.com/kb/2687323

Steps to fix the problem manually
The manual fix for this problem is to re-register MSCOMCTL.OCX. To do this, follow these steps:

1. Open an administrative-level command prompt. The command prompt MUST be on the administrative level for this to work. Click START, and type RUN in the run command line. On Windows Vista and Windows 7 you will see CMD.EXE appear in the quick search list. Right click CMD.EXE and left click Run As Administrator. A black command prompt opens. On Windows XP, there is no built-in run-as-admin, but you can use the run as menu option (or runas command line) to execute an admin command from a Limited User Account.

2. If on a 64-bit machine, type the following:
regsvr32.exe /u C:\Windows\SysWOW64\MSCOMCTL.OCX

Hit ENTER

then type
regsvr32.exe C:\Windows\SysWOW64\MSCOMCTL.OCX

If on a 32-bit machine, type the following:
regsvr32.exe /u C:\Windows\System32\MSCOMCTL.OCX

Hit ENTER

then type
regsvr32.exe C:\Windows\System32\MSCOMCTL.OCX

Your Excel BAM Addin should work again after doing this.

FYI,

http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2012/08/15/quot-unspecified-automation-error-quot-after-applying-ms12-060.aspx

Business Rule Engine: Database Lookup, .Net Helper Class

2012-06-24T13:46:00.002-07:00

I was googling for a blog article which could show me the simplest way of implementing BRE Rules with Database lookup and .net Helper Class. Most of the samples talk of writing everything in a Helper Class with logic to call the Rule. Rest talk of using Facts to pass on the .net Helper Class Instance. Some samples are limited to only Rule Store.....but not about how to call it from Orchestration.

Here I would like to discuss a simple common situation which one comes across while implement Rules in BRE.

In the Screenshot below I have 2 Rules which lookup 2 different tables.

I also have a .net Helper Class method just to convert the string Xml field to GUID since the AccountID field in the table is of Type GUID and the Xml field is of type string.

The Rules are easy to construct...Just Drag/Drop fields from the XML Schema and the Database table and the XML Helper Class.

The Xml Static Helper Class works in the Business Rules if we add the following Registry key or else the Rules will get executed but the BRE will not output any Error and the HelperClass condition will be skipped.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BusinessRules\3.0\StaticSupport (DWORD)

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\BusinessRules\3.0\StaticSupport (DWORD)

To test the Policy ...right click the Version and click Test Policy. Add instance of all inputs(Databases/Xml File but not of .Net Helper Class(It will read from GAC).Now on Click of Test button the Rule will be executed and the Input Xml file will be modified and will act as the Ouput file.

Lets look into how to call the Rule from the Orchestration and the code in the Xml Helper Class.
Call Rules Shape only work in Atomic Shape Scope, So we need to add the Scope and set it to Atomic in the Scope Properties.

I have add the Expression shape where I set the input parameters to the Call Rules Shape.

SQLConn is the connection to the Database which is used in the Input.

RulesDataConn is the input parameter for the table.

Northbound is a dummy database I have used which contains a table called CustomerAddressBase and AccountExtensionBase.

I am using these 2 tables in 2 different Rules.

I also created the variable for .Net Helper class and pass it on to the Call Rules Shape as below.

Here is is how it looks in the configuration of Call Rules Shape.

Now we have Configured the Orchestration. Below I will show you the type of Variables used above.

Here is the code of the .Net Helper Class which just converts the string from the Xml field to GUID field.

Note: The Helper Class should be static and Serializable as it used in Atomic Shape.

We do not need to create Facts as we are using Static Methods in the Helper Class.

Deploy the Rule from the Rule Store and Deploy the Orchestration and GAC the Helper Class.

After deployment link the Policy to the Application in BizTalk Admin Console.

Now drop the file and Rules should be executed and the Xml file will be created.

This tutorial is just for reference and not a Sample Lab to be followed. It will just help you if you missed any steps in your Project.

Microsoft has many good Samples but without any proper documentation to explain the steps involved ...it only has deployment and test guide to test the Sample.

Hope this guide helps you in implementing BRE using Microsoft Business Rule Composer.

BAM Add-In Excel Issue : Object library invalid or contains references to object definitions that could not be found.

2012-06-14T04:27:00.001-07:00

Recently when I opened Excel every time I got a weird Error for BAM Add-In.
Incidently I have not messed up with BAM recently for sure. After googling a bit I could not find any post related to BAM Add-In issue but many with VBA macros failing after a Cumulative update.
My laptop is set to auto update the system with latest updates as I love to try new features out in Microsoft market and this Cumulative update had messed up the macro settings in Excel.
http://support.microsoft.com/kb/957924/en-us

The point here is if you get Errors related to BAM Add-In Excel macro while opening Excel just follow these steps
To resolve the problem, delete cached versions of the control type libraries.
To do this, search the hard disk for “.exd,” and then delete all occurrences. The .exd files are re-created automatically by using the new controls the next time that you use VBA.

After deleting all .exd files(there were only 2 in AppData folder) things were back to normal and BAM Excel Add-In worked fine.

BizTalk 2010 - CRM 2011 Errors and Resolution

2012-05-30T04:02:00.001-07:00

Hi,
I just thought of posting few common and few uncommon Errors :) you might face during Integrating BizTalk with CRM 2011.

<Fault xmlns="http://www.w3.org/2003/05/soap-envelope"><Code><Value>Sender</Value><Subcode><Value xmlns:a="http://schemas.microsoft.com/net/2005/12/windowscommunicationfoundation/dispatcher">a: DeserializationFailed </Value></Subcode></Code><Reason><Text xml:lang="en-US">The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://schemas.microsoft.com/xrm/2011/Contracts/Services:entity. The InnerException message was 'There was an error deserializing the object of type Microsoft.Xrm.Sdk.Entity. The value '' cannot be parsed as the type 'Int32'.'. Please see InnerException for more details.</Text></Reason></Fault>

Resolution: The File send to CRM has a field with wrong format. Ie the field in CRM expects a Integer Value but a string is send. Check CRM for "Whole Number" fields or Option Set fields. Even Option Set fields needs number as a int without any characters like "," or "." . Change the XSLT value script in BizTalk map to have xs:int as datatype for the Integer field.

For a Float Field in CRM = xs: double in BizTalk value mapping XSLT.

I will keep updating this Post with new Errors encountered in CRM.

Database Lookup Functoid : Multiple Where Clause : CRM PickList field

2012-05-30T04:00:00.000-07:00

When integrating CRM I came across a Scenario while mapping a PickList field in CRM using BizTalk. Since we need to send the Index (AttributeValue) of the Value in the Xml to CRM for the PickList field I had to do a look up first to get the Picklist value Index.
Assume my Query is
Select top 1 from StringMap where Value='Staff' and AttributeName='lucky_title'
Staff comes from the Title field in the Input Xml.

Now in traditional Database Lookup Functoid we know about single where clause parameters but what if we have a multiple where clause?
Here is the StringMap table in CRM 2011

In that case what we have to do is add a String Concatenate Functoid or a Scripting Functoid for the Multiple Column and Multiple Value parameter with a separator.
I have used a Scripting Functoid.
select top 1 * from StringMap where Value + '|' + AttributeName = Title|lucky_title

CRM 2011 Attribute Lookup: Integrating BizTalk with CRM 2011

2012-04-15T13:43:00.000-07:00

Hi Friends,
I have been working on a CRM 2011 Integration using WCF Adapter from BizTalk. Richard Seroter has done a very good job by posting 2 Articles on it which clears most of the basics.
Past few days I have been struggling to get this Lookup thing right. I tried the solution which Richard mentioned but seems he dint test it well...it fails. I had also posted over msdn forum for help but nothing came up.
http://social.msdn.microsoft.com/Forums/en-US/biztalkgeneral/thread/3b08958a-cd8e-480d-9b35-eccbdfca04f8/#3b08958a-cd8e-480d-9b35-eccbdfca04f8

But this German link helped me a lot. I had to translate it to English but it was worth every penny.
http://www.msdynamics.de/viewtopic.php?f=60&t=12408

So what was the solution?
I had 2 Script functoids as shown in the map below as Richard has in the basic one first Series of CRM Integration.
http://seroter.wordpress.com/2011/02/10/the-good-bad-and-ugly-of-integrating-dynamics-crm-2011-and-biztalk-server-2010/

The C# Script in Functoid1 returns the name of the Key(Attribute Name) which is simple.

The important thing is mapping the value field where we lookup the other Entity which is shown as below

The Script is as below.

This XML works fine against CRM
LogicalName is always the name of the Entity.

<ns0:Create xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" 
   xmlns:ns3="http://schemas.microsoft.com/xrm/2011/Metadata" xmlns:ns5="http://schemas.microsoft.com/2003/10/Serialization/" 
   xmlns:ns1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlns:ns4="http://schemas.microsoft.com/xrm/2011/Contracts">
 <ns0:entity><ns4:Attributes><ns4:KeyValuePairOfstringanyType>
  <ns1:key>lucky_accountsbylistid</ns1:key>
  <value xsi:type="a:EntityReference" xmlns="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlnss="http://www.w3.org/2001/XMLSchema" 
      xmlnssi="http://www.w3.org/2001/XMLSchema-instance" xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts">
   <a:Id xsi:type="ser:guid"  xmlns:ser="http://schemas.microsoft.com/2003/10/Serialization/">
   4B4B105F-6B82-E111-A667-00155D067401</a:Id><a:LogicalName>lucky_loadlist</a:LogicalName>
 <a:Name />
</value></ns4:KeyValuePairOfstringanyType>
</ns4:Attributes><ns4:LogicalName>lucky_accountbylistvalidationrel</ns4:LogicalName></ns0:entity></ns0:Create>

So if you would like to work with CRM lookup its easy but hardly any resources online and strangely nothing from Microsoft on documentation regarding Integrating CRM 2011 with BizTalk.
I wasted good enough days banging my head to make this lookup work but its all is worth when you see it working finally.
Just mail me if you have any questions regarding Integration CRM 2011 using WCF Adapter in Biztalk.
Finally thanks Richard for post such valuable content regarding Integrating CRM 2011 using WCF BizTalk Adapter.

Hope this post helps you all!

Bm.exe is not a valid Win32 App

2012-01-28T03:34:00.000-08:00

Hi,
Recently I had an Issue with BAM Bm.exe. I tried to run it through command prompt got a wierd error sayign its not a Valid Win32 Application. I thought its an issue since I installed BizTalk in 32 bit mode on a 64 Bit Windows 7 machine. On googling found nothing ..seems nobody encountered it ever. Finally I thought google wont help looked at bm.exe and found the file size was 0.
Seems it got corrupted somehow. By replacing it with a new bm.exe solved the problem.

So basics do help when google fails to help you.

Exposed Orchestration as Web service returning void?

2011-11-19T04:50:00.001-08:00

Hi,
I was working on an Integration Scenario where we needed to call the BizTalk Orchestration from MS Access.
SO first thing started looking at how could we could send request from MS Access to Biztalk. We wanted a Synchronous Scenario where MS Access sends Request and waits for a Response.
The best option we had was to Expose the Orchestration as Webservice.
We did that and after googling we understood

MS Access cannot consume a WCF Service.
To Comsume a BizTalk WCF Service we need to create a Proxy Class.

So finally we thought to settle down for normal SOAP Web-services and exposed our Orchestration as Soap Web-service.

It looked good but we planned to make it more easy to be called from MS Access we thought to have the Web-service Input/Output parameter as string.

So MS Access will send XML in string and get response XML i nstring object.

So we made the changes in Orchestration and republished the Orchestration as Webservice and tried to consume it from a WebService testing tool caleld WCF Strom.

The input was passed on and the Webservice returned void though in BizTalk I noticed that I am returning the right XML.

After googling I found this link where it says we need to fine tune the Webservice to work it out.

http://stackoverflow.com/questions/6627798/biztalk-web-service-to-return-a-value-rather-than-void-ref

I made the changes in my Webservice created by the Webservice Publishing Wizard.

And finally got the Response as string.

Hope it helps you in working with Exposing Orchestration as Webservices.

Message as Mail Attachment?

2011-09-16T13:35:00.000-07:00

How to send any message as a mail attachment?

No need to create Multipart messages.
No need to set any parameters.

Just set the Dynamic SendPort SMTP properties like.

UserName,Password,Hostname,To email address.

You can find more settings here

http://geekswithblogs.net/mapfel/archive/2006/11/24/98761.aspx

Important thing is using the Mime Encoder in the Send Pipeline if you want to send attachments.

It seems like I tried setting the property in Orchestration as
msgXmlDocumentOut(SMTP.MessagePartsAttachments) = 1;

It dint work.Finally I added the PipelineComponent and it worked fine. You can also set the FileName Overriding the Mime.FileName property as shown below.

public static void SetMimeFileName(Microsoft.XLANGs.BaseTypes.XLANGPart msgpart, string MimeFileName)

{

msgpart.SetPartProperty(typeof(MIME.FileName), MimeFileName);

}

Global Context Field which is independent of a Message

2011-09-03T13:18:00.000-07:00

I was thinking of writing about a Context field which is independent of message. Imagine a Scenario where you would like to promote a field only for routing no matter what the incoming file content is, you know one thing for sure that there is a field in the content of the file(FF,XML,EDI) which has the data for routing to destination system. In this case it never makes sense to create a Schema and promote the field as Distinguished or Property field and only for routing you don’t even need to create an Orchestration.Message In….. MessageOut ….simple J.

So I thought to show you how simple is it to do with BizTalk.

Create a new BizTalk project and add a new Property Schema which will have our Global PropertySchema field.
Let’s add a field called Property1 to this Property Schema. Set the PropertySchemaBase as MessageContextPropertybase. So by this we say that the field Property1 will not be dependent upon the message content or it won’t be linked to any field in the message.
This namespace below is important. This is the namespace in which this property field (Property1) will be promoted globally.
Deploy the solution to BizTalk so our global property Schema is in BizTalk.
You can promote this field from any pipeline or Orchestration as shown below. pInMsg.Context.Promote(“Property1”,"https://BizTalk_Server_Project3.PropertySchema1", "Test");
If you want to access it from Orchestration then it’s important to know the .Net Namespace the property belongs to as shown below in the properties.

The Property field (Property1) can easily be accessed from the Orchestration with the .Net namespace. strProp=Msg_XmlDocumentIn(BizTalk_Server_Project3.Property1);

Simple enough…promotions are important as they can save lot of your design time to plan complex Integration's. Promotions can help you route any message from a ReceivePort to SendPort but I limit the use of such uncontrolled scenarios and use Orchestration as it gives me full control over the message for complex scenarios… ;) .

SQL Receive Adapter: Multiple tables in different Database

2011-05-11T03:45:00.000-07:00

Last week I had a simple scenario where BizTalk Server will use a SQL Receive Adapter and poll a SQL Stored Procedure(SP) which will return the rows from multiple tables spread across 2 different database.
Initially I thought the only catch out here will be to grant rights across all tables for the same User Account with which Biztalk polls the SP. Following were the last 2 lines of my proc.
SET @ReturnXML=SELECT * FROM ABC INNER JOIN DB2.dbo.XYZ as XYZ ON ABC.FIELD1=XYZ.FILED2 WHERE ABC.UpdateID=@UpdateID FOR XML AUTO,ELEMENTS
Select @ReturnXML
The SP worked perfect from SQL Query window but I started getting the following error when called by BizTalk SQL Receive Adapter.
Event ID: 5740
Description:The adapter "SQL" raised an error message. Details "HRESULT="0x 7a" Description="Description not provided"
I thought I am missing some access rights on the 'XYZ' table, I granted all possible rights for the user on the table but still the same issue. After struggling for 2 days I thought to concentrate only on what the SP returned in the last 2 lines shown above.I felt that BizTalk is having an issue with the data returned from SP as it wont care what you do in the SP.BizTalk is only interested in the results(rows) returned,So I removed the variable(@ReturnXML) and it worked. So SP now returns the SQL Query directly.
SELECT * FROM ABC INNER JOIN DB2.dbo.XYZ as XYZ ON ABC.FIELD1=XYZ.FILED2 WHERE ABC.UpdateID=@UpdateID FOR XML AUTO,ELEMENTS

Hope it helps you.

BAM Portal Activity Search: "This action cannot be performed because one or more database(s) appears corrupted"

2011-05-03T10:02:00.000-07:00

Hi,

I noticed this error when I tried to search on an Activity in the BAM Portal.

I googled and noticed a good description about this issue by Gilles but it did not help me resolve the problem.

http://blogs.msdn.com/b/gzunino/archive/2006/07/12/663790.aspx

So I thought of fixing it in the BAM (BAMPrimaryImport)database as it clearly said about duplicate entry. I searched on the ActivityID and found only 1 record in "bam_ActivityName_active" table and I also found 1 record in the "bam_ActivityName_completed" Activity table.
SELECT * FROM [BAMPrimaryImport].dbo.bam_ActivityName_active where ActivityID='{42B99A00-4A43-40D4-8F6F-183851984197}'

So I thought that its not a duplicate issue as the Error says as I saw only 1 entry in each tables.

But I was wrong.

The issue is that there should not be any pending records in the Active tables if the Activity is completed ie. if there exist a record in _Completed table for the same ActivityID.

In my case I intiated an Activity(BeginActivity) and Biztalk failed while updating the Actvity(UpdateActivity)and after that I completed the Activity(EndActivity). So there was an Orphan record in the _active table.

So I deleted all the records from _active table and it worked fine.

DELETE FROM [BAMPrimaryImport].dbo.bam_ActivityName_active

So this is what BizTalk means by Duplicate records.

"There should not be a record in the _active table if the activity has a record in the _completed table for the same ActivityID.If it exist then its a duplicate Error."

Error in Eventlog was as follows:
Event Type: Error
Event Source: BAM Portal
Description:
(BAMPortal.PortalApplication) Void LogAllErrors(System.Exception[]): System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: There are instances with duplicate ID '{42B99A00-4A43-40D4-8F6F-183851984197}' in activity 'xxx'. The duplicates must be removed from the database to fix this problem.
   at Microsoft.BizTalk.Bam.WebServices.Query.BamQueryService.GetInstanceData(String viewName, String activityName, InstanceQuery query, Int32 timeout)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Microsoft.BizTalk.Bam.WebServices.QueryService.BamQueryService.GetInstanceData(String viewName, String activityName, InstanceQuery query, Int32 timeout)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.QueryResultsObjectDataSource.ExecuteQuery(SQLQuery query)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.QueryResultsObjectDataSource.EnsureData(SQLQuery query)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.QueryResultsObjectDataSource.QueryResults(SQLQuery query)
   at BAMPortal.Search_aspx.ExecuteQuery()
   at BAMPortal.Search_aspx.executeQueryButton_Click(Object sender, CommandEventArgs e)
   at System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e)
   at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   --- End of inner exception stack trace ---
   at System.Web.UI.Page.HandleError(Exception e)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest()
   at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
   at System.Web.UI.Page.ProcessRequest(HttpContext context)
   at ASP.pages_search_aspx.ProcessRequest(HttpContext context)
   at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Happy Talking Biz. ;)

MIME.FileName= "Blank" ??

2011-04-21T13:59:00.000-07:00

Hi,

While working on an Integration to receive a file via email as attachment we normally use POP3 Adapter and MIME Decoder enabled So that the MIME Context fields are filled in properly. I googled on how does the POP3 Adapter populates it. This link helped me understand it better.

http://social.msdn.microsoft.com/forums/en-US/biztalkgeneral/thread/aff310cf-1f53-4d08-9193-9da5f4d53888

BizTalk uses the following logic to get the file name:
1. Take it from the Content-Description header. Some vendors don't populate the Content-Description header in the message sent to Exchange.
2. If not found, then the part name is based on the Content-ID header.
3. If still not found, then generate a GUID for the part name.

In our Scenario MIME.FileName was blank and the reason was the Mail Header was missing "content-disposition" field for Attachments.
Actual Header:
content-type: application/octet-stream; name=SampleAttachment.zip
content-transfer-encoding: base64
Expected Header:
content-type: application/octet-stream; name=SampleAttachment.zip
content-transfer-encoding: base64
content-disposition: attachment; filename=SampleAttachment.zip

Scott did shed some light on this issue out here

http://blogs.breeze.net/scotts/2008/05/26/MIMEFileNameNotPopulated.aspx

We had a discussion with the Vendor to populate content-disposition field for attachments in header but they were clear that their Mail Server guys will not accept this Change Request as the FileName is already available in ContentType field in the Mail header.Well actually the BizTalk MIME Decoder does populates it in "PartContentTypeSecondaryHeaderValue" .

So we handled it this way

if(MIME.FileName="")

filename = MIME.PartContentTypeSecondaryHeaderValue

I could not find much about this field.
Hope it helps you while working with FileNames in Mail Attachments.

Happy Integrating,
Nishil

Microsoft BizTalk Server 2009 Adapters for Host Systems 2.0 on BizTalk 2009 64 bit

2010-05-19T13:37:00.000-07:00

Hi All,
BizTalk Server 2009 Adapters for Host Systems 2.0 are used to communicate with Host Systems like DB2 Databases.

Do we need HIS to be installed to work with these adapters?

No,Host System Adapters can work with BizTalk just like normal adapters without HIS Server.

Are there any 32 bit or 64 bit Adapters for Host System?

The Host System Adapters installation files are by default 32 bit installers.
So you wont find any 64 bit installation files.

Does BizTalk 2009 64 bit support these Adapters?

No,Biztalk only supports these adapters in 32 bit mode.
http://msdn.microsoft.com/en-us/library/aa560166(BTS.10).aspx

	32-bit support	64-bit support
HTTP-based adapters (IIS)	Yes	Partial
BizTalk Host instances	Yes	Yes
Enterprise SSO	Yes	Yes
BAM portal (IIS)	Yes	No
SQL Server	Yes	Yes

So how we work with these adapters if we install BizTalk 2009 in 64 bit mode?

You can install 64 bit BizTalk on a 64 Bit Operating System like Windows 2008 R2 but you will have to configure BizTalk to run in 32 Bit mode only.

If you install the Adapters for Host System on a 64 bit BizTalk installed over a 64 bit Operating system,it will install successfully but you will not find those adapters(DB2...etc) in the Adapter list or on any ports.
So best option is to Install BizTalk in 64 bit mode but configure it to run in 32 bit mode using "BizTalk Configuration Wizard" and then install the Adapters for Host System.

Hope it helps you as it took few hours before I could figure this out and I did not get any Error while installing the Adapters and nor could I see them in the Adapter List.