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
- Download the zip file at DB v1.0.1.0. 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
- 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:
or
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!
@Jamiet