Techbubbles

Integration Services Catalog in SQL Server 2012

 

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.

image

Catalog creation

  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.

image 

Integration services uses this password to encrypt sensitive data stored in catalog.

Catalog Properties

To set the properties , right click SSISDB and select properties and you will get the properties dialogue box as shown below

image

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.

Project Versioning

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

image

click yes to confirm, and then click OK to close the message box.


Share this post :


Related Posts:

%d bloggers like this: