Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

SSIS Transformations

The SSIS transformations are the data flow components that are used to perform aggregations, sorting, merging, modifying, joining, data cleansing, and distributing the data.

Apart from these, there is an important and powerful transformation in SSIS called Lookup transformation to perform lookup operations. In this article, we will show you the list of available SSIS transformations and explains their working functionality.

SSIS Transformations with Examples

The following are a list of transformations available in SQL Server Integration Services. I suggest you click on the URL (red color links) to navigate to the required SSIS transformation and find the example with a detailed explanation.

Business Intelligence Transformations in SSIS

The following list of SSIS transformations will perform Business Intelligence operations such as Data Mining, Correcting, and cleaning the data

SSIS TransformationsDescription
Slowly Changing DimensionThis transformation coordinates the inserting and updating records in data warehouse dimension tables. It supports Type 0, Type 1, and SCD Type 2
Fuzzy Grouping TransformationIt is used to replace the wrongly typed words with correct words, by grouping technique.
Fuzzy Lookup TransformationThis SSIS transformation uses fuzzy matching to find one or more close matches present in the reference table and replace the source data with reference data.
Term Extraction TransformationIt is used to extract terms from the text present in the source data. This transformation provides four different options. And they are: Extract Nouns Only, Extracting Noun Phrases Only, Extract Nouns and Noun Phrases, and Exclusion Tab
Term Lookup TransformationIt uses the reference table to find the matched sentences in the source data and counts the number of times a term repeated in the source.
Data Mining Query TransformationThis SSIS transformation helps us to run data mining queries.
DQS Cleansing TransformationIt is used to correct the source data by applying the rules that we created for the data source.

Row Transformations in SSIS

The below list of SSIS transformations is useful to update the existing column values and to create new columns.

SSIS TransformationsDescription
Character Map TransformationIt is useful to transform input characters to uppercase, lowercase, etc.
Copy Column TransformationIt simply duplicates the source columns. Just like copying the column data, and pasting it in a new column.
Data Conversion TransformationThis transformation is used to convert the data from one data type to another data type. It is similar to CAST or CONVERT
Derived Column TransformationIt helps us to deal with SSIS expressions
Export Column TransformationIt is handy to export images, binary files, media, or any large documents from SQL Server to file system.
Import Column TransformationThis transformation is used to import data from the text file to the data flow. Do some manipulations, and then forward the data to the destination.
Script ComponentIt provides an option to perform functions that are not available in the SSIS toolbox. You can use this as the Data Source, or Transformation, or Destination.
OLE DB Command TransformationIt is used to run the SQL Statements like INSERT, UPDATE, and DELETE Statements in the Data Flow. Please refer to Insert Data, Delete Data, and Update Data articles to understand the operations.

Rowset Transformations in SSIS

The below list of SSIS transformations will create a new rowsets. And that rowsets may include aggregated data, Sorted data, or Pivoted and Unpivoted data.

SSIS TransformationsDescription
Aggregate TransformationThis SSIS transformation performs the aggregations such as SUM, MIN, AVG, etc
Sort TransformationIt is used to sort the data source in either Ascending or Descending order. It is similar to the T-SQL command ORDER BY statement.
Percentage Sampling TransformationThis SSIS transformation passes the given percentage of rows from the data source to selected output rows. And the remaining rows will be transferred to unselected output rows.
Row Sampling TransformationIt provides an option to specify the number of rows you want to retrieve from the data source.
Pivot TransformationIt is used for converting individual row data into separate columns, which is similar to SQL PIVOT. For SQL Server 2008 R2 users, I suggest you refer Pivot Transformation in 2008 R2 as well.
Unpivot TransformationIt is used to convert the DE Normalized data into normalized data. It is similar to SQL UNPIVOT

Split and Join Transformations

The following list of transformations is useful to distribute the source data to different outputs. And also to join multiple outputs as a single output.

SSIS TransformationsDescription
Conditional Split TransformationIt is like the IF or CASE statement, which checks the given condition, and based on the result, the output will send to the appropriate destination path.
Multicast TransformationThis SSIS transformation sends input data to multiple destination paths without any modification.
Union All TransformationIt is used to combine data from multiple sources (such as excel files, flat file, or multiple SQL tables), and produce one output
Merge Transformation

This SSIS transformation is used to merge two inputs, such as tables or files, and provide one output.

Merge Join TransformationThe Merge Join Transformation is useful to perform SQL Joins such as Inner Join, Left Outer Join, Full Outer Join and Right Outer Join (achieved by Swapping the tables)
Lookup TransformationThis transformation is used to compare source data with existing data present in the reference table (or Lookup Table) to find matching ones
Cache TransformationThis SSIS transformation writes data from a connected data source to a Cache file (.caw extension) using Cache Connection Manager. It is beneficial to perform Lookup on the data present in the cache file.

SSIS Audit Transformations

Below shown SSIS transformations helps to add Audit information and the Row count data

SSIS TransformationsDescription
Audit TransformationIt is used to find the auditing information such as Machine name, package name, id, date, etc
Row Count TransformationThis transformation counts the rows passed through a data flow, and stores the final count in a variable

For the people who want to save these SSIS Transformations, you can save the below-shown image

SSIS Transformations 2

Filed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes

Copyright © 2021· All Rights Reserved by Suresh.
About | Contact | Privacy Policy