SQL Server 2005: Integration Services
NOTE: This lab focuses on the
concepts in this module and as a result may not comply with Microsoft security recommendations.
After completing this lab, you will be able to: Create a SQL Server 2005 Integration Services (SSIS) package Create an SSIS package project Monitor the progress of an SSISpackage Log results from an SSIS package to both a text file and the Windows Event log
Exercise 1 Understanding the SSIS Interface
In this exercise, you will examine and execute a simple SSIS package that loads data into a dimension table by using the Data Flow task. You will then create a new project data source and a new package that mimics the design of thefirst package. The SSIS Designer is hosted in Business Intelligence Development Studio, an environment based on the Microsoft Development Environment (Visual Studio 2005) shell. This environment provides an integrated development environment for designing, creating, testing, debugging, and extending SSIS packages. To ease the complexity of designing SSIS packages, separate editors for data flowand control flow are provided. To implement the data load, you will: Use Business Intelligence Development Studio to create a new project and add a pre-built package to a project. Execute a package and view the graphical presentation of package execution. Examine the new interface. Create a new data source. Create a new package, add a data source to the package, and use the Data Flow Task to load adimension table with a Derived Column transform in the package. Tasks 1. Launch the SSIS design environment. Detailed Steps On the desktop, double-click Shortcut to SQL Server 2005, and then doubleclick Configure SQL Server 2005 for HOL. 2. In the Configuring Hands-on Labs Sample Data dialog box, click Next. 3. After the Next button becomes available, click Next. 4. Click OK in the HOL Configuremessage box that indicates the configuration completed successfully, and then close any open windows. 5. From the Windows taskbar, select Start | All Programs | Microsoft SQL Server 2005 | SQL Server Business Intelligence Development Studio. Business Intelligence Development Studio provides the environment in which SSIS packages are designed. 6. Click File | New | Project. 7. In the Project Typespane of the New Project dialog box, click the Business Intelligence Projects folder. 8. In the Templates pane, click the Integration Services Project icon. 9. In the Name text box, replace the default name with SSIS Exercise 1. 10. In the Location text box, enter C:\MSLabs\SQL Server 2005\User Projects. Make sure the Create directory for solution checkbox is cleared, and then click OK. Thiscreates a new SSIS project called SSIS Exercise 1 in a new solution, and creates a default empty package named Package.dtsx. A solution is the largest unit of management in the Business Intelligence Development Studio environment. A solution contains one or more projects. A project is a collection of SSIS packages, Data Sources, Data Source Views, and other items that you can use during packagedevelopment. 1. After the solution loads, click Project | Add Existing Item. 1.
You are logged on with user name Administrator and password MSEvent.123.
Add an existing SSIS
Page 3 of 16
Integration Services Tasks package to the project. Detailed Steps For the exercises in this lab, you will use objects stored in the C:\MSLabs\SQL Server 2005\Lab Projects\SSIS Lab folder. 2. In the AddExisting Item - SSIS Exercise 1 dialog box, navigate to C:\MSLabs\SQL Server 2005\Lab Projects\SSIS Lab\SSISLabDimProduct.dtsx and then click Add. This step adds an existing SSIS package to the SSIS Exercise 1 project. In the Solution Explorer, expand the SSIS Packages folder if necessary, and then double-click the SSISLabDimProduct.dtsx package. Double-clicking on the package object opens the...