Configuring the Script Component in the Script Component Editor
Before you write custom code in the Script component, you must select the type of data flow component that you want to create—source, transformation, or destination—and then configure the component's metadata and properties in the Script Transformation Editor.
Selecting the Type of Component to Create
When you add a Script component to the Data Flow pane of SSIS Designer, the Select Script Component Type dialog box appears. You preconfigure the component as a source, transformation, or destination. After you make this initial selection, you can continue to configure the component in the Script Transformation Editor.
To set the default script language for the Script component, use the Scripting language option on the General page of the Options dialog box. For more information, see General Page.
Understanding the Two Design-Time Modes
In SSIS Designer, the Script component has two modes: metadata design mode and code design mode.
When you open the Script Transformation Editor, the component enters metadata design mode. In this mode, you can select input columns, and add or configure outputs and output columns, but you cannot write code. After you have configured the component's metadata, you can switch to code design mode to write the script.
When you switch to code design mode by clicking Edit Script, the Script component locks metadata to prevent additional changes, and then automatically generates base code from the metadata of the inputs and outputs. After the autogenerated code is complete, you will be able to enter your custom code. Your code uses the auto-generated base classes to process input rows, to access buffers and columns in the buffers, and to retrieve connection managers and variables from the package, all as strongly-typed objects.
After entering your custom code in code design mode, you can switch back to metadata design mode. This does not delete any code that you have entered; however, subsequent changes to the metadata cause the base class to be regenerated. Afterward, your component may fail validation because objects referenced by your custom code may no longer exist or may have been modified. In this case, you must fix your code manually so that it can be compiled successfully against the regenerated base class.
Configuring the Component in Metadata Design Mode
In metadata design mode, you can select input columns, and add and configure outputs and output columns, but you cannot write code. After you have configured the component's metadata, switch to code design mode to write the script.
The properties that you must configure in the custom editor depend on the usage of the Script component. The Script component can be configured as a source, a transformation, or a destination. Depending on how the component is used, it supports either an input or outputs or both. The custom code that you will write processes the input and output rows and columns.
Inputs Columns Page of the Script Transformation Editor
The Input Columns page of the Script Transformation Editor is displayed for transformations and destinations, but not for sources. On this page, you select the available input columns that you want to make available to your custom script, and specify read-only or read/write access to them.
In the code project that will be generated based on this metadata, the BufferWrapper project item contains a class for each input, and this class contains typed accessor properties for each input column selected. For example, if you select an integer CustomerID column and a string CustomerName column from an input named CustomerInput, the BufferWrapper project item will contain a CustomerInput class that derives from ScriptBuffer, and the CustomerInput class will expose an integer property named CustomerID and a string property named CustomerName. This convention makes it possible to write code with type-checking like the following:
Dim currentCustomerID as Integer = CustomerInput.CustomerID
Dim currentCustomerName as String = CustomerInput.CustomerName
For more information about how to configure input columns for a specific type of data flow component, see the appropriate example under Developing Specific Types of Script Components.
Inputs and Outputs Page of the Script Transformation Editor
The Input and Outputs page of the Script Transformation Editor is displayed for sources, transformations, and destinations. On this page, you add, remove, and configure inputs, outputs, and output columns that you want to use in your custom script, within the following limitations:
When used as a source, the Script component has no input and supports multiple outputs.
When used as a transformation, the Script component supports one input and multiple outputs.
When used as a destination, the Script component supports one input and has no outputs.
In the code project that will be generated based on this metadata, the BufferWrapper project item contains a class for each input and output. For example, if you create an output named CustomerOutput, the BufferWrapper project item will contain a CustomerOutput class that derives from ScriptBuffer, and the CustomerOutput class will contain typed accessor properties for each output column created.
You can configure output columns only on the Input and Outputs page. You can select input columns for transformations and destinations on the Input Columns page. The typed accessor properties created for you in the BufferWrapper project item will be write-only for output columns. The accessor properties for input columns will be read-only or read/write depending on the usage type that you have selected for each column on the Input Columns page.
For more information about configuring inputs and outputs for a specific type of data flow component see the appropriate example under Developing Specific Types of Script Components.
Note
Although you cannot directly configure an output as an error output in the Script component for automatic handling of error rows, you can reproduce the functionality of an error output by creating an additional output and using script to direct rows to this output when appropriate. For more information, see Simulating an Error Output for the Script Component.
ExclusionGroup and SynchronousInputID Properties of Outputs
The ExclusionGroup property has a non-zero value only in transformations with synchronous outputs, where your code performs filtering or branching and directs each row to one of the outputs that share the same non-zero ExclusionGroup value. For example, the transformation can direct rows either to the default output or to an error output. When you create additional outputs for this scenario, make sure to set the value of the SynchronousInputID property to the integer that matches the ID of the component's input.
The SynchronousInputID property has a non-zero value only in transformations with synchronous outputs. If the value of this property is zero, it means that the output is asynchronous. For a synchronous output, where rows are passed through to the selected output or outputs without adding any new rows, this property should contain the ID of the component's input.
Note
When the Script Transformation Editor creates the first output, the editor sets the SynchronousInputID property of the output to the ID of the component's input. However, when the editor creates subsequent outputs, the editor sets the SynchronousInputID properties of those outputs to zero.
If you are creating a component with synchronous outputs, each output must have its SynchronousInputID property set to the ID of the component’s input. Therefore, each output that the editor creates after the first output must have its SynchronousInputID value changed from zero to the ID of the component's input.
If you are creating a component with asynchronous outputs, each output must have its SynchronousInputID property set to zero. Therefore, the first output must have its SynchronousInputID value changed from the ID of the component’s input to zero.
For an example of directing rows to one of two synchronous outputs in the Script component, see Creating a Synchronous Transformation with the Script Component.
Object Names in Generated Script
The Script component parses the names of inputs and outputs, and parse the names of columns in the inputs and outputs, and based on these names generates classes and properties in the BufferWrapper project item. If the found names include characters that do not belong to the Unicode categories UppercaseLetter, LowercaseLetter, TitlecaseLetter, ModifierLetter, OtherLetter, or DecimalDigitLetter, the invalid characters are dropped in the generated names. For example, spaces are dropped, therefore two input columns that have the names FirstName and [First Name] are both interpreted as having the column name FirstName, with unpredictable results. To avoid this situation, the names of inputs and outputs and of input and output columns used by the Script component should contain only characters in the Unicode categories listed in this section.
Script Page of the Script Transformation Editor
On the Script page of the Script Task Editor, you assign a unique name and a description for the Script task. You can also assign values for the following properties.
Note
In SQL Server 2008 Integration Services (SSIS), all scripts are precompiled. In previous versions, you specified whether scripts were precompiled by setting a Precompile property for the task.
ValidateExternalMetadata Property
The Boolean value of the ValidateExternalMetadata property specifies whether the component should perform validation against external data sources at design time, or whether it should postpone validation until run time. By default, the value of this property is True; that is, the external metadata is validated both at design time and at run time. You may want to set the value of this property to False when an external data source is not available at design time: for example, when the package downloads the source or creates the destination only at run time.
ReadOnlyVariables and ReadWriteVariables Properties
You can enter comma-delimited lists of existing variables as the values of these properties to make the variables available for read-only or read/write access within the Script component code. Variables are accessed in code through the ReadOnlyVariables and ReadWriteVariables properties of the autogenerated base class. For more information, see Using Variables in the Script Component.
Note
Variable names are case-sensitive.
ScriptLanguage
You can select either Microsoft Visual Basic 2008 or Microsoft Visual C# 2008 as the programming language for the Script component.
Edit Script Button
The Edit Script button opens the Microsoft Visual Studio Tools for Applications (VSTA) IDE in which you write your custom script. For more information, see Coding and Debugging the Script Component.
Connection Managers Page of the Script Transformation Editor
On the Connection Managers page of the Script Transformation Editor, you add and remove connection managers that you want to use in your custom script. Normally you need to reference connection managers when you create a source or destination component.
In the code project that will be generated based on this metadata, the ComponentWrapper project item contains a Connections collection class that has a typed accessor property for each selected connection manager. Each typed accessor property has the same name as the connection manager itself and returns a reference to the connection manager as an instance of IDTSConnectionManager100. For example, if you have added a connection manager named MyADONETConnection on the Connection Managers page of the editor, you can obtain a reference to the connection manager in your script by using the following code:
Dim myADONETConnectionManager As IDTSConnectionManager100 = _
Me.Connections.MyADONETConnection
For more information, see Connecting to Data Sources in the Script Component.
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services