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 SSIS catalog could be:

• SSIS projects have orphan environment references.
• Project or package parameter refers to environment variables that does not exist in its reference environments.
• Unused environments or environment variables etc.

Fixing such issues before giving your SSIS Catalog a fresh start might be a good idea, isn’t it?

The SSIS Catalog Migration Wizard offers a feature to detect such issues. This feature is in preview.

Background

The SSIS Catalog Migration Wizard is an extension for Visual Studio 2017 or 2019 and SQL Server Management Studio 2018. It is published on Visual Studio Marketplace. In my previous article, I have described how we can migrate SSIS Catalog artifacts from one server to another using SSIS Catalog Migration Wizard. In this article, we will see how to inspect the SSIS Catalog using SSIS Catalog Migration Wizard.

Demo

Launch the wizard from your preferred location and select source SQL Server. Click Next.

Select Target SQL Server where you want to migrate your catalog to. In case you want to only inspect the source server catalog, you can select the same SQL server in the target. Click Next.

On this screen, click on the Inspect Source button to inspect the source catalog for issues.

As this feature is in preview, we recommend you to review the recommendations before taking actions on your SSIS Catalog items.

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 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.