v220.127.116.11 – 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, v18.104.22.168 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:
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
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://email@example.com&item_name=Supporting%20sp_ssiscatalog.
- Download the zip file at DB v22.214.171.124. It contains two files, SsisReportingPack.dacpac & SSISDB.dacpac
- Unzip to a folder of your choosing
- Open a command prompt and change to the directory into which you unzipped the files
- "%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:
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].