Did you know documentation is built-in to usp_ssiscatalog?

Posted by jamiet on SQL Blog See other posts from SQL Blog or by jamiet
Published on Wed, 14 May 2014 21:18:29 GMT Indexed on 2014/05/26 21:55 UTC
Read the original article Hit count: 70

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:

SNAGHTMLa9937ec

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

© SQL Blog or respective owner

Related posts about ssis

Related posts about SSIS Catalog