Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 27

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 an invalid date, perhaps you want to 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 one of three roles: transform, source, or destination:

• Transform—Generally, the focus of your Data Flow will be on using the script as a transform. In this role, you can perform advanced cleansing with the out-of-the-box components.
• Source—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.)
• Destination—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.

You can write your script in VB.NET or C#, but once you select a language, you can’t change it. You can select the language by double-clicking the Script Component and going to the Script page of the Script Transformation Editor (shown in Figure 27-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 needs to be written to. Otherwise, the variable will be locked for the duration ...