Datasheet

backups, index defragmentation, database consistency checking, and so on) use SSIS behind the scenes
to coordinate the administration operations.
Optimizing and scaling SSIS is another common responsibility for both DBAs and developers alike.
Chapter 12 targets scaling SSIS, including ways to optimize destinations and how to take advantage of
SSIS functionality to make faster, more scalable packages.
SSIS Review
Many of you have practical, learn-on-the-go experience using SSIS, and are looking to this book to take
your knowledge to the next level and to fill in any knowledge gaps. Others of you have a good book
knowledge of SSIS and want to extend your skills. And there may be those of you (like us) who like to
dive right in, skip the intro books, and go right for the expert material.
To set the stage and provide a common starting point, we will walk you through a package creation that
involves many of the SSIS key concepts. It’s impossible to wrap all the SSIS functionality into this one
example, but you will get a good review of the basic features. If you feel you already know how to use
the basic SSIS features comfortably, feel free to skip this section. The ensuing discussions, however,
assume that you know some of these basic concepts and will not walk you through these steps again.
You should be familiar with some basics such as how to create a solution. This example assumes some
SSIS background, so if you need a more fundamental review, see one of the starter SSIS books available
such as Professional SQL Server 2005 Integration Services (Wiley Publishing, 2006).
In this package creation walk-through, you will start by developing a simple package Data Flow that
pulls a limited range of data (by using package variables) from a source and adds some data transforma-
tion steps. Since SSIS includes more than just processing data, you will then be working with the control
flow and creating complementary tasks and containers with Precedence Constraints to control what
happens in what order in this example. The final step is executing the package.
To begin, first start by opening the Business Intelligence Development Studio (BIDS) tool and creating a
new SSIS project called
ExpertSSIS. Then, rename the default package that is created (Package.dtsx)
to
Chapter1.dtsx. Confirm that you’d like to rename the package object as well.
Creating a Connection Manager
After creating the package, you can create the first connection manager. Right-click in the Connection
Manager pane at the bottom of the package editor and select New OLE DB Connection. On the
Configure OLE DB Connection Manager screen, if you do not have an AdventureWorks connection
in the Data Connections list, click New. Type localhost for the Server Name property, and select
AdventureWorks from the Database drop-down box. Click OK to save the connection, and click OK
again when you have the
localhost.AdventureWorks connection selected. Right-click the newly
created
localhost.AdventureWorks connection manager and rename it to AdventureWorks.
7
Chapter 1: Getting Started
04_134115 ch01.qxp 4/24/07 6:40 PM Page 7