September 8, 2012 06:07 by Jervis
Integration Services Catalog is a new feature in SQL Server 2012 which supports the centralization of storage of packages and configuration files. You can host only one catalog for one SQL Server Instance. When you deploy your project using project deployment model all project components stored in catalog. This post describes how to create a catalog and set the properties.
1. In SQL Server Management Studio , right click the Integration Services Catalogs folder and Select Create Catalog.
2. In Create Catalog dialog box, Select Enable Automatic execution of Integration Services Stored Procedure.
3. Catalog database name can not be changed and it is set to default name SSISDB. Provide the password and click ok as shown below.
Integration services uses this password to encrypt sensitive data stored in catalog.
To set the properties , right click SSISDB and select properties and you will get the properties dialogue box as shown below
The default encryption algorithm is AES_256. Operations include activities such as project deployment, project validation and project execution. Integration Services stores this information in catalog. You can see the active operations in catalog by right clicking SSISDB database.
Each time you re-deploy a project with the same name to same folder 10 versions are maintained in list.
- Locate the project under SSISDB database, right click on it and select versions.
- In the project versions dialogue box, select the version to restore and click restore button
click yes to confirm, and then click OK to close the message box.