THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

sp_ssiscatalog v1.0.1.0 now available for download

13 days ago I wrote a blog post entitled Introducing sp_ssiscatalog (v1.0.0.0) in which I first made mention of sp_ssiscatalog, an open source stored procedure intended to make it easy to query the SSIS Catalog. I have been working on some enhancements since then and hence v1.0.1.0 is now available for download from Codeplex.

What’s new in this release

This release includes the following enhancements:

  • [execution_id] now gets returned in a call to

    EXEC [dbo].[sp_ssiscatalog] @operation_type='exec';

  • Filter events by specifying packages to ignore
    EXEC [dbo].[sp_ssiscatalog] @operation_type='exec',@exec_events_packagesexcluded='SomePackage.dtsx,AnotherPackage.dtsx';

  • [event_message_id] is now returned in a list of events

  • List of executions can now be filtered via a minimum and maximum execution_id

    EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_minimum_execution_id=198,@execs_maximum_execution_id=201

  • Events resultsets now have a field, [event_message_context_xml] that contains an XML document containing all [event_message_context] info (if any exists)

Installation instructions

  1. Download the zip file at DB v1.0.1.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.)

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




depending on whether the target database already exists or not

This will create a database called [SsisReportingPack] which contains [dbo].[sp_ssiscatalog]

Feedback is welcomed!


Published Tuesday, November 20, 2012 10:42 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


No Comments

Leave a Comment


This Blog


Privacy Statement