Transformations

First introduced in the 1930s as the wallpaper cleaner, Play-Doh is now used by children. When you code transformations the T in ETL is limit only by your imagination and the data available. SSIS provides powerful tools for transform the raw Play-Doh of data into useful. Tools to help a business thrive. This article aims to show a few of the many ways you can handle data transformation tasks in SSIS.

Transformation Basics

Two types of transformation can occur in SSIS.
First is a simple data type transformation, such as extract data from a comma ASCII file. Transform it into an integer.
Second is change the format of data, such as take a flattened, comma delimited file. Storing the data more efficient in third normal form.
In two cases, you change the form of the data but not it’s content. If your hypothetical ASCII file data indicates that a company sold five widgets. Another example would be that when you get derived data, such as the total sales for a day. You don't change the facts but rather draw conclusions from them. You can also use the transformation step to ensure data fits predetermined business rules.

Transformation can raise some rather profound philosophical questions if you are such a mind. The substance of the data what it is in its essence remains the same while its form the data types may vary.
It almost goes without saying that there are all manners of the business rule. That can or should be enforcing the ETL process. Example, dollar values of a sale do not involve return items shouldn't be negative numbers. So you would have a rule that a monetary value associated with a sale must be greater than zero. You strength have an edict that a sale should never demand a product. The company doesn't sell, and you could enforce this rule with a foreign key constraint. If the constraint throws an error, then we would handle it in a WTF (Where's this from?) error routine.

Why do you need to transform data?

The most obvious reason is that data stored and optimized for use in one system. It will rarely match the storage and optimization needs in a second system. That's progressed to gather other, usually radically different, needs. You must consider where you should perform the transformation. Example dumps an entire large file into a staging table and then transforms it use of SQL. While the data is being moved to the "permanent" tables.

MSBI Tutorials Point