Professional SQL Server™ 2005 Integration Services

7.6. Script Component

The Script Component provides another type of scripting in SSIS. This component can be used only in the Data Flow portion of your SSIS package. The purpose of this component is to provide, consume, or transform data. Script components come in the following three types:

• Source Type Component: The role of this Script Component is to provide data to your Data Flow task. You can define outputs and their types and use script code to populate them. An example would be reading in a complex file format, possibly XML or something more archaic like a COBOL copybook file from the mainframe. I've worked with several files that were way too complex for any generic flat file reader and required custom coding to read.

• Destination Type Component: This type of Script Component consumes data much like an Excel or Flat File destination. One use for this type of Script Component is outputting data for batch processing to a mainframe system.

• Transformation Type Component: This type of Script Component can perform custom transformations on data. It consumes input columns and produces output columns. You would use the component when one of the built-in transformations just isn't flexible enough.

7.6.1. Using the Script Component

Here you'll see how to create and use a Script Component. In this example, you have to process a flat file and make sure the data is good. The good records will go on to their appropriate table while the questionable records will be sent to an error table ...