You Got Your Integration Services in My Database EngineWhen I first looked at Object Explorer in SSMS (SQL Server Management Studio) after installing SQL Server Denali CTP1, something looked out of place. I compared Object Explorer in Denali’s SSMS to the same in SQL Server 2008 R2, and it was immediately noticeable. There is a new node called Integration Services between the Management node and the SQL Server Agent node. Click on the images to enlarge them and get a better look.
So what is this node? Did they move the stored packages node out of SSIS/msdb to a new node in the database engine? Did they integrate (no pun intended) Integration Services into the Database Engine? Inquiring minds want to know.
My first thought was, “Hey, you got your Integration Services in My Database Engine,” which made me think of the old Reese’s Peanut Butter Cups commercials from the 1970s and early 1980s. Watch the video below if you aren’t familiar with the ads or if you just want to experience a bit of nostalgia.
I wanted to know I was looking for, so I hit F1 to pull up the F1 help for Object Explorer. On that page was a link for the Integration Services node. That gave me a list of several different things to explore. I want to take a moment to look at just the Integration Services Catalog.
Integration Services Catalog
I wasn’t finding any informaiton in Books Online for Denali on what an IS Catalog is. It directed me to a search of the TechNet Wiki. I was able to find a wealth of information on the IS Catalog. In short, it is a centralized point for storing and managing Integration Services packages, projects, and other objects. for a great primer on the IS Catalog take a look at the following Technet Wiki article: SQL Server “Denali” CTP1 – Integration Services (SSIS) Catalog Identifiers.
Creating an Integration Services Catalog
If you right-click on the Integration Services node, you only have a single action option: Create Catalog. If you click on it, it opens up a dialog box to create a catalog called SSISDB. There is a single configurable option. You must enter a password. If you try to click OK without entering a password, it gives you the error, “The password field cannot be empty. Input the password and try again.” Surprisingly, the OK button is not disabled if you do not enter a password. Of course, this is simply a CTP and the feature may not be complete. I clicked on Help to see more information, and it opened the page not found page.
Even though there was no help page found for the dialog, it showed a greate definition of how the password is used. The password will be the password used to create the database master key in the new database:
Integration Services Database
Integration Services stores application data in a database. Integration Services uses SQL Server encryption mechanisms to encrypt sensitive data that are stored in the Integration Services database. This requires that a Database Master Key to be created. You can manage this key by creating a backup after you have created the database. If you migrate or move the Integration Services database to another computer, you can restore the key to regain access to encrypted content.
Use this page to create an Integration Services Database, and specify the password for the Database Master key.
I entered a password and wanted to see how to create the catalog using T-SQL, so instead of clicking OK, I chose the Script option. I scripted it to a new query window and all it output was a GO statement and several blank lines into a query window plus another query window that was completely blank. I tried scripting it to the clipboard, an agent job, and to a file, and got similar results; two windows or files or jobs with 1 blank and 1 with just a GO statement. So its definitely not complete at this point, at least the dialog isn’t. I created a Connect item for this issue.
Since I couldn’t script it and there’s nothing more to customize, I went ahead and clicked OK. Cue the slide trombone. No good clicking the OK button either. Instead of creating the catalog, it returned another error.
Integration Services uses CLR stored procedures. Enable “clr enabled” configuration option.
Really? I have to enable CLR to be able to create an IS catalog? Okay, this is just my laptop. No real reason not to enable CLR so I enabled it:
Exec sp_configure 'clr enabled', 1; Reconfigure;
I thought that maybe the scripting options would work now. I closed the create catalog dialog, refreshed Object Explorer, and reopened the dialog. Attempting to script the catalog output the same results as before. I clicked OK and successfully created the IS catalog. I noticed several changes in Object Explorer.
- User database named SSISDB was created under the databases node
- IS database named SSISDB appeared under the Integration Services node
- SQL job named SSIS Server Maintenance Job under the SQL Server AgentJobs node
- Login named ##MS_SSISServerCleanupJobLogin## that is disabled and uses a random password
- Login named ##MS_SQLEnableSystemAssemblyLoadingUser## from Asymmetric Key MS_SQLEnableSystemAssemblyLoadingKey
Deleting an Integration Services Catalog
There is also an Asymmetric Key named MS_SQLEnableSystemAssemblyLoadingKey, but I was not able to determine if it had been created by the process that creates the catalog or if it had already existed. When I delete the SSISDB database under the Integration Services node, it deletes all of the bulleted objects as well. The Asymmetric Key is not deleted, so it may have existed previously. Or it may simply not need to delete it.
I noticed that I can delete the SSISDB user database manually. When I delete the SSISDB user database, it deletes the SSISDB node under the Integration Services node as well. I then check the other objects and note that none of the other objects have been deleted. I would have expected it to delete the same objects if it was going to delete the SSISDB node as well. Leaving the objects behind did not affect the ability to create a new IS catalog, and deleting the SSISDB node after recreating it deletes all of the objects as expected.
I created a Connect item for this issue as well.
Exploring the Integration Services Catalog
At this point, I decided to spend a little more time playing around with the option in SSMS rather then investigating further. I right-clicked on the SSISDB node and clicked on Properties. A dialog opened showing several general properties. Some of the properties are read-only and some are configurable. The Script options and the Help option both work correctly for this dialog
I changed most of the modifiable options to see what the Script output would be, and it outputs execution calls to a stored procedure named catalog.configure_catalog. The procedure accepts parameter name/value pairs, one at a time.
EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'MAX_PROJECT_VERSIONS', @property_value=11 EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'RETENTION_WINDOW', @property_value=366 EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'VALIDATION_TIMEOUT', @property_value=301 EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'OPERATION_CLEANUP_ENABLED', @property_value=N'FALSE'