The more I work with the SSIS catalog the more I appreciate that it’s design is an improvement over the SSIS in SQL 2008. Were going to avoid the heavy explanation and just walk through creating the SSIS catalog, folders and assigning permission for your developers. This post is more for the DB Administrator then the developers.
The first step is to create the catalog which also creates a database called “SSISDB”.
1. Create SSIS Catalog In SSMS
- Log into your SQL instance
- Right click on “Integration Services Catalogs”
- Choose “Create Catalog…”
- Check: Enable CLR Integration
- Check: Enable automatic execution of Integration Services…
- The Database name cannot be changed
- Assign a Password for the encryption key
A new Database will be created called “SSISDB” as well as a new Object under “Integration Services Catalogs” (ISC) called “SSISDB”. It should be backed up just like any of your other User databases.
Even though the name is plural (Catalogs), you can only have 1 SSIS catalog per SQL instance and you cannot rename the SSISDB database without messing up the catalog.
2. SSIS Catalog Settings
Here are a few settings I prefer to use for each SSIS Catalog installation. I credit the SSIS best practice page for some of these.
- Right click on the SSISDB Catalog and choose Properties
- Clean Logs Periodically: True
- Retention Period: 90 Days
- Server-wide Logging…: Performance
- Max Number of Versions…: 5
- Periodically Remove Old Versions: True
3. SSIS Catalog Folder Structure
The folders can be used to logically separate Developers, Groups & Departments or by Function. The trick is you can only have 1 folder layer, the sub-folders are predefined for the Projects and Environments.
- Right click on the SSISDB Catalog in ISC and choose “Create Folder”
- Assign a name and include a description for this folder, I usually add contact info for the Developers or their Department
At this point there will be a “Projects” sub-folder and an Environments sub-folder. If you right click on the Projects folder you can see there are options (db tales com) to deploy or import a project using SSMS. This will be gone over in another post as well as handling the Environments.
At this point we need to create Users and assign Permissions so they can deploy their projects and packages from Visual Studio.
4. Grant Permission to SSIS Catalog
At this point you can right click on the new Project Folder and choose properties. Then click Browse to add a user or developer.
You will notice that there are no logins or roles here to assign, only the default SQL and SSIS roles and accounts. This window is getting it’s logins/roles list from the SSISDB as opposed to the logins on the SQL instance.
- Leave the Folder Properties window open
- Inside the SSISDB database, create a User from an existing Login
- Do not assign any permission to the SSISDB database, just Public
- Go back to Folder Properties and click Browse again
- You can now choose the new User/Developer from the list
Note: When using Active Directory logins I have created DB Users without a login in the SSISDB and it works for assigning Folder permissions.
4a. Catalog and SSISDB Permissions
After the login has been added you will need to assign permissions for creating, modifying and executing projects and packages. If the environment is managed under a single group you might just assign full access to all developers. Otherwise you can Grant and Deny based on the Users role or the environment this SSIS Catalog is in.
My recommendation for a mixed shared environment:
- Grant Developers all permissions except for “Manage Permissions” and “Manage Object Permissions”
This allows the developer to deploy and manage from the project level down as well as manage their own environment settings. They can also execute the packages one at a time to test and view the report results.
It does not allow them to assign someone else to the folder so access can be controlled more efficiently.
5. Connect Visual Studio To SSIS Catalog
We have created the catalog, project folder and assign the developers permission to deploy. They will need to know what settings (db tales com) to use in their Visual Studio Project to deploy to the right server and folder.
These settings are found in the Project Properties:
Configuration Properties -> Deployment
- Server Name: nameofserver\sqlinstance
- Server Project Path: /SSISDB/AssignedFolder
Configuration Properties -> General
- TargetServerVersion: SQL Server xxxx
7. Package Protection Level In SSIS
The Package ProtectionLevel should be saved and deployed using the “DontSaveSensitive” option and Parameters can be used to provide passwords or other variables.
This keeps sensitive values out of the package and makes it more portable to different environments.