Exercise 1: Exploring the Integration Services Project

In this exercise, you will explore a partially completed Integration Services project design to populate the Adventure Works data warehouse. You will explore the design of the packages and specifically the use of a project connection manager and package parameters.

Task 1 – Opening the Integration Services Project

In this task, you will open the AdventureWorksBI solution that contains the Populate DW Integration Services project.

1. To open Visual Studio 2010, click the Start button, and then select All Programs | Microsoft Visual Studio 2010 | Microsoft Visual Studio 2010.
2. To open the existing solution, on the File menu, select Open | Project/Solution.

3. In the Open Project window, navigate to the Assets\AdventureWorksBI folder located in the Source folder for this lab, select the AdventureWorksBI.sln file, and then click Open.

   Note:

   This solution contains a single project that represents a partially completed ETL solution.

4. If the Error List opens, close the list to increase space in the designer window.

   Figure 1

   Closing the Error List

5. If the AdventureWorksDW2012 database in your environment exists on an instance other than localhost, in Solution Explorer, in the Connection Managers folder, right-click the AdventureWorksDW2012.conmgr connection manager, and then select Open. Modify the Server Name property, and then click OK.

Task 2 – Setting the Configurations

In this task, you will explore the QuotaExtracts folder that contains two quota extract files. You will use a script to create a configuration record to store the path to this folder so that the LoadFactSalesQuota package can reference the contained files.

1. To open Windows Explorer, right-click the Start button, and then select Open Windows Explorer.
2. In Windows Explorer, navigate to the Assets\QuotaExtracts folder located in the Source folder for this lab.
3. Notice the two extract files representing sales quota data for the years 2004 and 2005.

4. To copy the folder path to the clipboard, in the address box, right-click the QuotaExtracts folder, and then select Copy Address as Text.

   Figure 2

   Copying the Address

5. To open SQL Server Management Studio, click the Start button, and then select All Programs | Microsoft SQL Server 2012 | SQL Server Management Studio.
6. If the Connect to Server window does not open, in Object Explorer, click Connect, and then select Database Engine.
7. In the Connect to Server window, ensure the Server Type dropdown list is set to Database Engine.
8. In the Server Name box, enter the name of the database engine instance that hosts the AdventureWorksDW2012 database.
9. Click Connect.
10. To open the script file, on the File menu, select Open | File.

11. In the Open File window, navigate to the \Assets folder located in the Source folder for this lab, select the InsertConfiguration.sql file, and then click Open.

    Note:

    This script will insert a single row into the etl.Configurations table. This table is a custom table used to store configuration name/value pairs. Specifically, you will be inserting a row that defines the folder location for the quota extract files.

12. On the Query menu, select Specify Values for Template Parameters.

13. In the Specify Values for Template Parameters window, right-click inside the Value box, and then select Paste.

    Figure 3

    Pasting the Address

14. Click OK.

    To execute the script, on the toolbar, click Execute. You should see the same output as the image below.

    Figure 1

    Script Results

Task 3 – Exploring the Master Package

In this task, you will explore the purpose and design of the Master package.

1. Switch to Visual Studio 2010.
2. In Solution Explorer, right-click the Master.dtsx package, and then select Open.
3. To understand the package purpose, read the comments (in bold) located on the left side of the control flow design.

4. If necessary, use the zoom control to improve readability of the package design.

   Figure 4

   Locating the Zoom Control

5. To review the ETLDate package parameter, select the Parameters tab.

   Figure 5

   Selecting the Parameters Tab

6. Notice that the ETLDate parameter has been configured to be required. This means a valid value must be passed into the package at execution time.
7. Select the Control Flow tab.
8. To open the Variables window, on the SSIS menu, select Variables.
9. Notice that this package defines a single variable to store the ETLID. The purpose of this variable is described in the package comments.
10. To close the Variables window, click the Variable window’s close button.
11. To understand the package control flow design, read the comments located on the right side of the control flow design.
12. To review the LoadFactSalesQuota execute package task configuration, right-click the LoadFactSalesQuota task, and then select Edit.

13. In the Execute Package Task Editor window, select the Package page.

    Figure 6

    Selecting the Package Page

14. Notice the ReferenceType property is set to Project Reference.

    Note:

    The Project Reference setting is a new feature in SQL Server Code2012 Integration Services. No longer is there the requirement to create and use connection managers to locate the package.

15. Select the Parameter Bindings page.
16. Review the parameter binding, and notice that the ETLID generated for the Master package execution is passed to the child package’s ParentETLID parameter. This will enable the child package to log the executing package’s ETLID.
17. Click Cancel.

Task 4 – Exploring the LoadFactSalesQuota Package

In this task, you will explore the purpose and the design of the LoadFactSalesQuota package.

1. In Solution Explorer, right-click the LoadFactSalesQuota.dtsx package, and then select Open.
2. To understand the package purpose, read the comments (in bold) located on the left side of the control flow design.
3. Select the Parameters tab.
4. Notice that the ParentETLID parameter has been configured to be required. This means a valid value must be passed into the package at execution time. This is achieved through the parameter bindings of the Execute Package task in the Master package.
5. Select the Control Flow tab.
6. To understand the package control flow design, read the comments located on the right side of the control flow design.
7. To review the Insert New Quotas Data Flow Task configuration, right-click the Insert New Quotas task, and then select Edit.
8. To understand the data flow purpose and design, read all of the comments.

9. Notice the Transformations group that allows the package developer to group components together.

   Note:

   This capability is new in SQL Server 2012 Integration Services. In earlier versions of SQL Server, this feature was only available for grouping control flow executables (tasks and containers).