Sometimes you can't accomplish your data cleansing goal in a Derived Column Transform, and you must get more advanced. Say, for example, you want to run a routine where any character data is removed from the data or if the data coming in is not a date, perhaps you replace it with today's date. In these examples, you can use a Script Component in the Data Flow Task. The Script Component can play three roles: a transform, source, or destination.
Generally, the focus of your Data Flow will be on using the script as a transform. In this role, you can use it to perform advanced cleansing that can be done with the out-of-the-box components
When the script is used as a source, you can apply advanced business rules to your data as it's being pulled out of the source system. This happens sometimes with Cobol files
Lastly, when the script is used as a destination, you can use the script to write out to a non-OLE DB destination like XML or SharePoint
Your script can be written in VB.NET or C#, but once you select a language, you can't change it. You can select the language by double-clicking on the Script Component and going to the Script page of the Script Transformation Editor (shown in Figure 23-1). You can also select any variables you want to pass into the script in this page. Make sure to select the variable for ReadWrite only if the variable is needed to be written to. Otherwise, the variable might be locked for the duration of the script's ...