Category: BizTalk

BizTalk

Journey of enabling BizTalk BAM alerts via office 365 SMTP

Manjunath

I’m writing this blog as i couldn’t get straight forward steps neither from msdn nor from google. I had to refer basic of SQL database mail, RTA – real time aggregation, few of the blogs together.

My requirement was to set BAM alert on aggregated value which exeeds limit of 1000. I had built an activity, view and dimension for recording number of requests mades.

First Issue: Activity deployment error using BM.exe

Updating Activity… Done.
EXEC : Updating View… error : The BAM deployment failed.
SQL Analysis Services 2008 Enterprise Edition is not configured. Can not create OLAP cubes for RTAs.

Cause and resolution: This is main caused as RTA (real time aggregation) was enabled in excel sheet. There is small icon in the excel bam template which need to be unchecked. RTA is not support in SQL standard version and supported only in enterprise version.

capture.jpg

Whats next: Now how to get near real time experiance on SQL standard version?

Solution: Create a job, under SQL agent, add steps to run two DTS packages from SSIS. Put schedule as run as every minute or once per 2 minute.

BAM_AN_*, BAM_DM_*

Capture1.JPG

Capture2.JPG

Additionally, if you are unable to see aggregation data then either your RTA is disabled and Aggregation jobs are not running. Aggregation packages doesnt run automatically for scheduled aggregation. we need to create a job in SQL agent.

Whats next: Issues runing the job as BAM user

Error: Connecting to the Integration Services service on the computer “XXXX” failed with the following error: “Access is denied.”

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

Solution: Dont waste time in granting permissions in component services for integration services (as many articles says), Instead create a proxy user in SQL server and and run the agent job under proxy user. Proxy user can be created under proxies of SQL server agent

capture3.jpg

Capture4.JPG

Whats next: BAM alert jobs failure. Unhandled exception caught. Stoppping the service.

Error: System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘bam_Metadata_GetProperty’, database ‘BAMPrimaryImport’, schema ‘dbo’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

Application: BAMAlerts.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.Data.SqlClient.SqlException
at System.Data.SqlClient.SqlConnection.OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1<System.Action>)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(System.Data.SqlClient.TdsParserStateObject, Boolean, Boolean)
at System.Data.SqlClient.TdsParser.TryRun(System.Data.SqlClient.RunBehavior, System.Data.SqlClient.SqlCommand, System.Data.SqlClient.SqlDataReader, System.Data.SqlClient.BulkCopySimpleResultSet, System.Data.SqlClient.TdsParserStateObject, Boolean ByRef)

Solution: 

Go to “Stored Procedures”, right-click on the “bam_Metadata_GetProperty” procedure to grant permissions on, and then select “Properties” option.

From Stored Procedure Properties, select the “Permissions”

And to grant permissions to a user, database role, or application role you must click the “Search” button.

  • In this case, we need to give execution privileges to the database role “BAM_ManagementNSReader

Reference: https://blog.sandro-pereira.com/2014/02/05/microsoft-biztalk-bam-management-bammanagerexception-encountered-error-while-executing-command-on-sql-server-domainuser-system-data-sqlclient-sqlexception-the-execute-permissi/

Whats next: Configure SQL BAM profile to use office 365 (as we didn’t wanted to use local SMTP server nor from IIS SMTP Email feature)

Solution: No need to reconfigure BizTalk configuration wizard. just update SQL database mail via “Configure database mail” and “view, change, or delete an existing account” from default bam profile. update only SMTP server and credentials

Capture5.JPG

Whats next: Configure BAM alerts in BAM portal.

for simple value: Set alert will only get enable if you add one record in filter against which you want to create alert.

Capture6.JPG

Aggregated value: Its really hard to find out from msdn how to create alert on aggregated values. we have click on the value to create alerts for respective row and aggregated column

Capture7.JPG

Add subscribers either as email or as File

