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

sp_ssiscatalog v1.0.2.0 now available for download [SSIS]

v1.0.2.0 – what’s in it?

Things have been a bit quiet on the sp_ssiscatalog front since I last blogged about it three months ago in December 2012. Rest-assured development continues apace however and today I’m making available a minor update, v1.0.2.0 which is now available for download from Codeplex. For those that don’t know I describe sp_ssiscatalog as:

sp_ssiscatalog is a stored procedure that makes it easy to query for information that is strewn around the SSIS Catalog.

There aren’t too many functional changes in this release, it is more focused on making sp_ssiscatalog easier to use. Back in Documenting sp_ssiscatalog I explained how I was adding documentation to the messages tab of SSMS. Hence as of this new release when you execute sp_ssiscatalog you will see information such as this:

image

image

which I think should be very useful for anyone that wants to use sp_ssiscatalog to its fullest. Even I who wrote the thing and has been using it day-in, day-out for quite some time now can’t remember the names of all the parameters – now I no longer have to!

Note that you can turn off the display of the documentation using the @show_docs parameter:

exec sp_ssiscatalog @show_docs=0

If you want to display only the documentation and not actually have sp_ssiscatalog do any querying of the SSIS Catalog its @show_docs_only:

exec sp_ssiscatalog @show_docs_only=1

If you have any suggestions for future enhancements please put them in the comments below or submit them to the discussions page on the Codeplex site.

As a reminder, here is the sort of thing you can do with sp_ssiscatalog:

--Return all failed executions  
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_status_desc='failed'

--Return all executions for a specified folder
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_folder_name='My folder'  

--Return all executions of a specified package in a specified project
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_project_name='My project', @execs_package_name='Pkg.dtsx'

--Return information about the most recent execution
EXEC [dbo].[sp_ssiscatalog]

One last thing, if sp_ssiscatalog is useful to you and you’d like to support future development feel free to donate to my personal beer fund at http://www.paypal.com/cgi-bin/webscr?cmd=_xclick&business=jamie@jamie-thomson.net&item_name=Supporting%20sp_ssiscatalog.


Installation Instructions

  1. Download the zip file at DB v1.0.2.0. It contains two files, SsisReportingPack.dacpac & SSISDB.dacpac
  2. Unzip to a folder of your choosing
  3. Open a command prompt and change to the directory into which you unzipped the files
  4. Execute:
    • "%PROGRAMFILES(x86)%\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Publish /tdn:SsisReportingPack /sf:SSISReportingPack.dacpac /v:SSISDB=SSISDB /tsn:(local)
      (/tsn specifies the target server, change as appropriate. /tdn specifies the database name, you can call it whatever you like.)

If everything works OK you’ll see something like the following:

image

This will (if it doesn’t already exist) create a database called [SsisReportingPack] (or whatever you chose to call it) which contains [dbo].[sp_ssiscatalog].

image

Published Tuesday, March 12, 2013 12:51 AM 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

 

SSIS Junkie said:

I’ve just put out a new version of sp_ssiscatalog. The main change is requesting a list of executions

March 15, 2013 6:19 PM
 

Scott Whigham said:

Very cool, thanks for sharing. I'm checking it out now on a 2012 RTM instance right now but getting an error during deployment. It says "The following SqlCmd variables are not defined in the target scripts: SSISDB". Any suggestions on how to resolve that? The database is created but it only has "AllSchemaOwner" user, ssis_admin, unnamed, catalog, and internal - and that's it - no proc or tables. Any suggestions?

March 19, 2013 9:59 AM
 

jamiet said:

Hi Scott,

How are you deploying it? Are you perhaps using the deployment wizard in SSMS? Unfortunately that won't work because of a glaring limitation that I cover here: http://sqlblog.com/blogs/jamie_thomson/archive/2012/11/07/introducing-sp-ssiscatalog-v1-0-0-0.aspx

because of this limitation you *must* use the command-line deployment option.

regards

Jamie

March 21, 2013 11:57 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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