Page 195 - DCAP508_DATABASE_ADMINISTRATION

Unit 14: SQL Server Integration Services

SSIS is a component of SQL Server 2005/2008 and is the successor of DTS (Data Transformation Notes
Services) which had been in SQL Server 7.0/2000. From an end-user perspective DTS and SSIS
may appear similar, however they are actually quite different. SSIS has been completely written
from scratch and overcomes several limitations of DTS. Though the list of differences between
DTS and SSIS is quite large, something of note is that the internal architecture of SSIS is completely
different from DTS. It has segregated the Data Flow Engine from the Control Flow Engine or
SSIS Runtime Engine and hence improves the performance by a significant amount.

Notes In this unit, when referring to “SSIS 2008” it is the SSIS version that comes with SQL
Server 2008 whereas “SSIS 2005” refers to the SSIS version that comes with SQL Server 2005.
Developers tasked with creating or maintaining SSIS packages use a visual development tool
based on Microsoft Visual Studio called the SQL Server Business Intelligence Development
Studio (BIDS). It allows users to edit SSIS packages using a drag-and-drop user interface. A
scripting environment in which to write programming code is also available in the tool. A
package holds a variety of elements that define a workflow. Upon package execution, the tool
provides color-coded, real-time monitoring.

Connections

A connection includes the information necessary to connect to a particular data source. Tasks can
reference the connection by its name, allowing the details of the connection to be changed or
configured at run time.

Task A task is an atomic work unit that performs some action. There are a couple of
dozen tasks that ship in the box, ranging from the file system task (which can copy or
move files) to the data transformation task. The data transformation task actually copies
data; it implements the ETL features of the product.

Precedence Constraints

Tasks are linked by precedence constraints. The precedence constraint preceding a particular
task must be met before that task executes. The run time supports executing tasks in parallel if
their precedence constraints so allow. Constraints may otherwise allow different paths of
execution depending on the success or failure of other tasks. Together with the tasks, precedence
constraints comprise the workflow of the package.

Event Handlers

A workflow can be designed for a number of events in the different scopes where they might
occur. In this way, tasks may be executed in response to happenings within the package —such
as cleaning up after errors.

Variables

Tasks may reference variables to store results, make decisions, or affect their configuration.
A package may be saved to a file or to a store with a hierarchical namespace within a SQL Server
instance. In either case, the package content is persisted in XML.

LOVELY PROFESSIONAL UNIVERSITY 189