How to transform data in SSIS and what are available data transformations in SSIS?

Problem: How to transform data in SSIS and what are available data transformations in SSIS?

Solution:   Data flow transformations

Transformations are part of ETL activities, as we get the source files from different regions and different file formats. To combine all this sources into a single destination table we need to set the standards and data types and cleanse the values for certain extent to fit the need.

If you open SSIS Toolbox in data flow you will notice the area with this view.

All the Transformations that SSIS provides are inside in the data flow area of the package, some of them are used to aggregate, merge, distribute, and modify data. Transformations can also perform lookup operations and generate sample data sets.

Below is the list of transformations and the

Transformation	Description
Slowly Changing Dimension Transformation	configures the updating of a slowly changing dimension.
Fuzzy Grouping Transformation	standardizes values in column data.
Fuzzy Lookup Transformation	looks up values in a reference table using a fuzzy match.
Term Extraction Transformation	extracts terms from text.
Term Lookup Transformation	looks up terms in a reference table and counts terms extracted from text.
Data Mining Query Transformation	runs data mining prediction queries.
DQS Cleansing Transformation	corrects data from a connected data source by applying rules that were created for the data source.
Character Map Transformation	applies string functions to character data.
Copy Column Transformation	adds copies of input columns to the transformation output.
Data Conversion Transformation	converts the data type of a column to a different data type.
Derived Column Transformation	populates columns with the results of expressions.
Export Column Transformation	inserts data from a data flow into a file.
Import Column Transformation	reads data from a file and adds it to a data flow.
Script Component	uses script to extract, transform, or load data.
OLE DB Command Transformation	runs SQL commands for each row in a data flow.
Aggregate Transformation	performs aggregations such as AVERAGE, SUM, and COUNT.
Sort Transformation	sorts data.
Percentage Sampling Transformation	creates a sample data set using a percentage to specify the sample size.
Row Sampling Transformation	creates a sample data set by specifying the number of rows in the sample.
Pivot Transformation	creates a less normalized version of a normalized table.
Unpivot Transformation	creates a more normalized version of a nonnormalized table.
Conditional Split Transformation	routes data rows to different outputs.
Multicast Transformation	distributes data sets to multiple outputs.
Union All Transformation	merges multiple data sets.
Merge Transformation	merges two sorted data sets.
Merge Join Transformation	joins two data sets using a FULL, LEFT, or INNER join.
Lookup Transformation	looks up values in a reference table using an exact match.
Cache Transform	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 Transformation	The 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.
Audit Transformation	makes information about the environment available to the data flow in a package.
Row Count Transformation	counts rows as they move through it and stores the final count in a variable.

Example:

In the example, I would like to show we can use derived column to add the date of load as today’s date from a variable.

Step 0: Create a variable date as string and set the scope to package. Create an expression for setting the default values as today’s date.

Step 1: From the tool box drag a data flow task and use OLE DB Source and Destination and also in between the source and destination I want to use a transformation to inject todays date as load date.

Step 2: map the column from derived column output

Step 3: Execute the task.

Output:

As you can see the the date stamp  of input date is today.  This becomes handy when there are lot of files and batch loads.