I recently made this video to explain what I call “broken references” in the SSIS Catalog. In the video, I create and test a broken reference, and then demonstrate how SSIS Catalog Browser (and SSIS Catalog Compare) visualize broken references.

Enjoy!

What is a Broken Reference?

It may help to define reference first. An SSIS Catalog Reference is a relationship between an SSIS Project and an SSIS Catalog Environment in the SSIS Catalog. One or more references to environments may be configured for a project. At execution time one and only one reference may be selected – like Highlander, “There can be only one.”

A broken reference occurs when one configures a reference between a project and an environment, and then the environment is deleted. SSIS Catalog Browser (and SSIS Catalog Compare) visualize broken references:

SSIS Catalog Compare allows data integration lifecycle managers to delete SSIS Catalog Environments:

If the environment is referenced, however, SSIS Catalog Compare prompts the DILM manager with a dialog that lists the SSIS Projects that reference the environment:

Conclusion

It’s possible to create broken references using SSIS Catalog Compare. However, SSIS Catalog Compare:

1. Warns DILM managers they are about to create a broken reference; and
2. Requests confirmation before allowing DILM managers to create a broken reference.

Need Help with Your Enterprise ETL or Data Warehouse?

At Enterprise Data & Analytics, we believe you should ask four questions when hiring an enterprise data consultancy.

Enterprise Data & Analytics offers comprehensive ETL, SSIS, Data Warehouse, and Azure Data Factory knowledge, experience, and success to your enterprise. We engage in three phases:

1. Training – Enterprise Data & Analytics offers enterprise-scale, experience-based training; sharing knowledge from experience with enterprise teams of all sizes.
2. Partnering – In addition to delivering successful, start-to-finish, enterprise data warehouse solutions, Enterprise Data & Analytics team members join your enterprise team members at the developer, project manager, and developer manager levels to shepherd (freshly-trained) team members as we – together – apply data integration patterns to your enterprise data warehouse solution.
3. Sustaining – After the code is implemented, tested, documented, and signed-off; Enterprise Data & Analytics team members stand ready to support your enterprise data warehouse and EDW team in the event of a disaster, re-join your team to jumpstart the next version, or merely to answer a question.

Contact us today!