Data Flow best practices in Transformations

One of the main goals of transforming data is to do all those transformations in memory to achieve the best performance possible in spite of saving data temporarily to disk. For that reason, if the transform uses some type of component that forces any delay, its use should be reduced or even avoided. The SSIS components could be one of the following three types:

• Row Transformations: This type of transformation does not block data in the pipeline. While new columns could be added, row transformations do not create any additional records and data is not blocked in the pipeline. Row transformations are also known as synchronous transformations because they reuse existing buffers and do not require data to be copied to a new buffer to complete the transformation. Some examples of this type of transformations are the Derived Column, Multicast, Lookup, Copy Column, Conditional Split, OLE DB Command, and several others that do not block Data Flow in...