Integration Services Transformations
TOC
Collapse the table of content
Expand the table of content

Integration Services Transformations

 

Applies To: SQL Server 2016

SQL Server Integration Services transformations are the components in the data flow of a package that aggregate, merge, distribute, and modify data. Transformations can also perform lookup operations and generate sample datasets. This section describes the transformations that Integration Services includes and explains how they work.

The following transformations perform business intelligence operations such as cleaning data, mining text, and running data mining prediction queries.

TransformationDescription
Slowly Changing Dimension TransformationThe transformation that configures the updating of a slowly changing dimension.
Fuzzy Grouping TransformationThe transformation that standardizes values in column data.
Fuzzy Lookup TransformationThe transformation that looks up values in a reference table using a fuzzy match.
Term Extraction TransformationThe transformation that extracts terms from text.
Term Lookup TransformationThe transformation that looks up terms in a reference table and counts terms extracted from text.
Data Mining Query TransformationThe transformation that runs data mining prediction queries.
DQS Cleansing TransformationThe transformation that corrects data from a connected data source by applying rules that were created for the data source.

The following transformations update column values and create new columns. The transformation is applied to each row in the transformation input.

TransformationDescription
Character Map TransformationThe transformation that applies string functions to character data.
Copy Column TransformationThe transformation that adds copies of input columns to the transformation output.
Data Conversion TransformationThe transformation that converts the data type of a column to a different data type.
Derived Column TransformationThe transformation that populates columns with the results of expressions.
Export Column TransformationThe transformation that inserts data from a data flow into a file.
Import Column TransformationThe transformation that reads data from a file and adds it to a data flow.
Script ComponentThe transformation that uses script to extract, transform, or load data.
OLE DB Command TransformationThe transformation that runs SQL commands for each row in a data flow.

The following transformations create new rowsets. The rowset can include aggregate and sorted values, sample rowsets, or pivoted and unpivoted rowsets.

TransformationDescription
Aggregate TransformationThe transformation that performs aggregations such as AVERAGE, SUM, and COUNT.
Sort TransformationThe transformation that sorts data.
Percentage Sampling TransformationThe transformation that creates a sample data set using a percentage to specify the sample size.
Row Sampling TransformationThe transformation that creates a sample data set by specifying the number of rows in the sample.
Pivot TransformationThe transformation that creates a less normalized version of a normalized table.
Unpivot TransformationThe transformation that creates a more normalized version of a nonnormalized table.

The following transformations distribute rows to different outputs, create copies of the transformation inputs, join multiple inputs into one output, and perform lookup operations.

TransformationDescription
Conditional Split TransformationThe transformation that routes data rows to different outputs.
Multicast TransformationThe transformation that distributes data sets to multiple outputs.
Union All TransformationThe transformation that merges multiple data sets.
Merge TransformationThe transformation that merges two sorted data sets.
Merge Join TransformationThe transformation that joins two data sets using a FULL, LEFT, or INNER join.
Lookup TransformationThe transformation that looks up values in a reference table using an exact match.
Cache TransformThe transformation that writes data from a connected data source in the data flow to a Cache connection manager that saves the data to a cache file. The Lookup transformation performs lookups on the data in the cache file.
Balanced Data Distributor TransformationThe transformation distributes buffers of incoming rows uniformly across outputs on separate threads to improve performance of SSIS packages running on multi-core and multi-processor servers.

Integration Services includes the following transformations to add audit information and count rows.

TransformationDescription
Audit TransformationThe transformation that makes information about the environment available to the data flow in a package.
Row Count TransformationThe transformation that counts rows as they move through it and stores the final count in a variable.

You can also write custom transformations. For more information, see Developing a Custom Transformation Component with Synchronous Outputs and Developing a Custom Transformation Component with Asynchronous Outputs.

Community Additions

ADD
Show:
© 2016 Microsoft