Page 199 - DCAP508_DATABASE_ADMINISTRATION

Unit 14: SQL Server Integration Services

14.4 Features of the Data Flow Task Notes

SSIS provides the following built-in transformations:
Conditional Split,
Multicast
Union-All, Merge, and Merge Join

Sort
Fuzzy Grouping
Lookup and Fuzzy Lookup
Percentage Sampling and Row Sampling
Copy/Map, Data Conversion, and Derived Column
Aggregation
Data Mining Model Training, Data Mining Query, Partition Processing, and Dimension
Processing

Pivot and Unpivot
Slowly Changing Dimension
Script Component
Audit
Cache Transform
Export and Import Column
OLE DB Command
Row Count
Term Extraction
Term Lookup
The Conditional Split transformation is used to speed up the query on the source table based on
a particular condition. It is similar to the “if..else” construct in the C language.

14.5 SSIS Components

As described in the introduction, SSIS creates packages which are composed of tasks that can
move data from source to destination, and if necessary transform it. Within SSIS package the
workflow can be defined, the SSIS runtime engine ensures the tasks inside the package are
executed according to the workflow. Following is a description of the different tasks/
components/executables of a package.
Package
A package is a collection of tasks which are executed in an orderly fashion by SSIS runtime
engine. It is an XML file, which can be saved on SQL Server or on a file system. A package can be
executed by SQL Server Agent Job, DTEXEC command (a command line utility bundled with
SSIS to execute a package; another similar utility DTEXECUI, has a GUI), from BIDS environment
or by calling one package by another package (achieves modular approach). You can use DTUTIL
utility to move package from file system to SQL Server or vice versa. Alternatively the
undocumented sp_dts_getpackage/sp_ssis_getpackage and sp_dts_putpackage/
sp_ssis_putpackage stored procedures which reside in msdb system database can be used.

LOVELY PROFESSIONAL UNIVERSITY 193