Azure BizTalk FlatFile Decoder to XML Map

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

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

1. Dont try to validate the FlatFile Schema.
2. 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

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:

or

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

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"

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

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