A transformation component has one input.

On the Input Columns page of the Script Transformation Editor, the column list shows the available columns from the output of the upstream component in the data flow. Select the columns that you want to transform or pass through. Mark any columns that you want to transform in place as Read/Write.

For more information about the Input Columns page of the Script Transformation Editor, see Script Transformation Editor (Input Columns Page).

A transformation component supports one or more outputs.

On the Inputs and Outputs page of the Script Transformation Editor, you can see that a single output has been created, but the output has no columns. On this page of the editor, you may need or want to configure the following items.

• Create one or more additional outputs, such as a simulated error output for rows that contain unexpected values. Use the Add Output and Remove Output buttons to manage the outputs of your synchronous transformation component. All input rows are directed to all available outputs unless you indicate that you intend to redirect each row to one output or the other. You indicate that you intend to redirect rows by specifying a non-zero integer value for the ExclusionGroup property on the outputs. The specific integer value entered in ExclusionGroup to identify the outputs is not significant, but you must use the same integer consistently for the specified group of outputs.
  

  Note:
  You can also use a non-zero ExclusionGroup property value with a single output when you do not want to output all rows. However, in this case, you must explicitly call the DirectRowTo<outputbuffer> method for each row that you want to send to the output.

• Assign a more descriptive name to the input and outputs. The Script component uses these names to generate the typed accessor properties that you will use to refer to the input and outputs in your script.
  
• Leave columns as is for synchronous transformations. Typically a synchronous transformation does not add columns to the data flow. Data is modified in place in the buffer, and the buffer is passed on to the next component in the data flow. If this is the case, you do not have to add and configure output columns explicitly on the transformation's outputs. The outputs appear in the editor without any explicitly defined columns.
  
• Add new columns to simulated error outputs for row-level errors. Ordinarily multiple outputs in the same ExclusionGroup have the same set of output columns. However, if you are creating a simulated error output, you may want to add more columns to contain error information. For information about how the data flow engine processes error rows, see Using Error Outputs in a Data Flow Component. Note that in the Script component you must write your own code to fill the additional columns with appropriate error information. For more information, see Simulating an Error Output for the Script Component.
  

For more information about the Inputs and Outputs page of the Script Transformation Editor, see Script Transformation Editor (Inputs and Outputs Page).

If you want to use existing variables in your script, you can add them in the ReadOnlyVariables and ReadWriteVariables property fields on the Script page of the Script Transformation Editor.

When you add multiple variables in the property fields, separate the variable names by commas. You can also select multiple variables by clicking the ellipsis (…) button next to the ReadOnlyVariables and ReadWriteVariables property fields, and then selecting the variables in the Select variables dialog box.

For general information about how to use variables with the Script component, see Using Variables in the Script Component.

For more information about the Script page of the Script Transformation Editor, see Script Transformation Editor (Script Page).

When you open the VSTA IDE after you create and configuring a transformation component, the editable ScriptMain class appears in the code editor with a stub for the ProcessInputRow method. The ScriptMain class is where you will write your custom code, and ProcessInputRow is the most important method in a transformation component.

If you open the Project Explorer window in VSTA, you can see that the Script component has also generated read-only BufferWrapper and ComponentWrapper project items. The ScriptMain class inherits from the UserComponent class in the ComponentWrapper project item.

At run time, the data flow engine invokes the ProcessInput method in the UserComponent class, which overrides the ProcessInput method of the ScriptComponent parent class. The ProcessInput method in turn loops through the rows in the input buffer and calls the ProcessInputRow method one time for each row.

A transformation component with synchronous outputs is the simplest of all data flow components to write. For example, the single-output example shown later in this topic consists of the following custom code:

    Row.City = UCase(Row.City)

    Row.City = (Row.City).ToUpper();

To finish creating a custom synchronous transformation component, you use the overridden ProcessInputRow method to transform the data in each row of the input buffer. The data flow engine passes this buffer, when full, to the next component in the data flow.

Depending on your requirements, you may also want to write script in the PreExecute and PostExecute methods, available in the ScriptMain class, to perform preliminary or final processing.

