Journey of enabling BizTalk BAM alerts via office 365 SMTP

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!

 

Advertisements

Azure devops – Powershell – passwords

Error:
2019-09-23T18:56:36.7532247Z ##[command]. ‘C:\vstsagent\A1\_work\r1\a\xxxxx\ConfigureHosts.ps1’ -BizTalkHostGroup “BizTalk Application Users” -BizTalkHostInstanceAccount “xxx\SVC_xxx” -BizTalkHostInstanceAccountPassword “***” -BizTalkIsolatedHostGroup “BizTalk Isolated Host Users” -BizTalkIsolatedHostInstanceAccount “xxx\SVC_xxx” -BizTalkIsolatedHostInstanceAccountPassword “***”
2019-09-23T18:56:37.3226214Z ##[error]Invoke-Expression : At line:1 char:393
+ … xxxx” -BizTalkIsolatedHostInstanceAccountPassword ” &JDN+K …
+                                                                 ~
The ampersand (&) character is not allowed. The & operator is reserved for future use; wrap an ampersand in double
quotation marks (“&”) to pass it as part of a string.
Solution:
Problem is not with & instead all special charecter needs escape sequence. In my case i had ` and ” in the password which needed to be replace with escape sequence.
replace ` with ` ` and ” with “”

Biztalk – Adapter handler creation error

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

Creating String extension function

public static string ToCamelCase(this string str)
{
if (!string.IsNullOrEmpty(str) && str.Length > 1)
{
return Char.ToUpperInvariant(str[0]) + str.Substring(1);
}
return str;
}

Usage:
string text = “test data”;
string camalCaseText = text.ToCamelCase();

Output: Test data

Function declaration should be done outside of main class.

https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/how-to-add-custom-methods-for-linq-queries

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

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 “no1”, 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

 


			

Context “WCF.Headers” is cached in BizTalk Engine

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. SessionHeader should have namespace urn:partner.soap.sforce.com
  3. Session Inactivity time under profile/user level overrides organisation level timeout settings.
  4. Session Management setting in Salesforce gives created and valid until time for every session.
  5. Session ID in login response, session ID in session management and session hash code are linked 1-1.
  6. Session hash code is unique for any given session ID in Salesforce

 

 

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

 

Deleting Isolated host Active instance

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

Automated Build terminologies

  • Build automation is essentially automating how the software is built instead of manually invoking the compiler. This would be accomplished via tools such as Makefiles or Ant.
  • Deployment automation is less well-define but involves taking your built software and “deploying” or “installing” it on a test or production system.
  • Continuous integration means having an automated process build your software continuously as developers check in code. For example, every 15 to 30 minutes a server might wake up, look for new check-ins, and build the project if any changes were made.
  • Continuous delivery is a combination of CI and DA where the software builds from CI are also deployed to a test system.

Basic use cases in XSLT

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