Whats next: Email alerts was throwing error

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 22 (2019-10-11T15:50:51). Exception Message: Cannot send mails to mail server. (Transaction failed. The server response was: 5.2.0 STOREDRV.Submission.Exception:SendAsDeniedException.MapiExceptionSendAsDenied; Failed to process message due to a permanent exception with message Cannot submit message. 0.35250:0A000981, 1.36674:0A000000, 1.61250:00000000, 1.45378:02000000, 1.44866:051F0000, 1.36674:0E000000, 1.61250:00000000, 1.45378:0A1F0000, 1.44866:86020000, 16.55847:72100000, 17.43559:0000000004020000000000000000000000000000, 20.52176:140F2B890E00101043050000, 20.50032:140F2B897E17000000000000, 0.35180:48050000, 255.23226:0A000E81, 255.27962:0A000000, 255.27962:0E000000, 255.31418:0A000F81, 0.35250:0A000000, 1.36674:0A000000, 1.61250:00000000, 1.45378:02000000, 1.44866:32000000, 1.36674:32000000, 1.61250:00000000, 1.45378:37000000, 1.44866:01000000, 16.55847:9E000000, 17.43559:0000000000030000000000000000000000000000, 20.52176:140F2B890E0070200A001481, 20.50032:140F2B897E1710106B050000, 0.35180:0A001581, 255.23226:4800D13D, 255.27962:0A000000, 255.27962:32000000, 255.17082:DC040000, 0.27745:75050000, 4.21921:DC040000, 255.27962…).

Solution: This one was the hardest of all as error message couldnt tell where exactly was the issue. Apperently, it was an issue with Email From field, but couldn’t get where was the error. I spent lot of time on this and identified as an issue with BAMalerts.exe. This program has been written to read the Email “From” field from table from dbo.bam_Metadata_Properties database BAMPrimaryImport

SELECT PropertyValue FROM dbo.bam_Metadata_Properties WHERE propertyname = ‘AlertMailFrom’

Updating value in this table will get the Email From field corrected.

VOILA, BAM alerts are working with office 365 now! Hurray!

 

BizTalk

Biztalk – Adapter handler creation error

Manjunath

Error:

CreateBizTalkAdapterHandler : WCF-WebHttp Receive Handler for IsolatedHost could not be created: Exception calling
“Invoke” with “2” argument(s): “Access denied “.Exception.ToString()
At C:\vstsagent\A1\_work\r1\a\xxx\ConfigureHosts.ps1:356 char:1
+ CreateBizTalkAdapterHandler ‘WCF-WebHttp’ ‘Receive’ ‘IsolatedHost’ …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,CreateBizTalkAdapterHandler

Inner exception:
Type: System.Management.ManagementException
Access denied
at System.Management.ManagementException.ThrowWithExtendedInfo(ManagementStatus errorCode)
at System.Management.ManagementObject.Put(PutOptions options)
at System.Management.Instrumentation.SchemaNaming.RegisterAssemblySpecificDecoupledProviderInstance()
at System.Management.Instrumentation.SchemaNaming.RegisterAssemblySpecificSchema()
at System.Management.Instrumentation.ManagementInstaller.Install(IDictionary savedState)
at System.Configuration.Install.Installer.Install(IDictionary stateSaver)
at System.Configuration.Install.Installer.Install(IDictionary stateSaver)
at System.Configuration.Install.AssemblyInstaller.Install(IDictionary savedState)
at System.Configuration.Install.Installer.Install(IDictionary stateSaver)
at System.Configuration.Install.TransactedInstaller.Install(IDictionary savedState)

 

Solution: 

Added user to Biztalk administrator and SSO administrator group.

For other issues refer : https://docs.microsoft.com/en-gb/windows/win32/wmisdk/wmi-troubleshooting

BizTalk, C#

Static variables are non serlizable by default – A Common problem in BizTalk solutions

Manjunath

Below error appears on restart of host instance manually or due to the automatic restart of host instance due to DTC issue (Also know as Host instance recovering its state due to network issues or crashing of instances)

Shape name: ShapeName
ShapeId: d0e0ca4a-cd4b-44d2-a662-526f65665ad5
Exception thrown from: segment 1, progress 37
Inner exception: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

OR

Object reference not set an instance of an object.

Problem: Static variable are not serlizable. This is the standard problem in BizTalk solution where static variables are used within class libraries..

Solution:

With Serialization, we can only serialize properties that are:

  • Public
  • Not static
  • Not read Only

In this case, if you want to serialize by adding new variable “no1_Serialize“, you must wrap it, like this:

[Serializable]
public class Numbers
{
    public int no;
    public static int no1;
    public SubNumbers SubNumber;

    public int no1_Serialize {get {return no1;} set {no1 = value;} }
}

Ref: https://stackoverflow.com/questions/17222900/serialize-object-along-with-static-member-variables-to-xml


		


			
		
	






	
	

		BizTalk
Context “WCF.Headers” is cached in BizTalk Engine
	


	

							

			 Manjunath		
		
		

			
The scenario where we encountered this issue was during Salesforce integration. Salesforce expects custom header to be passed in the soap envolope.


Issue: INVALID_SESSION_ID: Invalid Session ID found in SessionHeader: Illegal Session. Session not found, missing session hash: wbecQmJucxUexYVBpM7E2jqTFaqEPZRWpOdXMu2wJC8= This error usually occurs after a session expires or a user logs out. 




Current :- 


 


msgQueryAllReq(WCF.Headers) = “<headers><SessionHeader xmlns=’urn:partner.soap.sforce.com’><sessionId>” + SessionId +  “</sessionId></SessionHeader></headers>”;


New/Solution:-


msgQueryAllReq(WCF.OutboundCustomHeaders) = “<headers><SessionHeader xmlns=’urn:partner.soap.sforce.com’><sessionId>” + SessionId +  “</sessionId></SessionHeader></headers>”;


Keep Takeaways:


    

  1. WCF.Headers is cached by BizTalk engine.
    2. 

  2. SessionHeader should have namespace urn:partner.soap.sforce.com
    3. 

  3. Session Inactivity time under profile/user level overrides organisation level timeout settings.
    4. 

  4. Session Management setting in Salesforce gives created and valid until time for every session.
    5. 

  5. Session ID in login response, session ID in session management and session hash code are linked 1-1.
    6. 

  6. Session hash code is unique for any given session ID in Salesforce
    7. 



 


 
 
 
  
 Request contains different Session ID due to Caching at BizTalk engine level. 
 
 
  
  


 


References:


https://docs.microsoft.com/en-us/biztalk/core/using-soap-headers-in-wcf-messages-with-orchestrations


https://social.msdn.microsoft.com/Forums/en-US/aa8958fe-6b64-427d-9c40-89e516304d5b/biztalk-cached-value-not-getting-changed?forum=biztalkgeneral


 

		


			
		
	








	
	

		BizTalk
Deleting Isolated host Active instance
	


	

							

			 Manjunath		
		
		

			
declare @IsolatedHostName nvarchar(128)

declare @uidInstanceID uniqueidentifier

declare @uidServiceID uniqueidentifier

declare @fKnownInstance int

declare @spname nvarchar(512)


Begin Tran

set @IsolatedHostName=’BTS_WEBSERVICES_ISOLATED_HOST’

set @uidInstanceID=’d6414769-30c1-41a2-b9ee-f930bfc01ec0′ –InstanceID

set @uidServiceID=’683AEDF1-027D-4006-AE9A-443D1A5746FC’ –ServiceClassID

set @spname= ‘int_AdminTerminateInstance_’ + @IsolatedHostName

exec @spname @uidInstanceID, @uidServiceID, @fKnownInstance OUTPUT

DELETE FROM InstancesPendingOperations WITH (ROWLOCK) WHERE uidInstanceID = @uidInstanceID OPTION (KEEPFIXED PLAN)


Commit Tran

		


			
		
	






	
	

		Azure Integration Services, BizTalk, Technology and tricks
Basic use cases in XSLT
	


	

							

			 Manjunath		
		
		

			

Basic programming use cases in XSLT



Use case
Relevant XSLT elements




Creating nodes
xsl:element, xsl:attribute, xsl:text, xsl:comment, xsl:processing-instruction




Copying nodes
xsl:copy-of, xsl:copy




Repetition (looping)
xsl:for-each




Sorting
xsl:sort




Conditional processing
xsl:choose, xsl:if




Computing or extracting a value
xsl:value-of




Defining variables and parameters
xsl:variable, xsl:param




Defining and calling subprocedures (named templates)
xsl:template, xsl:call-template




Defining and applying template rules
xsl:template, xsl:apply-templates, xsl:apply-imports




Numbering and number formatting
xsl:number, xsl:decimal-format




Debugging
xsl:message




Combining stylesheets (modularization)
xsl:import, xsl:include




Compatibility
xsl:fallback




Building lookup indexes
xsl:key




XSLT code generation
xsl:namespace-alias




Output formatting
xsl:output




