THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Did you know documentation is built-in to usp_ssiscatalog?

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

Published Wednesday, May 14, 2014 11:18 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement