Exercise 2: Deploying and Managing the Integration Services Project

In this exercise, you will create the SSIS Catalog and then deploy the Populate DW project to this catalog. Once deployed you will execute the Master package and review the execution results using the dashboard and standard reports.

Task 1 – Creating the SSIS Catalog

In this task, if you do not already have a SSIS Catalog, you will create one. Additionally, you will create the AdventureWorksBI folder within the catalog.

1. Switch to SQL Server Management Studio.

2. In Object Explorer, expand the Integration Services Catalogs folder.

   Figure 7

   Expanding the Integration Services Folder

3. If there are no items in the Integration Services Catalogs folder, right-click the Integration Services Catalogs folder, and then select Create Catalog.
4. In the Create Catalog window, in the Password box, enter Password;1.
5. In the Retype Password box, re-enter the same password, and then click OK.
6. To create a folder in the catalog, right-click SSISDB (beneath the Integration Services Catalogs folder) and then select Create Folder.

7. In the Create Folder window, in the Folder Name box, enter AdventureWorksBI.

   Figure 8

   Entering the Folder Name

8. Click OK.

9. In Object Explorer, expand the Databases folder, and notice the SSISDB database.

   Note:

   This database can be used to store deployed projects consisting of packages, connection managers and various configurations. It is also stores execution and validation messages. Additionally, it includes an API in the form of stored procedures that can be used by developers and administrators to perform various tasks, like execute packages.

Task 2 – Deploying the Integration Services Project

In this task, you will deploy the Populate DW project to the SSIS Catalog.

1. Switch to Visual Studio 2010.
2. In Solution Explorer, right-click the Populate DW project, and then select Deploy.
3. In the Integration Services Deployment Wizard, click Next.

4. In the Select Source step, notice the default source.

   Note:

   The project deployment option will deploy the project build which is an .ispac file.

5. Click Next.
6. In the Select Destination step, in the Server Name box, if necessary, modify the text to the name of the instance that hosts the SSIS Catalog.
7. To the right of the Path box, click Browse.

8. In the Browse for Folder or Project window, select the AdventureWorksBI folder.

   Figure 9

   Selecting the Folder

9. Click OK.
10. Click Next.
11. Click Deploy.
12. When the deployment has succeeded, click Close.

Task 3 – Executing the Master Package

In this task, you will execute the Master package three times. The first execution will use the Run Package window and the ETLDate will be set to 1/1/2004. The second and third package executions will be run from a script, for the ETLDate values of 1/1/2005 and 1/1/2006. Note that no quota extract file exists for 2006.

1. Switch to SQL Server Management Studio.

2. In Object Explorer, expand Integration Services Catalogs| SSIDB | AdventureWorksBI | Projects | Populate DW | Packages.

   Figure 10

   Expanding to the Project Packages

3. Right-click the Master.dtsx package, and then select Execute.

4. In the Run Package window, notice the error message beneath the window title.

   Figure 111

   Reviewing the Error Message

5. To set the ETLDate parameter, click the ellipsis.

   Figure 12

   Locating the Ellipsis

6. In the Edit Literal Value for Execution window, in the Value box, replace the text with 1/1/2004.

   Note:

   Recall that this value will be used to locate the quota extract file.

7. Click OK.
8. Select the Connection Managers tab.
9. Notice that project connection manager property values can be overridden. Do not modify any of the property values.
10. To execute the package, click OK.

11. In the dialog window, notice the ID for the operation, and then click No.

    Note:

    Each package execution generates a unique operation ID for logging purposes.

12. Use the steps in this task to configure the execution the Master package again, but with an ETLDate parameter value of 1/1/2005. Do not execute the package.

13. Instead of executing the package immediately, click the Script button, and then select New Query Editor Window.

    Figure 13

    Scripting the Package Execution

14. Click Cancel.
15. In the query window, review the T-SQL script used to execute the package, and notice the assignment of the parameter value.
16. On the toolbar, click Execute.
17. Notice the result returned. The result returned by the script execution represents the operation ID.

18. In line four of the script, modify the value of 2005 to 2006.

    Note:

    Recall that there is no quota extract file for 2006. This package execution will fail.
    Figure 14

    Modifying the Parameter Value in the Script

19. Execute the script.

Task 4 – Monitoring the Integration Services Environment

In this task, you will review the Integration Services environment. Specifically you will review the failed package execution and drill through to read messages that describe why the package execution failed.

1. In Object Explorer, right-click SSIDB (the catalog, not the database), and then select Reports | Standard Reports | Integration Services Dashboard.

2. Notice the Execution Information section detailing one failed and two successful executions.

   Figure 15

   Reviewing the Execution Information

3. Review the remaining report information.
4. Click the All Messages link available for each operation to review the error messages.

Task 5 – Finishing Up

In this task, you will close all open applications.

1. To close SQL Server Management Studio, on the File menu, select Exit.
2. If prompted to save changed items, click No.
3. To close SQL Server Business Intelligence Studio, on the File menu, select Exit.
4. If prompted to save changed items, click No.
5. Close Windows Explorer.