Whitespace stripping
xsl:strip-space, xsl:preserve-space





		


			
		
	






	
	

		BizTalk
Pipeline Component Load() and Save() error
	


	

							

			 Manjunath		
		
		

			
Error:


Pipeline Component gives error “Pipeline componet Load() method failed on IPersistPropertyBag implementation: <name of the Pipeline component>”  while dragged on one of the stages for pipeline


LoadPipelineComponentError


Pipeline Component gives error “Pipeline componet Save() method failed on IPersistPropertyBag implementation: <name of the Pipeline component>”  while trying to save the pipeline


SavePipelineComponentError


Solution: Implement the Argument exception handling. Do not use third party dll in the argument exception handling


Microsoft.BizTalk.Component.Interop.IPropertyBag pb;

string propName;




object val = null;

try

{

pb.Read(propName, out val, 0);

}

catch (System.ArgumentException argEx)

{

//Do not use third pary dll logging

    return val;

}

catch (System.Exception e)

{

//Can use any third party dll logging

throw new System.ApplicationException(e.Message);

}

return val;

		


			
		
	






	
	

		BizTalk
Pipeline Component, Seekable stream issue
	


	

							

			 Manjunath		
		
		

			
One of my projects we were working on the the Archiving pipeline component. we want to file in any stage of the pipeline component, hence we had it as any type


[ComponentCategory(CategoryTypes.CATID_Any)]

[ComponentCategory(CategoryTypes.CATID_PipelineComponent)]


Error faced:


There was a failure executing the send pipeline: “<Pipeline assembly>” Source: “<Pipeline Component name>” Send Port: “<Send port name>” URI: “URI path” Reason: ForwardOnlyEventingReadStream does not support Seek()


Cause:


usage of stream (System.IO.Stream) object when it is non seekable. when the stream object becomes non seekable the data in the stream get lost. when tried to retreive data from the stream the above error appears.


Solution:


Use seekable object from the class Microsoft.BizTalk.Streaming.ReadOnlySeekableStream. This ReadOnlySeekableStream is overrided implemenation (Inherited class) ofthe stream  and handles seekable stream.


//Donot use stream objects


originalStrm = new ReadOnlySeekableStream(inmsg.BodyPart.GetOriginalDataStream());


//After Catch use this

finally

{

if (binWriter != null)

{

binWriter.Flush();

binWriter.Close();

}

if (originalStrm != null)

{

if (!originalStrm.CanSeek)

{

ReadOnlySeekableStream seekableStream = new ReadOnlySeekableStream(inmsg.BodyPart.GetOriginalDataStream());

seekableStream.Position = 0;

inmsg.BodyPart.Data = seekableStream;

inmsg.BodyPart.Data.Position = 0;

}

else

{

originalStrm.Seek(0, SeekOrigin.Begin);

inmsg.BodyPart.Data = originalStrm;

}

}

}

		


			
		
	






	
	

		BizTalk
Implementation of macro in Pipeline Component
	


	

							

			 Manjunath		
		
		

			
Below are few of the macros and implementation in .Net/BizTalk.net. This can be directly used in the archiving pipeline component


“%datetime%” -> DateTime.Now.ToString(“yyyy-MM-ddTHHmmss”);

“%datetime_bts2000%” -> DateTime.Now.ToString(“yyyyMMddHHmmsss”); 

“%datetime.tz%” -> DateTime.Now.ToString(“yyyy-MM-ddTHHmmsszzz”).Replace(“:”,“”); 

“%MessageID%” -> InMsg.MessageID.ToString();

“%SourceFileName%” -> InMsg.Context.Read(“ReceivedFileName”, “http://schemas.microsoft.com/BizTalk/2003/file-properties&#8221;).ToString();

“%time%” -> DateTime.Now.ToString(“HHmmss”).Replace(“:”, “”);

“%time.tz%” -> DateTime.Now.ToString(“HHmmsszzz”).Replace(“:”, “”); 


Implementaion of the searching pattern to search the pattern %<any alpha number or . >%


string pattern = @”[%][\w|.]*[%]”;

string strMatch = “”;

foreach (Match match in Regex.Matches(FileNameWithMacro, pattern))

{

ReadMacro(match.Value); //This will be method implmentation of replacing macro with actaull file content.

FileName = FileName.Replace(match.Value, strMatch);

strMatch =“”;

}