Follow the steps below to install usp_ssiscatalog.
1. Download the latest installer from
. Running that installer will create a folder on your hard drive containing two .dacpac files:
SSISReportingPack.dacpac is the one that contains usp_ssiscatalog however SSISDB.dacpac represents all of the objects in the SSIS Catalog hence is a pre-requisite and has to be bundled along for the ride. Simply unzip these two files into a folder.
2. Ordinarily a dacpac can be deployed to a SQL Server from SSMS using the Deploy Dacpac wizard however in this case there is a limitation. Due to usp_ssiscatalog referring to objects in the SSIS Catalog (which it has to do of course) the dacpac contains a
SqlCmd variable to store the name of the database that underpins the SSIS Catalog; unfortunately the Deploy Dacpac wizard in SSMS has a rather gaping limitation in that it cannot deploy dacpacs containing SqlCmd variables. Hence, we can use the command-line
tool, sqlpackage.exe, instead. Don’t worry if reverting to the command-line sounds a little daunting, I assure you it is not. Simply open a command-prompt and cd to the folder containing the dacpacs:
- "%PROGRAMFILES(x86)%\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /action:Publish /TargetDatabaseName:SsisReportingPack /SourceFile:SSISReportingPack.dacpac /Variables:SSISDB=SSISDB /TargetServerName:(local)
or the shortened form:
- "%PROGRAMFILES(x86)%\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Publish /tdn:SsisReportingPack /sf:SSISReportingPack.dacpac /v:SSISDB=SSISDB /tsn:(local)
remembering to set your server name appropriately (here mine is set to “(local)” ).
If everything works successfully you will see something resembling:
(it may change in different versions)
And you’re done! You’ll have a new database called [SsisReportingPack] which contains usp_ssiscatalog: