Datasheet

Chapter 1: Welcome to SQL Server Integration Services
3
The data pipeline has been overhauled so that it scales to better use the multiple, dual, and quad - core
processor improvements. The Lookup Component that performs foreign key resolutions has also been
redesigned to allow for persistence of lookup cache that screams when you tune them for dimension
tables. Underneath SSIS now allows new TSQL extensions for multiple data manipulation language
(DML) operations like the
MERGE statement.
If you are looking for the latest toys, this version of SSIS has added new workflow components to control
the cache window maintenance, to generate TSQL traces, or reset row count variables. In the Data Flows,
there are new ADO Sources and Destinations to add to the OLE Sources and Destinations that were part
of the first version.
Lastly, there has been a major improvement to the development environment from the previous versions
with the removal of the cobbled - together Visual Basic for Applications (VBA) implementation. The VBA
environment was only intended as a temporary implementation to allow custom scripting within your
ETL processes, evidenced by the clunky integration and that you were limited to VB.NET only. Now the
Script Tasks and Components use an embedded version of the Microsoft Visual Studio 2008 Tools for
Applications (VSTA) environment that supports both VB and C# .NET programming languages. In
addition, you can now add web references to your ETL processes without having to code your own
wrappers to web services to make use of existing business logic or data sources. We ’ ll touch on all of
these improvements as you read through this book and explore the examples, but first let ’ s get started.
Getting Started
Most of this book will assume that you know nothing about the past releases of SQL Server DTS and
will start with a fresh look at SQL Server SSIS. After all, when you dive into the new features, you ’ ll
realize how little knowing anything about the old release actually helps you when learning this one.
However, if you don ’ t come from the world of DTS, it ’ s hard for us not to throw in a few analogies here
and there to get these folks also up to speed on SSIS. The learning curve can be considered steep at first,
but once you figure out the basics, you ’ ll be creating what would have been complex packages in DTS in
no time. To get an idea of how easy SSIS is to use, look at a tool that is a staple in the ETL world, the
Import and Export Wizard.
Import and Export Wizard
If you need to move data quickly from almost any OLE DB – compliant Data Source to a destination, you
can use the SSIS Import and Export Wizard (shown in Figure 1 - 1). In fact, many SSIS packages are born
this way. The wizard is a quick way to move the data, perform very light transformations of data, and all
versions except Express allow you to persist the logic of the data movement into a package. The basic
concept of an import/export wizard has not changed substantially from the days of DTS. You still have
the option of checking all the tables you ’ d like to transfer. However, you also get the option now of
encapsulating the entire transfer of data into a single transaction.
c01.indd 3c01.indd 3 8/28/08 12:01:14 PM8/28/08 12:01:14 PM