Section 9

Administering SSIS

  • Lesson 52: Creating and Configuring the SSIS Catalog
  • Lesson 53: Deploying Packages to the Package Catalog
  • Lesson 54: Configuring the Packages
  • Lesson 55: Configuring the Service
  • Lesson 56: Securing SSIS Packages
  • Lesson 57: Running SSIS Packages
  • Lesson 58: Running Packages in T-SQL and Debugging Packages
  • Lesson 59: Scheduling Packages

Chapter 52

Creating and Configuring the SSIS Catalog

One of the most significant differences between previous versions of SSIS and SQL Server 2012 is the introduction of the SSIS catalog. The catalog only applies for those who are in the project deployment model and gives you many new features for administering and configuring packages. It also enables you to run packages in T-SQL or through PowerShell.

Creating the Catalog

Behind the scenes, a catalog is simply a database where your packages are stored and configured. As packages run, it stores operational information about the packages’ run and any errors. Because the packages are in a database called SSISDB, much of their management can be done through T-SQL or PowerShell, that’s in addition to the normal way of managing them, which is through Management Studio.

The easiest way to create the SSIS catalog (you have only one catalog per database instance) and its accompanying database is with Management Studio. In Management Studio, connect to your database engine and navigate to the Integration Services node in Object Explorer. Right-click the Integration Services Catalogs node and select Create Catalog. This opens the Create Catalog dialog box (shown in Figure 52-1) where you must type a password that will encrypt all packages. To fully utilize the package catalog, you must also have the Common Language Runtime (CLR) integration turned on by checking the Enable CLR box. Click OK to create the database and catalog.

Figure 52-1

c52f001.tif

Configuring the Catalog

After the catalog is created, you’re then able to configure the catalog by right-clicking the SSISDB catalog and selecting Properties, which opens the Catalog Properties screen. One of the catalog’s jobs is to store operational log data about your packages’ execution like errors, warnings, and duration. In the Catalog Properties dialog box (Figure 52-2), you can configure how many days of that history are kept. By default, the catalog will store 365 days of history, which may be far too much for most environments.

By default, the catalog stores only basic logging. If you want to store more detailed logging for debugging, you can choose Verbose for the Server-wide Default Logging Level option, but note that this will slow down your packages and take much more space in your catalog to store the logs.

Lastly, as you deploy packages, there is a rudimentary version control system in the catalog. By default, 10 versions of your project are kept in case of a problem, but you can change that in the Catalog Properties screen with the Maximum Number of Versions per Project option.

Figure 52-2

c52f002.tif

Creating and Using Folders

In the catalog, folders are containers for multiple projects, much like a solution is in Visual Studio. Imagine a data warehousing project with many subject areas like HR, finance, and inventory. You could create a folder called Data Warehouse and deploy all the projects for each subject area into that central folder.

To create a new folder, right-click the catalog and select Create Folder. Name the folder and click OK. You cannot deploy an SSIS project in the project deployment model without a folder first being created. Security permissions can also be shared among any projects in a folder.

Try It

In this Try It, you create and configure the SSIS catalog. After this lesson, you will understand how to use the SSIS catalog and folder structure.

This lesson has no samples to download.

Lesson Requirements

Create an SSIS catalog database on your database instance. Configure the catalog to retain only 45 days of history. Also, create a folder called Data Warehouse for your projects to be deployed into.

Hints

  • You can perform all steps within Management Studio while connected to the database engine.

Step-by-Step

1. Open SQL Server Management Studio and connect to your database engine.
2. Right-click the Integration Services Catalogs node and select New Catalog.
3. Type a password into the Password area, ensure that CLR is enabled, and click OK.
4. Right-click the newly created catalog and select Properties. This opens the Catalog Properties dialog box. Change Retention Period (days) to 30 days and click OK.
5. Right-click the catalog and select New Folder. Name the folder Data Warehouse and click OK.
Please select Lesson 52 on the DVD, or online at www.wrox.com/go/ssis2012video, to view the video that accompanies this lesson.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset