Within those blog posts, I also provided at least one SQL script for monitoring the status of jobs – whether they were disabled or running as anticipated, such as every minute, every fifty minutes, and so forth. However, it has come to my attention this week that even though they were enabled and running as planned, some of them were not finishing with success, and I was not notified. For this reason, I decided to create a second SQL script to enhance the functionality of the earlier one. This new script is designed to search for any failures that have occurred in the last hour. It will then present me with the most recent failure details, including the error message, for each of the Jobs in an error state.
THIS SQL SCRIPT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND.
Hope you find this helpful! So, if you liked the content or found it helpful and want to help me write more content, you can buy (or help buy) my son a Star Wars Lego!
Author: Sandro Pereira
Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.
He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.
View all posts by Sandro Pereira
You may already know that I usually use the series A fish out of water when I want to write something that goes a little bit off-topic on my main blog topic: Enterprise Integration. This time, and despite this, can also be considered an Enterprise Integration – ETL process – I don’t consider myself a SQL Server “expert”. I often delegate these tasks to my data team. However, this week one of my clients call me regarding an issue we were facing with our integration platform, which is mainly composed of SQL Server, BizTalk Server, and Azure.
While diagnosing the problem – almost feeling like Dr. House – I realize we were not getting any new data because the ETL jobs failed with the error: There is already an active instance of this package.
That happened because we were controlling the execution of the package and not allowing multiple executions of the same package to coincide by doing the following validation.
IF (SELECT COUNT(*) AS ExecutionCount
FROM SSISDB.catalog.executions
WHERE status = 2
AND folder_name = ''
AND package_name = '.dtsx') > 0
BEGIN
THROW 50000, 'There is already an active instance of this package.', 1;
END
The problem was that I didn’t knew at that time how to monitor which SSIS packages were currently running and how to stop them. Because for some reason, I’m guessing network issues, those packages were kind of zombies. And I didn’t have my team available at that time. Because that was a production environment, I had to learn, which is also good! And this may be a helpful tip for other situations where we need to check what are the SSIS Packages running from the Catalog and want to subsequently stop them.
To accomplish that, we need to:
On current versions of Windows, on the Start page, type SSMS and then select Microsoft SQL Server Management Studio.
When using older versions of Windows, on the Start menu, point to All Programs, point to Microsoft SQL Server, and then select SQL Server Management Studio.
On the Object Explorer panel, expand Integration Services Catalogs, right-click on SSISDB, and select the Active Operations option from the options menu.
A new Active Operations window will open, presenting all the running packages
From this window, you can select the package you want and click the Stop button to force stopping that SSIS package execution.
It’s also possible to do the same process via T-SQL by running these to queries:
Query to retrieve all currently running packages in the SSIS.Catalog
SELECT * FROM SSISDB.catalog.executions WHERE end_time IS NULL
Query to stop the execution of a specific SSIS package
Hope you find this helpful! So, if you liked the content or found it helpful and want to help me write more content, you can buy (or help buy) my son a Star Wars Lego!
Author: Sandro Pereira
Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.
He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.
View all posts by Sandro Pereira
The last time I released a new version of my stencil, it was on January 26 of 2022. A long time ago indeed, so it is fair to say that I do need to release a new major version of my stencils, and that will be a long work and process. However, I decided to do this task progressively and release minor updates during this “journey”. This way, it becomes easier for me because I don’t need to spend long periods allocated to this task, and at the same time, all of you can start enjoying these new icons.
What’s new in this version? (for now)
The main goal of this release was to provide the new icons present in the Azure Portal, on the Power Platform, and new existing Services. In this version, the changes and additions are:
New shapes: New shapes added on MIS Databases and Analytics Stencils, MIS Azure Additional or Support Stencils, Microsoft Integration Stencils, MIS Azure Stencils, and MIS Power Platform Stencils;
SVG Files: Add new SVG files;
Special Highlights: Microsoft Fabric and the new Logic App Data Mapper
Microsoft Integration, Azure, Power Platform, Office 365, and much more Stencils Pack
Microsoft Integration, Azure, Power Platform, Office 365, and much more Stencils Pack it’s a Visio package that contains fully resizable Visio shapes (symbols/icons) that will help you to visually represent On-premise, Cloud or Hybrid Integration and Enterprise architectures scenarios (BizTalk Server, API Management, Logic Apps, Service Bus, Event Hub…), solutions diagrams and features or systems that use Microsoft Azure and related cloud and on-premises technologies in Visio 2016/2013:
BizTalk Server
Microsoft Azure
Integration
Integration Service Environments (ISE)
Logic Apps and Azure App Service in general (API Apps, Web Apps, and Mobile Apps)
Azure API Management
Messaging: Event Hubs, Event Grid, Service Bus, …
Azure IoT and Docker
AI, Machine Learning, Stream Analytics, Data Factory, Data Pipelines
SQL Server, DocumentDB, CosmosDB, MySQL, …
and so on
Microsoft Power Platform
Microsoft Flow
PowerApps
Power BI
Office365, SharePoint,…
DevOps and PowerShell
Security and Governance
And much more…
… and now non-related Microsoft technologies like:
SAP Stencils
The Microsoft Integration Stencils Pack is composed of 27 files:
Microsoft Integration Stencils
MIS Additional or Support Stencils
MIS AI and Machine Learning Stencils
MIS Apps and Systems Logo Stencils
MIS Azure Additional or Support Stencils
MIS Azure Mono Color
MIS Azure Old Versions
MIS Azure Others Stencils
MIS Azure Stencils
MIS Buildings Stencils
MIS Databases and Analytics Stencils
MIS Deprecated Stencils
MIS Developer Stencils
MIS Devices Stencils
MIS Files Stencils
MIS Generic Stencils
MIS Infrastructure Stencils
MIS Integration Fun
MIS Integration Patterns Stencils
MIS IoT Devices Stencils
MIS Office365
MIS Power BI Stencils
MIS PowerApps and Flows Stencils
MIS SAP Stencils
MIS Security and Governance
MIS Servers (HEX) Stencils
MIS Users and Roles Stencils
That you can use and resize without losing quality, in particular, the new shapes.
Download
You can download Microsoft Integration, Azure, BAPI, Office 365, and much more Stencils Pack for Visio from GitHub here:
Hope you find this helpful! So, if you liked the content or found it helpful and want to help me write more content, you can buy (or help buy) my son a Star Wars Lego!
Author: Sandro Pereira
Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.
He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.
View all posts by Sandro Pereira
Yesterday while troubleshooting a BizTalk Server developer environment at a client, I encountered an unusual error while trying to configure the BizTalk Server Backup job:
Could not find server ‘server name’ in sys.servers.
Based on the error description, and by the fact that I was trying to run a SQL Server job, I knew that the error should be on the SQL Server side, some incorrect configurations,
Cause
Why this problem start to happen is unclear to me, and at the time I didn’t have all the information available to understand that. However, after investigating a little this error type I realize that we can execute a SQL query to check out what is your linked server:
select * from sys.servers
Or
Select @@SERVERNAME
The problem was that once I run these scripts, I realize the server name was not the expected one, it was incorrect! For example:
I was expecting BTS2020LAB01.
But instead, I was seeing VMIMAGE01.
And that was causing the failure on the Backup job and other BizTalk server jobs.
Solution
To solve this issue we need to fix that server name and for that, we can apply the following SQL script:
sp_dropserver 'VMIMAGE01'
GO
sp_addserver 'BTS2020LAB01',local
GO
After applying this script, make sure you restart the SQL Server service.
Once you have done these steps, you can successfully start your BizTalk Backup job.
Author: Sandro Pereira
Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.
He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.
View all posts by Sandro Pereira
The full 7.3 version is complete with the release of v.7.3.1. This was a massive work of adding new shapes of new services that appear on Azure and changing the existing one with the new version of the shapes. Work is done and I hope you enjoy it!
What’s new in this version?
This is the list of changes and additions present in this release:
New shapes on MIS Azure Stencils, MIS Azure Additional or Support Stencils, MIS Developer Stencils, and MIS Security and Governance packages: add a considerable amount of new shapes of new services that appear on Azure has both changing the existing one with their new layout.
Move old versions of the shape layout to MIS Azure Old Versions package.
New shapes on MIS AI and Machine Learning Stencils: several new shapes add it to this package with several Cognitive Services.
New shapes on Microsoft Integration Stencils: some new shapes add it to this package describing Schemas, Maps, Aggrements, Partners, Assemblies and so on.
Lock the aspect ratio of the new stencil icons: This was a requested made that can be very handly to protects against accidental resizing with another shape aspect.
SVG files: new SVG files added.
Microsoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack
Microsoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack it’s a Visio package that contains fully resizable Visio shapes (symbols/icons) that will help you to visually represent On-premise, Cloud or Hybrid Integration and Enterprise architectures scenarios (BizTalk Server, API Management, Logic Apps, Service Bus, Event Hub…), solutions diagrams and features or systems that use Microsoft Azure and related cloud and on-premises technologies in Visio 2016/2013:
BizTalk Server
Microsoft Azure
Integration
Integration Service Environments (ISE)
Logic Apps and Azure App Service in general (API Apps, Web Apps, and Mobile Apps)
Azure API Management
Messaging: Event Hubs, Event Grid, Service Bus, …
Azure IoT and Docker
AI, Machine Learning, Stream Analytics, Data Factory, Data Pipelines
SQL Server, DocumentDB, CosmosDB, MySQL, …
and so on
Microsoft Power Platform
Microsoft Flow
PowerApps
Power BI
Office365, SharePoint,…
DevOps and PowerShell
Security and Governance
And much more…
… and now non-related Microsoft technologies like:
SAP Stencils
The Microsoft Integration Stencils Pack is composed of 28 files:
Microsoft Integration Stencils
MIS Additional or Support Stencils
MIS AI and Machine Learning Stencils
MIS Apps and Systems Logo Stencils
MIS Azure Additional or Support Stencils
MIS Azure Black and Gray
MIS Azure Old Versions
MIS Azure Stencils
MIS Black and Cyan
MIS Buildings Stencils
MIS Databases and Analytics Stencils
MIS Deprecated Stencils
MIS Developer Stencils
MIS Devices Stencils
MIS Files and Message Types Stencils
MIS Generic Stencils
MIS Infrastructure and Networking Stencils
MIS Integration Fun
MIS Integration Patterns Stencils
MIS IoT Stencils
MIS Office, Office 365 and Dynamics 365
MIS Power BI Stencils
MIS Power Platform Stencils
MIS SAP Stencils
MIS Security and Governance
MIS Servers (Hexagonal) Stencils
MIS Users and Roles Stencils
MIS API Connectors
Organisational Stencils
That you can use and resize without losing quality, in particular, the new shapes.
Download
You can download Microsoft Integration, Azure, BAPI, Office 365 and much more Stencils Pack for Visio from GitHub Here:
The full 7.3.0 version is not yet complete, but I decided to release it in small pieces instead of taking a long time to make all the planned changes and being the Azure part that will require the most work.
What’s new in this version?
This is the list of changes and additions present in this release:
New shapes on MIS: Office, Office 365 and Dynamics 365 package: add some new Dynamic 365 shapes like Dataverse, SCM Warehousing, Project Timesheet, Return To School orReturn To Work, and several new Office/Office 365 shapes.
Remove API Connectors shapes from MIS: Power Platform package: there goal was to simplify this package and migrate that stencils to a dedicated package, since there are more then 600 connectors. Also these connectores are common to Power Automate, Power Apps and Logic Apps.
Create a new package MIS: API Connectors: This package will provide stencils to all connectors currently provided for Microsoft Power Automate, Microsoft Power Apps, and Azure Logic Apps.
Lock the aspect ratio of the new stencil icons: This was a requested made that can be very handly to protects against accidental resizing with another shape aspect.
SVG files: new SVG files added.
Microsoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack
Microsoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack it’s a Visio package that contains fully resizable Visio shapes (symbols/icons) that will help you to visually represent On-premise, Cloud or Hybrid Integration and Enterprise architectures scenarios (BizTalk Server, API Management, Logic Apps, Service Bus, Event Hub…), solutions diagrams and features or systems that use Microsoft Azure and related cloud and on-premises technologies in Visio 2016/2013:
BizTalk Server
Microsoft Azure
Integration
Integration Service Environments (ISE)
Logic Apps and Azure App Service in general (API Apps, Web Apps, and Mobile Apps)
Azure API Management
Messaging: Event Hubs, Event Grid, Service Bus, …
Azure IoT and Docker
AI, Machine Learning, Stream Analytics, Data Factory, Data Pipelines
SQL Server, DocumentDB, CosmosDB, MySQL, …
and so on
Microsoft Power Platform
Microsoft Flow
PowerApps
Power BI
Office365, SharePoint,…
DevOps and PowerShell
Security and Governance
And much more…
… and now non-related Microsoft technologies like:
SAP Stencils
The Microsoft Integration Stencils Pack is composed of 27 files:
Microsoft Integration Stencils
MIS Additional or Support Stencils
MIS AI and Machine Learning Stencils
MIS Apps and Systems Logo Stencils
MIS Azure Additional or Support Stencils
MIS Azure Black and Gray
MIS Azure Old Versions
MIS Azure Stencils
MIS Black and Cyan
MIS Buildings Stencils
MIS Databases and Analytics Stencils
MIS Deprecated Stencils
MIS Developer Stencils
MIS Devices Stencils
MIS Files and Message Types Stencils
MIS Generic Stencils
MIS Infrastructure and Networking Stencils
MIS Integration Fun
MIS Integration Patterns Stencils
MIS IoT Stencils
MIS Office, Office 365 and Dynamics 365
MIS Power BI Stencils
MIS Power Platform Stencils
MIS SAP Stencils
MIS Security and Governance
MIS Servers (Hexagonal) Stencils
MIS Users and Roles Stencils
Organisational Stencils
That you can use and resize without losing quality, in particular, the new shapes.
Download
You can download Microsoft Integration, Azure, BAPI, Office 365 and much more Stencils Pack for Visio from GitHub Here:
While organizing my vast resources in my hard drive, I recently found out, polished, and improved two SQL Server queries that allow us to check the users and groups with access to BAM resources.
These are simple SQL Queries, but they are essential for maintaining your environment under control, security, and privacy standards compliance.
Generally, BizTalk Server is compatible with all the privacy standards-compliant like GDPR or FIPS. BizTalk Server is a messaging broker that doesn’t capture or store any data on its system other than for the time needed to complete business processes and connect and route messages to their target systems. However, because you can process messages and/or communicate with systems that contain sensitive data (personal data), you must have some good practices in BizTalk Server Applications to comply with privacy standards.
Business Activity Monitoring (BAM) is a collection of tools that allow you to manage aggregations, alerts, and profiles to monitor relevant business metrics (called Key Performance Indicators, or KPIs). It gives you end-to-end visibility into your business processes, providing accurate information about the status and results of various operations, processes, and transactions so you can address problem areas and resolve issues within your business. But it is also a component that can capture data from the messages passing by the systems, and some of these data can be sensitive – that shouldn’t happen, but it can happen.
So, it is always good in terms of security, control, documentation, and in some cases, privacy to know which users can access BAM data.
BizTalk Server: SQL Query to list all Users with access to BAMPrimaryImport database
This is a simple SQL Server Query that provides a list of all users that has access to the BAMPrimaryImport database.
THIS SQL SCRIPT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.
Completing the enormous saga, aka nightmare, to install BAM in one of my latest BizTalk Server installations and once I thought I solve all the issues… BAM strikes again! Now complaining about the following error:
Exception Message=”Microsoft (R) Business Activity Monitoring Utility Version 3.13.717.0 Copyright (C) Microsoft Corporation. All rights reserved.
ERROR: Failed to set up BAM database(s). Database ‘BAMAnalysis’ has compatibility level larger than 1103 with StorageEngineUsed set to InMemory. Tabular databases with compatibility level at 1200 or above must use StorageEngineUsed set to TabularMetadata.
My team didn’t follow the SQL Server installation, we just send to another team the requirements necessary for BizTalk Server and the installation guide, so we were a little surprised by this error.
And despite I never had seen this error before, the reason was quite simple: This error is caused by the SQL Server Analysis Services (SSAS) instance being installed using the tabular mode, and BizTalk Server needs a multi-dimensional mode SSAS instance.
Solution
You have at least two solutions on the table to solve this issue.
Solution 1: Uninstall and re-install SQL Server Analysis Services (SSAS)
The trick here is to be able to know how to uninstall SSAS and to do that you need to:
Open Command Prompt as Administrator model on the SQL Server and execute the following command
Action performed. In here we are going to uninstall
Feature we are going to remove. AS indicates Analysis Services instance
The name of our AS instance
After that, you just need to add new features to the existing installation, in this case, a multi-dimensional mode SSAS instance.
Solution 2: Modify the msmdsrv.ini file
An alternative way is to modify the msmdsrv.ini file. Administrators can modify default server configuration properties of an SQL Server Analysis Services (SSAS) instance by using SQL Server Management Studio. However, properties pages in SQL Server Management Studio show a subset of the properties most likely to be modified. For SSAS, all properties are in the msmdsrv.ini file.
The default installation, msmdsrv.ini can be found in the C:Program FilesMicrosoft SQL ServerMSAS15.MSSQLSERVEROLAPConfig folder
Of course, this depend on the version you are using.
On the msmdsrv.ini file:
Configure the DeploymentMode property to be 0 (zero) and save the file
After that, restart the SSAS instance and these will change from a tabular instance to a multi-dimensional instance
I recently received a request for help from a customer about a problem that has started to occur with SQL Server jobs in BizTalk Server. Everything was working fine for the last few months until they begin receiving the following errors while the SQL Jobs was trying to execute:
The job failed. Unable to determine if the owner (domainusername) of job MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb has server access (reason: Could not obtain information about Windows NT group/user ‘domainusername’, error code 0x534. [SQLSTATE 42000] (Error 15404)).
Cause
Actually, the reason for this error was quite simple, and it is an error that can happen relatively often if we do not take the necessary steps.
In this case, the user that performed the BizTalk Server installation and configuration was a personal account that was a member of the BizTalk Server Administration group, and by default, he is configured as the owner of that jobs. However, at some point, the employee left or terminated the contract and his account was terminated or disabled and that is the reason for that error started to happen.
Solution
The solution is simple, we need to change the owner. And to do that, we need:
Open the SQL Server Management Studio.
Expand SQL Server Agent, and then Jobs.
Right-click on job name, in this case, MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb and select Properties
In the Owner field, select the sa account, or any other account, as the job owner using the ellipsis button
Do that steps for all the BizTalk Server SQL Jobs.
I’m not a Dynamics 265 expert, and Dynamics 365 is not my focus area. Nevertheless, I couldn’t ignore the flood of requests to add the new Dynamics 365 logos, especially the App icons. It took a while, but they are finally here.
What’s new in this version?
These are the list of changes and additions present in this major release:
New shapes: There are new shapes on the following Visio Stencils files (.vssx):
MIS Office, Office 365 and Dynamics 365: add the new Dynamic 265 logo, Dynamics 365 App Icons, and Dynamics 365 Mixed Reality Icons.
MIS Azure Stencils and MIS Azure Additional or Support Stencils: there were a few new icons add to the stencils, most of them related to new preview features and integration services like the new Logic App icon.
SVG files: new SVG files added.
Microsoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack
Microsoft Integration, Azure, Power Platform, Office 365 and much more Stencils Pack it’s a Visio package that contains fully resizable Visio shapes (symbols/icons) that will help you to visually represent On-premise, Cloud or Hybrid Integration and Enterprise architectures scenarios (BizTalk Server, API Management, Logic Apps, Service Bus, Event Hub…), solutions diagrams and features or systems that use Microsoft Azure and related cloud and on-premises technologies in Visio 2016/2013:
BizTalk Server
Microsoft Azure
Integration
Integration Service Environments (ISE)
Logic Apps and Azure App Service in general (API Apps, Web Apps, and Mobile Apps)
Azure API Management
Messaging: Event Hubs, Event Grid, Service Bus, …
Azure IoT and Docker
AI, Machine Learning, Stream Analytics, Data Factory, Data Pipelines
SQL Server, DocumentDB, CosmosDB, MySQL, …
and so on
Microsoft Power Platform
Microsoft Flow
PowerApps
Power BI
Office365, SharePoint,…
DevOps and PowerShell
Security and Governance
And much more…
… and now non-related Microsoft technologies like:
SAP Stencils
The Microsoft Integration Stencils Pack is composed of 27 files:
Microsoft Integration Stencils
MIS Additional or Support Stencils
MIS AI and Machine Learning Stencils
MIS Apps and Systems Logo Stencils
MIS Azure Additional or Support Stencils
MIS Azure Black and Gray
MIS Azure Old Versions
MIS Azure Stencils
MIS Black and Cyan
MIS Buildings Stencils
MIS Databases and Analytics Stencils
MIS Deprecated Stencils
MIS Developer Stencils
MIS Devices Stencils
MIS Files and Message Types Stencils
MIS Generic Stencils
MIS Infrastructure and Networking Stencils
MIS Integration Fun
MIS Integration Patterns Stencils
MIS IoT Stencils
MIS Office, Office 365 and Dynamics 365
MIS Power BI Stencils
MIS Power Platform Stencils
MIS SAP Stencils
MIS Security and Governance
MIS Servers (Hexagonal) Stencils
MIS Users and Roles Stencils
Organisational Stencils
That you can use and resize without losing quality, in particular, the new shapes.
Download
You can download Microsoft Integration, Azure, BAPI, Office 365 and much more Stencils Pack for Visio from GitHub Here:
