Join me For Expert SSIS Training!

I’m honored to announce Expert SSIS – a course from Enterprise Data & Analytics!

The next delivery is 01-02 Apr 2019, 9:00 AM – 4:30 PM ET.

If you sign up by 31 Jan, you save money. Right now the course is on sale!

Data integration is the foundation of data science, business intelligence, and enterprise data warehousing. This instructor-led training class is specifically designed for SQL Server Integration Services (SSIS) professionals responsible for developing, deploying, and managing data integration at enterprise-scale.

You will learn to improve data integration with SSIS by:

  • Building faster data integration.
  • Making data integration execution more manageable.
  • Building data integration faster.

Agenda

  1. SSIS Design Patterns for Performance – how to build SSIS packages that execute and load data faster by tuning SSIS data flows and implementing performance patterns.
  2. SSIS Deployment, Configuration, Execution, and Monitoring – the “Ops” part of DevOps with SSIS using out-of-the-box tools and the latest utilities.
  3. Automation – how to use Business Intelligence Markup Language (Biml) to improve SSIS quality and reduce development time.

Did someone mention a sale?

Yep! The course is currently on sale until 31 Jan 2019!

I hope to see you there!

PS – Want to Learn More About Azure Data Factory?

Delete SSIS Catalog Folders With SSIS Catalog Compare

To delete a Catalog folder using SSIS Catalog Compare, right-click the folder and click Delete Folder:

You are prompted to confirm folder deletion:

If the folder contains additional Catalog artifacts, the user is prompted again to confirm the deletion of the folder and all contents:

Let’s take a short break right here and consider some important facts about SSIS Catalog References and Environments.

Some Important Facts About SSIS Catalog References and Environments

  1. SSIS Catalog Environments may exist in any SSIS Catalog Folder.
  2. SSIS Catalog References define a relationship between an SSIS Catalog Environment and an SSIS Project or Package.
  3. The SSIS Catalog Environment is not required to reside in the same SSIS Catalog Folder as the SSIS Project or Package.
  4. The SSIS Catalog permits the deletion of Catalog Environments that participate in references.

Deleting the SSIS Catalog Folder Using SSMS

You cannot delete the SSIS Catalog Folder using SQL Server Management Studio – SSMS – unless the the folder is empty. Attempting to drop a non-empty SSIS Catalog Folder produces an error:

(click to enlarge)

However, SSMS allows the user to delete a Catalog Environment that is referenced with no warning or notification to the user:

Simply right-click the Environment and click Delete:

The Delete Object window, containing the SSIS Catalog Environment, displays:

Click OK and the Catalog Environment is deleted:

No warning, no message that env1 Catalog Environment is referenced by either a project in its own folder, Demo:

… or in a project in another folder (the ParamtersTest SSIS Project in the Test folder, in this case):

I can hear you thinking, “Why is this a problem, Andy?” I’m glad you asked – that’s an excellent question. Let’s try and execute an SSIS Package from the Test folder – from the SSIS Project named ParametersTest whose Configuration shown in the second screenshot above:

(click to enlarge)

Please note that I have checked the Environment checkbox and selected the referenced Catalog Environment. Why doesn’t this clear the error?

Because the Demo\env1 Catalog Environment no longer exists!

The error reads:

Parameter “NonSensitiveProjectParameter” is configured to receive a value from an environment variable named “Int32 Parameter”, but there is no environment variable named “Int32 Parameter” in environment “Demo\env1”. Select a different environment, or use a literal value for the parameter.

The error message is misleading – there is no Demo\env1 Catalog Environment. I just deleted it.

This is a broken reference.
And this is just how easy it is to accidentally create a broken reference when using SSMS to manage the SSIS Catalog.

But You Can Fix This, Right?

Well, yes and no. Mostly no.

“Can’t you connect to a different SSIS Catalog – one that contains another version of env1 – and then script env1 by pressing the Script button?”

Please watch this animated gif of me doing just that:

(click to enlarge)

Clicking the Script button after an Environment has been created and configured results in an empty SSMS script window.

Sad trombone…

You have options, but at this point something has gone very, very wrong with your plans for the day.

How SSIS Catalog Compare Helps

SSIS Catalog Compare informs the user if they are about to delete an SSIS Catalog Environment that participates in a reference.

If the folder contents include SSIS Catalog Environments that participate in references, the user must confirm the deletion of these Catalog Environments:

The message reads:

The environment Demo/env1 references the following folder(s)/project(s) in vDemo\QA:
Demo/LiftAndShift
Test/ParametersTest

The environment Demo/env2 references the following folder(s)/project(s) in vDemo\QA:
Demo/LiftAndShift

Are you sure you want to delete the environment(s)
Demo/env1
Demo/env2?

Note a message is created for each SSIS Catalog Environment that is about to be deleted, and each SSIS Project which consumes these Environments is also listed. Some Projects exists in other SSIS Catalog Folders, which is by design.

If the Reference is Broken…

SSIS Catalog Compare identifies references for which the Catalog Environment does not exist as Broken References. An example of how SSIS Catalog Compare surfaces a broken reference is shown here:

But Wait There’s More!

You can use SSIS Catalog Compare to connect to a different SSIS Catalog instance and script the SSIS Catalog Environment, as shown in this animated gif:

(click to enlarge)

Conclusion

You can still delete SSIS Catalog Environments using SSIS Catalog Compare, but not without being warned (if they participate in References). Why? The SSIS Catalog supports this functionality; so does SSIS Catalog Compare. But if a user accidentally deletes an SSIS Catalog Environment that is needed, SSIS Catalog Compare facilitates scripting SSIS Catalog artifacts, including SSIS Catalog Environments.

As you may glean from this analysis of one potential scenario using SSIS Catalog Environments management, managing SSIS Catalog artifacts in SSIS Catalog Compare can save your bacon.

Learn more about SSIS Catalog Compare!