I am still working apace on updates to my open source project SSISReportingPack, specifically I am working on improvements to usp_ssiscatalog which is a stored procedure that eases the querying and exploration of the data in the SSIS Catalog.

In this blog post I want to share a titbit of information about usp_ssiscatalog, that all the actions that you can take when you execute usp_ssiscatalog are documented within the stored procedure itself. For example if you simply execute

EXEC usp_ssiscatalog @action='exec'

in SSMS then switch over to the messages tab you will see some information about the action:

OK, that’s kinda cool. But what if you only want to see the documentation and don’t actually want any action to take place. Well you can do that too using the @show_docs_only parameter like so:

EXEC dbo.usp_ssiscatalog @a='exec',@show_docs_only=1;

That will only show the documentation. Wanna read all of the documentation? That’s simply:

EXEC dbo.usp_ssiscatalog @a='exec',@show_docs_only=1;
EXEC dbo.usp_ssiscatalog @a='execs',@show_docs_only=1;
EXEC dbo.usp_ssiscatalog @a='configure',@show_docs_only=1;
EXEC dbo.usp_ssiscatalog @a='exec_created',@show_docs_only=1;
EXEC dbo.usp_ssiscatalog @a='exec_running',@show_docs_only=1;
EXEC dbo.usp_ssiscatalog @a='exec_canceled',@show_docs_only=1;
EXEC dbo.usp_ssiscatalog @a='exec_failed',@show_docs_only=1;
EXEC dbo.usp_ssiscatalog @a='exec_pending',@show_docs_only=1;
EXEC dbo.usp_ssiscatalog @a='exec_ended_unexpectedly',@show_docs_only=1;
EXEC dbo.usp_ssiscatalog @a='exec_succeeded',@show_docs_only=1;
EXEC dbo.usp_ssiscatalog @a='exec_stopping',@show_docs_only=1;
EXEC dbo.usp_ssiscatalog @a='exec_completed',@show_docs_only=1;

I hope that comes in useful for you sometime. Have fun exploring the documentation on usp_ssiscatalog. If you think the documentation can be improved please do let me know.

@jamiet