The SSIS Catalog is surfaced within the SSISDB database. In this post I describe some tips for managing your SSIS Catalog database, named SSISDB. For information regarding restoring SSISDB, please see Deploying SSIS Projects to a Restored SSIS Catalog (SSISDB).
Create the SSIS Catalog
To create the SSIS Catalog, connect to an instance of SQL Server using SQL Server Management Studio (SSMS). Open the Object Explorer and right-click the “Integration Services Catalogs” node:
The Create Catalog dialog displays:
The SSIS Catalog requires SQLCLR. Check the “Enable CLR Integration” checkbox to proceed:
You can optionally select the “Enable automatic execution of the Integration Services stored procedure at SQL Server startup” option by checking its checkbox (I do):
The SSIS Catalog requires a password. If you restore the SSISDB database (which wholly contains the SSIS Catalog), you will most likely need this password. So store it somewhere very safe.
Click the OK button to create the SSIS Catalog.
Note: I’ve created a short (< 2:00) video that walks through this process. You can view it here.
Back It Up
As with all SQL Server database, please back up SSISDB. What follows is a (very) basic guide describing one simple method to backup your SSISDB database. Please, please, please learn more about SQL Server backup and restore options and their implications before backing up an SSISDB database in your enterprise. Feel free to use the steps I describe on your laptop or a virtual machine. And please remember…
Backups are useless. Restores are priceless. Conduct practice Disaster Recovery exercises in which you restore databases and then test functionality. You’ll be glad you did. Here is a link containing Microsoft’s advice on restoring the SSISDB database in SQL Server 2016.
In SQL Server Management Studio (SSMS), right-click the SSISDB database, hover over Tasks, and click Back Up:
When the “Back Up Database – SSISDB” dialog displays, set appropriate backup options. (In this screenshot, I am setting no backup options. You can break stuff here. Unintentionally. Easily. In Production. Again, read more about backing up databases – especially about backing up SSISDB).
I’m going to walk you through a very simple example of an SSISDB backup on one of my demonstration virtual machines:
I remove the default Destination by clicking the Remove button.
I click the Destination Add button and select a location and filename for my SSISDB backup:
After clicking OK, my very simple (please read waaaaay more than this post before attempting this at work or home!!) back up is configured:
When I click OK, my backup completes successfully:
Deleting the SSIS Catalog
If you want to delete the SSIS Catalog, drop the SSISDB database (Wait! Back it up first!):
The Delete Object dialog displays. Click the OK button to attempt to drop the SSISDB database (and, thereby, the SSIS Catalog):
Starting Over with a Fresh SSIS Catalog Installation
Perhaps you’re trying to build a presentation or demonstration about creating an SSIS Catalog. Maybe you just want a fresh start. Whatever the reason, you may find it annoying that once you’ve created and deleted an SSIS Catalog you are stuck with some of the settings:
Clearing the SQLCLR Option
Clearing the SQLCLR option (“Enable CLR Integration”) is accomplished by executing the following Transact-SQL (T-SQL) script:
sp_configure ‘clr enabled’, 0;
GO
RECONFIGURE;
These statements, when executed in SSMS, appear as shown here:
This solves some of the problem, but not all. The Create Catalog dialog still has that “Enable automatic execution of the Integration Services stored procedure at SQL Server startup” checkbox checked. Worse, now the checkbox is disabled!
Fear not. This setting can be reset using a T-SQL script:
EXEC sp_procoption
@ProcName = ‘sp_ssis_startup’,
@OptionName = ‘startup’,
@OptionValue = 0;
When executed, the resulting messages appear as shown:
After you reset those settings you may demonstrate creating an SSIS Catalog in a pristine instance, as shown here:
If you want, you may also delete the “SQL Server Maintenance Job” which is created when the SSIS Catalog is created:
This post provides some basic guidance on how to manage the SSIS Catalog in an instance of SQL Server. Please read more at Backup, Restore, and Move the SSIS Catalog MSDN article.
—
You might want to contact Enterprise Data & Analytics because we get the SSIS Catalog.
—
:{>
Related Training:
SSIS Academy: Using the SSIS Catalog – 3 days, you, me, and the SSIS Catalog…
IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago
IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
Learn More:
Deploying SSIS Projects to a Restored SSIS Catalog (SSISDB)
A New Version of SSDT is Available
Announcing SSIS Catalog Compare v1.0
Stop an SSIS Package
Stairway to Integration Services
Stairway to Biml
SQL Server Central
Need help implementing an SSIS solution?
Contact Enterprise Data & Analytics today!
Thank you for this article! I was looking on how to undo the SSIS catalog. Is it okay to delete the ##MS_SQLEnableSystemAssemblyLoadingUser and ##MS_SSISServerCleanupJobLogin## logins after the database has been dropped and CLR disabled?
Hi Tammy,
Thank you for reading my blog and for this feedback.
As far as I know, it is safe to delete those logins. I am unaware of their use by any other SQL Server databases. HOWEVER, I recommend disabling the logins for some time – 90-180 days – before deleting them, just to be on the safe side.
Hope this helps,
Andy
Thank you for this article, Andy!
I followed your instructions but after I hit ‘OK’ button to create SSIS catalog I got an error message saying
“Execution Error!
Cannot drop master key because certificate ‘MS_Cert_Proj_1 is encrypted by it. Changed database context to ‘SSISDB’. (Microsoft SQL Server, Error: 15580)”
and the SSIS catalog/database have not been created.
So I ran the following
DROP CERTIFICATE MS_Cert_Proj_1
but got another error message:
Cannot drop the certificate ‘MS_Cert_Proj_1’, because it does not exist or you do not have permission.
I am logged in as ‘sa’ using SQL Server authentication mode and I got empty result sets after running the following 3 statements:
SELECT * FROM sys.certificates
where name like ‘%proj%’
SELECT * FROM sys.certificates
select
database_name = d.name,
dek.encryptor_type,
cert_name = c.name
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;
Any suggestions?
Thank you!
Actually, your article says in order to delete SSIS catalog SSIS database must be dropped, but I did it the other way around: I deleted SSIS catalog and, as a result SSIS database was dropped.
As with Walt, the order needs to be:
– Delete any folders in Integration Services Catalog
– Delete SSIS Catalog
– This will delete SSISDB database
Please can you detail this clearly in your blog post? If you delete the SSISDB database first, you will be left with an orphaned SSIS catalog!
Hi Walt and Doodles,
Thanks for your feedback.
Walt, I investigated dropping the SSISDB Catalog vs. dropping the SSISDB database and learned dropping the Catalog does a better job cleaning up Catalog artifacts (logins, etc.) than dropping the database.
Doodles, I was able to drop the Catalog without dropping Catalog folders first. I could be missing a step. Can you describe why you believe the folders must be deleted first?
I do not know what you mean by “you will be left with an orphaned SSIS catalog!” Can you clarify what you mean? I still see an SSISDB Catalog in the SSMS Object Explorer’s Integration Services Catalogs node, but it’s a ghost that disappears when I refresh this node (after dropping the SSISDB database).
You are both correct: Dropping the SSISDB Catalog will drop the SSISDB database. Since dropping the Catalog does a better job cleaning up after itself, I’ll write an updated post recommending dropping the Catalog instead.
Thank you!
:{>
You are right Andy – I just had to refresh Integration Services catalog and it disappears. I didn’t realise this. I tried to right-click it and Delete but it gave me weird errors related to the SSISDB database missing, so i freaked out. But all i needed to do was refresh
However as you say, in terms of logins etc, it’s neatest to delete the catalog.
Thanks for the blog!
Hi Doodles,
Thanks for pointing out what you saw and experienced and for the follow-up. And thanks for reading my blog!
:{>
Nice going you are the SSIS man, Andy. Don’t forget some of the scripts above need to be run from the master database.