Creating a Destination with the Script Component

You use a destination component in the data flow of an Integration Services package to save data received from upstream sources and transformations to a data source. Ordinarily the destination component connects to the data source through an existing connection manager.

For an overview of the Script component, see Extending the Data Flow with the Script Component.

The Script component and the infrastructure code that it generates for you simplify significantly the process of developing a custom data flow component. However, to understand how the Script component works, you may find it useful to read through the steps for developing a custom data flow components in the Developing a Custom Data Flow Component section, and especially Developing a Custom Destination Component.

Getting Started with a Destination Component

When you add a Script component to the Data Flow tab of SSIS Designer, the Select Script Component Type dialog box opens and prompts you to select a Source, Destination, or Transformation script. In this dialog box, select Destination.

Next, connect the output of a transformation to the destination component in SSIS Designer. For testing, you can connect a source directly to a destination without any transformations.

Configuring a Destination Component in Metadata-Design Mode

After you select the option to create a destination component, you configure the component by using the Script Transformation Editor. For more information, see Configuring the Script Component in the Script Component Editor.

To select the script language that the Script destination will use, you set the ScriptLanguage property on the Script page of the Script Transformation Editor dialog box.

A data flow destination component has one input and no outputs. Configuring the input for the component is one of the steps that you must complete in metadata design mode, by using the Script Transformation Editor, before you write your custom script.

Adding Connection Managers

Ordinarily a destination component uses an existing connection manager to connect to the data source to which it saves data from the data flow. On the Connection Managers page of the Script Transformation Editor, click Add to add the appropriate connection manager.

However, a connection manager is just a convenient unit that encapsulates and stores the information that is required to connect to a data source of a particular type. You must write your own custom code to load or save your data, and possibly to open and close the connection to the data source.

For general information about how to use connection managers with the Script component, see Connecting to Data Sources in the Script Component.

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

Configuring Inputs and Input Columns

A destination component has one input and no outputs.

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

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

The Inputs and Outputs page of the Script Transformation Editor shows a single input, which you can rename. You will refer to the input by its name in your script by using the accessor property created in the auto-generated code.

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

Adding Variables

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

Scripting a Destination Component in Code-Design Mode

After you have configured the metadata for your component, you can write your custom script. In the Script Transformation Editor, on the Script page, click Edit Script to open the Microsoft Visual Studio Tools for Applications (VSTA) IDE where you can add your custom script. The scripting language that you use depends on whether you selected Microsoft Visual Basic or Microsoft Visual C# as the script language for the ScriptLanguage property on the Script page.

For important information that applies to all kinds of components created by using the Script component, see Coding and Debugging the Script Component.

Understanding the Auto-generated Code

When you open the VSTA IDE after you create and configuring a destination 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 destination 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 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.

Writing Your Custom Code

To finish creating a custom destination component, you may want to write script in the following methods available in the ScriptMain class.

1. Override the AcquireConnections method to connect to the external data source. Extract the connection object, or the required connection information, from the connection manager.

2. Override the PreExecute method to prepare to save the data. For example, you may want to create and configure a SqlCommand and its parameters in this method.

3. Use the overridden ProcessInputRow method to copy each input row to the external data source. For example, for a SQL Server destination, you can copy the column values into the parameters of a SqlCommand and execute the command one time for each row. For a flat file destination, you can write the values for each column to a StreamWriter, separating the values by the column delimiter.

4. Override the PostExecute method to disconnect from the external data source, if required, and to perform any other required cleanup.

Examples

The examples that follow demonstrate code that is required in the ScriptMain class to create a destination component.

ADO.NET Destination Example

This example demonstrates a destination component that uses an existing ADO.NET connection manager to save data from the data flow into a SQL Server table.

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

1. Create an ADO.NET connection manager that uses the SqlClient provider to connect to the AdventureWorks database.

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

3. Add a new Script component to the Data Flow designer surface and configure it as a destination.

4. Connect the output of an upstream source or transformation to the destination component in SSIS Designer. (You can connect a source directly to a destination without any transformations.) This output should provide data from the Person.Address table of the AdventureWorks sample database that contains at least the AddressID and City columns.

5. Open the Script Transformation Editor. On the Input Columns page, select the AddressID and City input columns.

6. On the Inputs and Outputs page, rename the input with a more descriptive name such as MyAddressInput.

7. On the Connection Managers page, add or create the ADO.NET connection manager with a name such as MyADONETConnectionManager.

8. On the Script page, click Edit Script and enter the script that follows. Then close the script development environment.

9. Close the Script Transformation Editor and run the sample.

Imports System.Data.SqlClient
...
Public Class ScriptMain
    Inherits UserComponent

    Dim connMgr As IDTSConnectionManager100
    Dim sqlConn As SqlConnection
    Dim sqlCmd As SqlCommand
    Dim sqlParam As SqlParameter

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

        connMgr = Me.Connections.MyADONETConnectionManager
        sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

    End Sub

    Public Overrides Sub PreExecute()

        sqlCmd = New SqlCommand("INSERT INTO Person.Address2(AddressID, City) " & _
            "VALUES(@addressid, @city)", sqlConn)
        sqlParam = New SqlParameter("@addressid", SqlDbType.Int)
        sqlCmd.Parameters.Add(sqlParam)
        sqlParam = New SqlParameter("@city", SqlDbType.NVarChar, 30)
        sqlCmd.Parameters.Add(sqlParam)

    End Sub

    Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)
        With sqlCmd
            .Parameters("@addressid").Value = Row.AddressID
            .Parameters("@city").Value = Row.City
            .ExecuteNonQuery()
        End With
    End Sub

    Public Overrides Sub ReleaseConnections()

        connMgr.ReleaseConnection(sqlConn)

    End Sub

End Class

using System.Data.SqlClient;
public class ScriptMain:
    UserComponent

{
    IDTSConnectionManager100 connMgr;
    SqlConnection sqlConn;
    SqlCommand sqlCmd;
    SqlParameter sqlParam;

    public override void AcquireConnections(object Transaction)
    {

        connMgr = this.Connections.MyADONETConnectionManager;
        sqlConn = (SqlConnection)connMgr.AcquireConnection(null);

    }

    public override void PreExecute()
    {

        sqlCmd = new SqlCommand("INSERT INTO Person.Address2(AddressID, City) " +
            "VALUES(@addressid, @city)", sqlConn);
        sqlParam = new SqlParameter("@addressid", SqlDbType.Int);
        sqlCmd.Parameters.Add(sqlParam);
        sqlParam = new SqlParameter("@city", SqlDbType.NVarChar, 30);
        sqlCmd.Parameters.Add(sqlParam);

    }

    public override void MyAddressInput_ProcessInputRow(MyAddressInputBuffer Row)
    {
        {
            sqlCmd.Parameters["@addressid"].Value = Row.AddressID;
            sqlCmd.Parameters["@city"].Value = Row.City;
            sqlCmd.ExecuteNonQuery();
        }
    }

    public override void ReleaseConnections()
    {

        connMgr.ReleaseConnection(sqlConn);

    }

}

Flat File Destination Example

This example demonstrates a destination component that uses an existing Flat File connection manager to save data from the data flow to a flat file.

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

1. Create a Flat File connection manager that connects to a destination file. The file does not have to exist; the destination component will create it. Configure the destination file as a comma-delimited file that contains the AddressID and City columns.

2. Add a new Script component to the Data Flow designer surface and configure it as a destination.

3. Connect the output of an upstream source or transformation to the destination component in SSIS Designer. (You can connect a source directly to a destination without any transformations.) This output should provide data from the Person.Address table of the AdventureWorks sample database, and should contain at least the AddressID and City columns.

4. Open the Script Transformation Editor. On the Input Columns page, select the AddressID and City columns.

5. On the Inputs and Outputs page, rename the input with a more descriptive name, such as MyAddressInput.

6. On the Connection Managers page, add or create the Flat File connection manager with a descriptive name such as MyFlatFileDestConnectionManager.

7. On the Script page, click Edit Script and enter the script that follows. Then close the script development environment.

8. Close the Script Transformation Editor and run the sample.

Imports System.IO
...
Public Class ScriptMain
    Inherits UserComponent

    Dim copiedAddressFile As String
    Private textWriter As StreamWriter
    Private columnDelimiter As String = ","

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

        Dim connMgr As IDTSConnectionManager100 = _
            Me.Connections.MyFlatFileDestConnectionManager
        copiedAddressFile = CType(connMgr.AcquireConnection(Nothing), String)

    End Sub

    Public Overrides Sub PreExecute()

        textWriter = New StreamWriter(copiedAddressFile, False)

    End Sub

    Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)

        With textWriter
            If Not Row.AddressID_IsNull Then
                .Write(Row.AddressID)
            End If
            .Write(columnDelimiter)
            If Not Row.City_IsNull Then
                .Write(Row.City)
            End If
            .WriteLine()
        End With

    End Sub

    Public Overrides Sub PostExecute()

        textWriter.Close()

    End Sub

End Class

using System.IO;
public class ScriptMain:
    UserComponent

{
    string copiedAddressFile;
    private StreamWriter textWriter;
    private string columnDelimiter = ",";

    public override void AcquireConnections(object Transaction)
    {

        IDTSConnectionManager100 connMgr = this.Connections.MyFlatFileDestConnectionManager;
        copiedAddressFile = (string) connMgr.AcquireConnection(null);

    }

    public override void PreExecute()
    {

        textWriter = new StreamWriter(copiedAddressFile, false);

    }

    public override void MyAddressInput_ProcessInputRow(MyAddressInputBuffer Row)
    {

        {
            if (!Row.AddressID_IsNull)
            {
                textWriter.Write(Row.AddressID);
            }
            textWriter.Write(columnDelimiter);
            if (!Row.City_IsNull)
            {
                textWriter.Write(Row.City);
            }
            textWriter.WriteLine();
        }

    }

    public override void PostExecute()
    {

        textWriter.Close();

    }

}

See Also

Concepts

Creating a Source with the Script Component

Developing a Custom Destination Component