Microsoft® SQL Server® 2012 Bible

1181

Chapter 52: Building, Deploying, and Managing ETL Workfl ows in Integration Services

52

Transform Defi nition/Use

Data Conversion Adds a copy of an existing column to the data fl ow, enabling data

type conversions in the process.

Data Cleansing New with SQL Server 2012, this task cleanses data in the data fl ow by

using a Data Quality Services Connection. Rows can be mapped to

DQS Domains and either corrected according to rules or simply

marked as violations of rules.

Data Mining Query Runs a DMX query for each row of the data fl ow, enabling rows to be

associated with predictions, such as the likelihood that a new cus-

tomer makes a purchase or the probability that a transaction is

fraudulent.

Derived Column Uses expressions to generate values that can either be added to the

data fl ow or replace existing columns.

Export Column Writes large object data types (DT_TEXT, DT_NTEXT, or DT_IMAGE)

to fi les specifi ed by a fi lename contained in the data fl ow. For exam-

ple, large text objects could be extracted into different fi les for inclu-

sion in a website or text index.

Fuzzy Grouping Identifi es duplicate rows in the data fl ow using exact matching for any

data type and fuzzy matching for string data types (DT_STR and

DT_WSTR).

Fuzzy Lookup Similar to the Lookup transform, except that when an exact lookup

fails, a fuzzy lookup is attempted for any string columns (DT_STR and

DT_WSTR).

Import Column Reads large object data types (DT_TEXT, DT_NTEXT, or DT_IMAGE)

from fi les specifi ed by a fi lename contained in the data fl ow, adding

the text or image objects as a new column in the data fl ow.

Lookup Finds rows in a database table or cache that match the data fl ow and

includes selected columns in the data fl ow, much like a join between

the data fl ow and a table or cache. For example, a product ID could

be added to the data fl ow by looking up the product name in the

master table.

Merge Combines the rows of two sorted data fl ows into a single data fl ow.

For example, if some of the rows of a sorted data fl ow are split by an

error output or Conditional Split transform, then they can be merged

again. Rows in the data fl ow must be sorted, either by using advanced

editor of ole db source or the sort component.

Merge Join Provides SQL join functionality between data fl ows sorted on the join

columns. Rows in the data fl ow must be sorted by using the method

described for the merge transform.

Multi Cast Copies every row of an input data fl ow to many different outputs.

Ole DB Command Executes a SQL statement (such as UPDATE or DELETE) for every row

in a data fl ow, row-by-row. Use with caution because this is a slow

transform.

Continues

c52.indd 1181c52.indd 1181 7/31/2012 10:29:30 AM7/31/2012 10:29:30 AM

http://www.it-ebooks.info