Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 23. Cleansing Data with the Script Component

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 ...