Are you planning to migrate your SSIS Catalog to a newer version of SQL Server? You might like to know if your SSIS Catalog has any issues.
Some of the most common issues in the SSIS catalog could be:
- SSIS projects have orphan environment references.
- Project or package parameter refers to environment variables that do not exist in their reference environments.
- Unused environments or environment variables etc.
Fixing such issues before giving your SSIS Catalog a fresh start might be a good idea.
The SSIS Catalog Migration Wizard offers a feature to detect such issues.
Background
The SSIS Catalog Migration Wizard is an extension for Visual Studio 2017 or 2019 and SQL Server Management Studio 2018. is published on Visual Studio Marketplace.
This article describes inspecting the SSIS Catalog using SSIS Catalog Migration Wizard.
Demo
Launch the wizard from your preferred location and select the source SQL Server. Click Next.
Select Target SQL Server to which you want to migrate your catalog to. If you want only to inspect the source server catalog, you can select the same SQL server in the target. Click Next.
Click the Inspect Source button on this screen to inspect the source catalog for issues.
Note:
1. Inspect Source catalog is an important utility that preempts the possible errors and warnings during the SSIS Catalog migration to a new SQL Server.
2. You should review the recommendations before taking action on your SSIS Catalog items.
How to Resolve Inspection Errors and Warnings?
In SSIS Catalog Migration Wizard Inspect Source tool, you get to know the errors and warnings in SSIS Catalog. In this section, we will see how to fix some of those common errors and warnings.
Error 1: Orphan Environment reference to SSIS Project.
Project ‘ProjectName‘ is configured to environment reference‘EnvironmentFolderName\EnvironmentreferenceName’ for environment variable values. But, no environment named ”EnvironmentreferenceName’ that exists in the catalog folder EnvironmentFolderName‘.
Description:
This error indicates an orphan environment reference configuration present in SSIS Catalog. ‘EnvironmentFolderName\EnvironmentreferenceName’ is a nonusable environment reference causing an orphan environment reference error.
Solution:
- Open SSMS and connect to SQL Server Integration Services Catalog concerning the above error.
- i. In case the orphan reference ‘EnvironmentFolderName\EnvironmentreferenceName’ is not valid.
Navigate to the project ‘ProjectName’ where the orphan environment reference’EnvironmentFolderName\EnvironmentreferenceName’ is present. Right Click on project ProjectName and click on Configure. In the Configure dialogue, remove the reference ‘EnvironmentFolderName\EnvironmentreferenceName’.
ii. In case the orphan reference ‘EnvironmentFolderName\EnvironmentreferenceName’ is a Valid reference.
Navigate to the reference SSIS folder ‘EnvironmentFolderName’ in the SSIS Catalog. In the Environments Folder, Right Click and add click ‘Create Environment’ and specify the Environment name as ‘EnvironmentreferenceName’. Double-click the environment you just created and all variables in the Variables tab used in the referring SSIS project(s), .and Click OK to Finish.
Error 2: Orphan Environmen Variable reference in Project or Package Parameter.
Parameter ParameterName is configured to receive a value from an environment variable named ReferencedVariableName. But, there is no variable named ReferencedVariableName in any of its environment references.
Description:
This error indicates an orphan environment variable reference ReferencedVariableName present in the Project or Package parameter value of ProjectName in SSIS Catalog. ReferencedVariableName is nonusable and could cause the concerning package to fail during execution.
Solution:
- Open SSMS and connect to SQL Server Integration Services Catalog concerning the above error.
- Navigate to the project ‘ProjectName’ where the orphan environment reference variable ReferencedVariableName is present. Right-click on the SSIS project and click on Configure.
- Open all Environments from their respective SSIS folders as specified on Configure window in the above step. Add the missing ReferencedVariableName with its value in all those environments.
Warning 1:: Unused Environment present in SSIS Catalog Folder.
Environment Folder\Environment is not being referenced by any project. This can be deleted.
Description:
This warning indicates a potential unused Environment that can be removed from SSISDB.
Solution:
- Open SSMS and connect to SQL Server Integration Services Catalog concerning the above warning.
- Please navigate the SSIS Catalog folder containing unused Environment, right-click on it, and Delete it. Before deleting the environment.
Warning 2: Unused Environment Variable(s) present in SSIS Catalog Environment(s).
Environment variable(s) Var1, Var2 is(are) not being used in any of the project references. These can be removed from the Environment Folder\Environment.
Description:
This warning indicates potential unused Environment variable(s) that can be removed from SSISDB Environment Folder\Environment.
Solution:
- Open SSMS and connect to SQL Server Integration Services Catalog concerning the above warning.
- Navigate to the SSIS Catalog environment Folder\Environment containing unused Environment variables var1, var2 and double-click on it
- Delete all listed variables var1,var2 from the environment and Click OK. Before deleting the environment variables.
How About Keeping an Eye on it?
Well, this may not sound like a very interesting thing to do. But, it might make sense to monitor basic issues in your SSIS Catalog, say every week or after every deployment. With the command-line utility, you can programmatically inspect the SSIS Catalog. Perhaps, you can schedule this command using any scheduler like SQL Server Agent Job. The sample command looks like the one below.
"D:\SSIS Cataloger.Pro\SSIS.Cataloger.Pro.exe" /st:0 /ssn:LAPTOP-R9A0KU50 /opr:inspect
You can read more about the command-line parameters here.
See more