Magisterio

14:02 09/01/201314:02 09/01/2013Chapter 16: SQL Server Integration Services
In this chapter:
• • • • • • • The Import and Export Wizard Creating a Package Working with Connection Managers Building Data Flows Building Control Flows Creating Event Handlers Saving and Running Packages

Files needed:
• • ISProject1.zip ISProject2.zip

SQL Server Integration Services Microsoft says that SQLServer Integration Services (SSIS) “is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing.” A simpler way to think of SSIS is that it’s the solution for automating SQL Server. SSIS provides a way to build packages made up of tasks that can move data around from place to place and alter it on the way.There are visual designers (hosted within Business Intelligence Development Studio) to help you build these packages as well as an API for programming SSIS objects from other applications. In this chapter, you’ll see how to build and use SSIS packages. First, though, we’ll look at a simpler facet of SSIS: The SQL Server Import and Export Wizard. If you choose to use the supplied solution filesrather than building your own, you’ll need to edit the properties of the OLE DB Connection Managers within the projects to point to your own test server. You’ll learn more about Connection Managers in the “Working with Connection Managers” section later in this chapter.

The Import and Export Wizard
Though SSIS is almost infinitely customizable, Microsoft has produced a simple wizard to handlesome of the most common ETL tasks: importing data to or exporting data from a SQL Server database. The Import and Export Wizard protects you from the complexity of SSIS while allowing you to move data between any of these data sources: • • • • • SQL Server databases Flat files Microsoft Access databases Microsoft Excel worksheets Other OLE DB providers

You can launch the Import and Export wizardfrom the Tasks entry on the shortcut menu of any database in the Object Explorer window of SQL Server Management Studio.

Try It!
To import some data using the Import and Export Wizard, follow these steps: 1. Launch SQL Server Management Studio and log in to your test server.
16-2 Introduction to SQL Server 2005 Copyright © 2005 Accelebrate, Inc

The Import and Export Wizard 2. Open a newquery window. 3. Select the master database from the Available Databases combo box on the toolbar. 4. Enter this text into the query window: CREATE DATABASE Chapter16 5. 6. 7. 8. 9. Click the Execute toolbar button to create a new database. Expand the Databases node in Object Explorer Right-click on the Chapter16 database and select Tasks Import Data. Read the first page of the Import and ExportWizard and click Next. Select SQL Native Client for the data source and provide login information for your test server. 10. Select the AdventureWorks database as the source of the data to import. 11. Click Next. 12. Because you’re importing data, the next page of the wizard will default to connection information for the Chapter16 database. Click Next. 13. Select Copy Data From One or More Tables orViews and click Next. Note that if you only want to import part of a table you can use a query as the data source instead. 14. Select the HumanResources.Department, HumanResources.Employee, HumanResources.EmployeeAddress, HumanResources.EmployeeDepartmentHistory, and HumanResources.EmployeePayHistory tables, as show in Figure 16-1. As you select tables, the wizard will automatically assign namesfor the target tables.

_

Introduction to SQL Server 2005

16-3

SQL Server Integration Services

Figure 16-1: Selecting tables to import

15. Click the Edit button in the Mapping column for the HumanResources.Department table. 16. The Column Mappings dialog box lets you change the name, data type, and other properties of the destination table columns. You can also set other options...