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
Following my last blog post and while I was trying to fix some issues regarding a BizTalk Server 2020 installation and configuration provided by my intern as is learning process. We end up solving some critical problems that allow us to install the platform’s most important components. Nevertheless, several components like BAM Tools, BAM Portal and BizTalk EDI/AS2 Runtime failed to configure:
Once, we inspect the logfile provide the the BizTalk Server Configurarion Wizard we found the following error message:
[2020-11-10 22:17:18:0674 Error Configuration Framework]Feature: [BAM Tools] Failed to configure with error message [Feature is skipped due to failed validation. Please go to Custom Configuration to fix the validation error.
Validation Error: <Exception Message=”Error validating the SSIS Catalog database. Please ensure SQL Server Integration Services is installed on the local machine and SSIS Catalog is created on target SQL Server.” Source=”BAMTools” HelpID=”idsErrorValidateSSISCatalogDatabase”><Exception Message=”SSIS Catalog (SSISDB) does not exist on server BTS2020LABAVMRG. Please create SSIS Catalog.” Source=”Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper” HelpID=”error_SSISCatalogNotExists”/></Exception>]
[2020-11-10 22:17:18:0674 Error Configuration Framework]Feature: [BAM Portal] Failed to configure with error message [The Configuration of Feature ‘BAM Portal’ failed because the dependent Feature ‘BAM Tools’ was not configured. ]
[2020-11-10 22:17:18:0674 Error Configuration Framework]Feature: [BizTalk EDI/AS2 Runtime] Failed to configure with error message [Feature is skipped due to dependent feature (BAM Tools) failed to configure correctly.]
Three distinct errors but the last two are a sequence of the first one, all of them are related to the same cause.
Cause
SSISDB was automatically created during the BizTalk Server configuration process on previous versions if we enable BAM Portal. However, BAM Portal was deprecated in this new BizTalk Server version, and the configuration process no longer created the SSIS Catalog. Instead, we need to create the catalog manually.
If you don’t create the SSIS Catalog manually, you will not be able to configure BAM Portal. Nevertheless, you can always perform this operation whenever you think is required, and after that, configure BAM Portal.
Make sure you also configure SQL Server Database Mail feature if you wish to configure BAM Alerts on your BizTalk Server 2020 environment
Solution
The solution is very simple, we need to manually create the catalog by using the following instructions:
Open SQL Server Management Studio and connect to the SQL Server Database Engine.
In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
On the Create Catalog window, do the following configuration and click OK.
Select Enable CLR Integration option.
Enter a password to protect the encryption key.
And run the BizTalk Server Configuration again. At the end you will be able to configure all BizTalk Server components.
Last blog post I wrote about an “Access is denied” error while trying to connect with SQL Server Integration Services (SSIS). Today’s post is about the same problem but in a different situation/context, of course with a different cause and solution, this time I got this error while trying to execute a SQL Server Job to run the BAM_AN_<name>View and BAM_DM_<name> to import BAM data to Analysis Server and maintaining the BAMPrimaryImport BAM tables:
Date 5/10/2019 12:41:03 PM
Log Job History (BAM <name> SQL Server Integration Services Packages)
Step ID 1
Server localhost
Job Name BAM <name> SQL Server Integration Services Packages
Step Name BAM <name> Cube Update Integration Services package
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: NT ServiceSQLAgent$BIZTALK. Microsoft (R) SQL Server Execute Package Utility Version 13.0.5264.1 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 12:41:04 PM Could not load package “MSDBBAM_AN_<name>View” because of error 0xC00160AE. Description: Connecting to the Integration Services service on the computer “localhost” 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. Source: Started: 12:41:04 PM Finished: 12:41:04 PM Elapsed: 0.016 seconds. The package could not be loaded. The step failed.
To better contextualize this issue, I got this error after:
I give permissions to my user to connect to SSIS (see how in my previous blog post)
I successfully created the SQL JOB to import and maintain BAM data, so I was able to navigate in SSIS to select the correct packages
Cause
Again, the description says that by default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges to connect to the Integration Services service. That, in other words, means:
insufficient rights to connect to SSIS.
And the reason behind that is that the tasks are by default running under (Run as) SQL Server Agent Service Account that is typically a different user that the user that is configuring/creating the importation Jobs. Usually, it will run under a service account or NT Service like: “NT SERVICEMSSQLSERVER” or in my case “NT ServiceSQLAgent$BIZTALK” and this may not have access to SSIS.
Solution
The solution to this issue is:
to give permission to the SQL Server Agent Service Account
or for better control, you should set up a Proxy Account to run SSIS packages.
To set up a Proxy Account to run SSIS packages you should:
Note: I will assume that there a Login for the user is already created/configured in SQL Server and that will also have access to BAMPrimaryImport database;
The first step is to create the credentials which will be then used in the Proxy Account. To do this, we need to:
In SQL Server Management Studio, click on Security and then right click on Credentials, click on New Credential…
On the New Credential window
Put a Credential name. You can put the same name as the domain name or a meaningful name. In my case, I add “BAM Import Account”
Click on Identity, which will open the Select User or Group window to ensure you select the correct user or Group;
And then you will need to put in the password for the Domain account you selected and confirm the password in another text box
Then click Ok to create your new Credential
The second step will be creating a proxy to be used within the SQL Server Agent. To do that you should:
In SSMS, click on SQL Server Agent, then Proxies and then SSIS Package Execution.
Right-click and select New Proxy…
On the New Proxy account window
Give your Proxy a meaningful name, in my case, “BAM Proxy”
Under Credential Name select the credential you should use to execute the packages, in my case, “BAM Import Account”
And activate the following subsystems from the list:
“SQL Server Integration Services Package”
Then click Ok to create your new Proxy.
The third and final step is to associate this proxy on your job execution. To do that you should:
In SSMS go to SQL Server Agent, right click on the BAM importation Jobs and select Properties
Select the steps tab and for edit all the steps that are executing the SSIS packages
On the “Run as” combo box, you will now be able to see the Proxy created earlier. Select that option. And click OK.
Now, and assuming that you configured adequately, if you manually run the job, or wait for the next scheduled execution, it will run successfully… we hope.
Let’s stay on the topic of my last blog post “BizTalk Server 2016 and SQL Server Integration Services (SSIS) 2016” and described another issue that I recently faced while trying to connect with SQL Server Integration Services (SSIS): “Access is denied“. The full error description was:
TITLE: Connect to Server
——————————
Cannot connect to localhost.
——————————
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
——————————
Connecting to the Integration Services service on the computer “localhost” 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.
For help, click: http://go.microsoft.com/fwlink/?LinkId=506689
——————————
Connecting to the Integration Services service on the computer “localhost” 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.
——————————
BUTTONS:
OK
——————————
Cause
Well, the description says that 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. However, I was a local administrator and a BizTalk Administrator
But still, the cause is clear: insufficient rights to connect to SSIS. When using SQL Server 2012 or later, when a user without enough rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an “Access is denied” error message. You can avoid this error message by ensuring that users have the required DCOM permissions.
Solution
So, to solve this issue, you should:
Open Component Services; from a Run dialog, you can enter “dcomcnfg“, with Administrator permissions.
On the left-hand tree, navigate to Component Services | Computers | My Computer | DCOM Config.
Find “Microsoft SQL Server Integration Services 13.0“, right-click and select “Properties“
On the Properties windows, select the “Security” tab and for each type of permission click “Edit” and add an appropriate AD group or user.
Select “Allow” to all options.
Once you have completed, you will be required to restart the SSIS service.
From the start menu, navigate to the “SQL Servers Configuration Manager“, right-click on “SQL Server Integration Services“, and “Restart“.
I’m back to writing! With so many talks in recent times and those who still come, and with it all the time necessary to prepare them; with so much work and new projects ongoing (thankfully); with 3 kids at home and recently married… it has been difficult to arrange a free time to concentrate on the writing. But I’m back, and for starting with a smooth topic that I like: “Errors and Warnings, Causes and Solutions” on a problem that actually I faced today while trying to connect with SQL Server Integration Services (SSIS): “The specified service does not exist as an installed service.”
Today, while I was trying to access SSIS from SQL Server 2016 Server, that host and support BizTalk Server 2016 I got the following and bizarre error:
TITLE: Connect to Server
——————————
Cannot connect to ..
——————————
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
——————————
Connecting to the Integration Services service on the computer “localhost” failed with the following error: “The specified service does not exist as an installed service.”.
This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.
Cause
This was bizarre because again I was trying to access to SSIS directly from SQL Server machine and I was sure that I had Integration Services installed and running on the server as I was able to confirm access to the services (services.msc).
I’m not a SQL Server specialist, but after careful research into the SSIS documentation it says:
“To connect directly to an instance of the legacy Integration Services Service, you have to use the version of SQL Server Management Studio (SSMS) aligned with the version of SQL Server on which the Integration Services Service is running. For example, to connect to the legacy Integration Services Service running on an instance of SQL Server 2016, you have to use the version of SSMS released for SQL Server 2016.”
That triggered some red lights on my head because:
I knew that this was a recent installation and we were using a current version of SQL Server Management Studio (v17.9.1);
and I also knew that for example during the BizTalk Server configuration we may face some issues configuring some features if we are using a recent version of SSMS, you should use a compatible and recommended version: SSMS 16.5.3.
Install SSMS 16.5.3 in another machine to access SSIS.
Or accessing from BizTalk Server 2016 machine that should have this SSMS version installed.
In my case, I was able to connect to SSIS without any problem from SSMS installed in BizTalk Server 2018 machine because I always installed from day one SSMS 16.5.3 on BizTalk Servers machines.
