30-01-2013, 01:45 PM
SQL Server Integration Services
SQL Server.pdf (Size: 421.5 KB / Downloads: 48)
SQL Server Integration Services
16-2 Introduction to SQL Server 2005 Copyright © 2005 Accelebrate, Inc
Microsoft says that SQL Server 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.
The Import and Export Wizard
Though SSIS is almost infinitely customizable, Microsoft has produced a simple
wizard to handle some 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 wizard from the Tasks entry on the shortcut
menu of any database in the Object Explorer window of SQL Server Management
Studio.
Creating a Package
The Import and Export Wizard is easy to use, but it only taps a small part of the
functionality of SSIS. To really appreciate the full power of SSIS, you’ll need to use
BIDS to build an SSIS package. A package is a collection of SSIS objects including:
• Connections to data sources.
• Data flows, which include the sources and destinations that extract and load
data, the transformations that modify and extend data, and the paths that link
sources, transformations, and destinations.
• Control flows, which include tasks and containers that execute when the
package runs. You can organize tasks in sequences and in loops.
• Event handlers, which are workflows that runs in response to the events raised
by a package, task, or container.