Directing input rows to one of two or more possible outputs does not require much more custom code than the single-output scenario discussed earlier. For example, the two-output example shown later in this topic consists of the following custom code:

 [Visual Basic]
    Row.City = UCase(Row.City)
    If Row.City = "REDMOND" Then
        Row.DirectRowToMyRedmondAddresses()
    Else
        Row.DirectRowToMyOtherAddresses()
    End If

 [C#]
        Row.City = (Row.City).ToUpper();

        if (Row.City=="REDMOND")
        {
            Row.DirectRowToMyRedmondAddresses();
        }
        else
        {
            Row.DirectRowToMyOtherAddresses();
        }

In this example, the Script component generates the DirectRowTo<OutputBufferX> methods for you, based on the names of the outputs that you configured. You can use similar code to direct error rows to a simulated error output.

This example demonstrates a synchronous transformation component with a single output. This transformation passes through the AddressID column and converts the City column to uppercase.

If you want to run this sample code, you must configure the package and the component as follows:

1. Add a new Script component to the Data Flow designer surface and configure it as a transformation.
   
2. Connect the output of a source or of another transformation to the new transformation component in SSIS Designer. This output should provide data from the Person.Address table of the AdventureWorks sample database that contains the AddressID and City columns.
   
3. Open the Script Transformation Editor. On the Input Columns page, select the AddressID and City columns. Mark the City column as Read/Write.
   
4. On the Inputs and Outputs page, rename the input and output with more descriptive names, such as MyAddressInput and MyAddressOutput. Notice that the SynchronousInputID of the output corresponds to the ID of the input. Therefore you do not have to add and configure output columns.
   
5. On the Script page, click Edit Script and enter the script that follows. Then close the script development environment and the Script Transformation Editor.
   
6. Create and configure a destination component that expects the AddressID and City columns, such as a SQL Server destination, or the sample destination component demonstrated in Creating a Destination with the Script Component. Then connect the output of the transformation to the destination component. You can create a destination table by running the following Transact-SQL command in the AdventureWorks database:
   
   CREATE TABLE [Person].[Address2](
       [AddressID] [int] NOT NULL,
       [City] [nvarchar](30) NOT NULL
   )

7. Run the sample.
   

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)

        Row.City = UCase(Row.City)

    End Sub

End Class

 [C#]
public class ScriptMain:
    UserComponent

{
    public override void MyAddressInput_ProcessInputRow(MyAddressInputBuffer Row)
    {

        Row.City = (Row.City).ToUpper();

    }

}

This example demonstrates a synchronous transformation component with two outputs. This transformation passes through the AddressID column and converts the City column to uppercase. If the city name is Redmond, it directs the row to one output; it directs all other rows to another output.

If you want to run this sample code, you must configure the package and the component as follows:

1. Add a new Script component to the Data Flow designer surface and configure it as a transformation.
   
2. Connect the output of a source or of another transformation to the new transformation component in SSIS Designer. This output should provide data from the Person.Address table of the AdventureWorks sample database that contains at least the AddressID and City columns.
   
3. Open the Script Transformation Editor. On the Input Columns page, select the AddressID and City columns. Mark the City column as Read/Write.
   
4. On the Inputs and Outputs page, create a second output. After you add the new output, make sure that you set its SynchronousInputID to the ID of the input. This property is already set on the first output, which is created by default. For each output, set the ExclusionGroup property to the same non-zero value to indicate that you will split the input rows between two mutually exclusive outputs. You do not have to add any output columns to the outputs.
   
5. Rename the input and outputs with more descriptive names, such as MyAddressInput, MyRedmondAddresses, and MyOtherAddresses.
   
6. On the Script page, click Edit Script and enter the script that follows. Then close the script development environment and the Script Transformation Editor.
   
7. Create and configure two destination components that expect the AddressID and City columns, such as a SQL Server destination, a Flat File destination, or the sample destination component demonstrated in Creating a Destination with the Script Component. Then connect each of the outputs of the transformation to one of the destination components. You can create destination tables by running a Transact-SQL command similar to the following (with unique table names) in the AdventureWorks database:
   
   CREATE TABLE [Person].[Address2](
       [AddressID] [int] NOT NULL,
       [City] [nvarchar](30) NOT NULL

8. Run the sample.
   

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)

        Row.City = UCase(Row.City)

        If Row.City = "REDMOND" Then
            Row.DirectRowToMyRedmondAddresses()
        Else
            Row.DirectRowToMyOtherAddresses()
        End If

    End Sub

End Class

public class ScriptMain:
    UserComponent

public override void MyAddressInput_ProcessInputRow(MyAddressInputBuffer Row)
    {

        Row.City = (Row.City).ToUpper();

        if (Row.City == "REDMOND")
        {
            Row.DirectRowToMyRedmondAddresses();
        }
        else
        {
            Row.DirectRowToMyOtherAddresses();
        }

    }
}