Here is the next in my series of posts explaining how common tasks in DTS can now be implemented in SSIS.
Changing values in the pipeline
Data transformation in DTS is done using ActiveX Script. Although being notoriously slow (ActiveX Script is uncompiled code), limited in its capabilities (it can really only operate on a row at a time) and fraught with hidden irregularities (witness its ability to arbitrarily misinterpret valid dates) ActiveX Script is a quick and easy way to provide inline manipulation of data before it reaches its destination. Most commonly, ActiveX Script was used for data cleansing and data scrubbing operations such as:
- Applying proper casing to textual values
- Concatenating incoming values in order to derive a new value
- Validating and replacing invalid data
In SSIS, the alternative to using ActiveX Script in these scenarios is to use the Derived Column component. This component leverages the SSIS expression language to provide fast, error-free manipulation of data in the pipeline.
Some people are of the opinion that producing complicated transformations in the Derived Column component is difficult given its interface and I wouldn't disagree with them (I hope for better in the future and have requested that here). In these scenarios it is wise to employ the use of a synchronous script component to carry out your transformations. The SSIS script component uses compiled .Net code and hence is not susceptible to the vagaries of ActiveX scripting.