Article
02/06/2012

Creating an ODBC Destination with the Script Component

In SQL Server Integration Services, you typically save data to an ODBC destination by using an ADO.NET destination and the .NET Framework Data Provider for ODBC. However, you can also create an ad hoc ODBC destination for use in a single package. To create this ad hoc ODBC destination, you use the Script component as shown in the following example.

Note

If you want to create a component that you can more easily reuse across multiple Data Flow tasks and multiple packages, consider using the code in this Script component sample as the starting point for a custom data flow component. For more information, see Developing a Custom Data Flow Component.

Example

The following example demonstrates how to create a destination component that uses an existing ODBC connection manager to save data from the data flow into a Microsoft SQL Server table.

This example is a modified version of the custom ADO.NET destination that was demonstrated in the topic, Creating a Destination with the Script Component. However, in this example, the custom ADO.NET destination has been modified to work with an ODBC connection manager and save data to an ODBC destination. These modifications also include the following changes:

You cannot call the AcquireConnection method of the ODBC connection manager from managed code, because it returns a native object. Therefore, this sample uses the connection string of the connection manager to connect to the data source directly by using the managed ODBC .NET Framework Data Provider.
The OdbcCommand expects positional parameters. The positions of the parameters are indicated by the question marks (?) in the text of the command. (In contrast, a SqlCommand expects named parameters.)

This example uses the Person.Address table in the AdventureWorks2008R2 sample database. The example passes the first and fourth columns, the int AddressID and nvarchar(30) City columns, of this table through the data flow. This same data is used in the source, transformation, and destination samples in the topic, Developing Specific Types of Script Components.

To configure this Script Component example

Create an ODBC connection manager that connects to the AdventureWorks2008R2 database.
Create a destination table by running the following Transact-SQL command in the AdventureWorks2008R2 database:
```
CREATE TABLE [Person].[Address2](
    [AddressID] [int] NOT NULL,
    [City] [nvarchar](30) NOT NULL
)
```
Add a new Script component to the Data Flow designer surface and configure it as a destination.
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.) To ensure that this sample works, the output of the upstream component must include at least the AddressID and City columns from the Person.Address table of the AdventureWorks2008R2 sample database.
Open the Script Transformation Editor. On the Input Columns page, select the AddressID and City columns.
On the Inputs and Outputs page, rename the input with a more descriptive name such as MyAddressInput.
On the Connection Managers page, add or create the ODBC connection manager with a descriptive name such as MyODBCConnectionManager.
On the Script page, click Edit Script, and then enter the script shown below in the ScriptMain class.

Close the script development environment, close the Script Transformation Editor, and then run the sample.

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

    Dim odbcConn As OdbcConnection
    Dim odbcCmd As OdbcCommand
    Dim odbcParam As OdbcParameter

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

        Dim connectionString As String
        connectionString = Me.Connections.MyODBCConnectionManager.ConnectionString
        odbcConn = New OdbcConnection(connectionString)
        odbcConn.Open()

    End Sub

    Public Overrides Sub PreExecute()

        odbcCmd = New OdbcCommand("INSERT INTO Person.Address2(AddressID, City) " & _
            "VALUES(?, ?)", odbcConn)
        odbcParam = New OdbcParameter("@addressid", OdbcType.Int)
        odbcCmd.Parameters.Add(odbcParam)
        odbcParam = New OdbcParameter("@city", OdbcType.NVarChar, 30)
        odbcCmd.Parameters.Add(odbcParam)

    End Sub

    Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)

        With odbcCmd
            .Parameters("@addressid").Value = Row.AddressID
            .Parameters("@city").Value = Row.City
            .ExecuteNonQuery()
        End With

    End Sub

    Public Overrides Sub ReleaseConnections()

        odbcConn.Close()

    End Sub

End Class

using System.Data.Odbc;
...
public class ScriptMain :
    UserComponent
{
    OdbcConnection odbcConn;
    OdbcCommand odbcCmd;
    OdbcParameter odbcParam;

    public override void AcquireConnections(object Transaction)
    {

        string connectionString;
        connectionString = this.Connections.MyODBCConnectionManager.ConnectionString;
        odbcConn = new OdbcConnection(connectionString);
        odbcConn.Open();

    }

    public override void PreExecute()
    {

        odbcCmd = new OdbcCommand("INSERT INTO Person.Address2(AddressID, City) " +
            "VALUES(?, ?)", odbcConn);
        odbcParam = new OdbcParameter("@addressid", OdbcType.Int);
        odbcCmd.Parameters.Add(odbcParam);
        odbcParam = new OdbcParameter("@city", OdbcType.NVarChar, 30);
        odbcCmd.Parameters.Add(odbcParam);

    }

    public override void MyAddressInput_ProcessInputRow(MyAddressInputBuffer Row)
    {

        {
            odbcCmd.Parameters["@addressid"].Value = Row.AddressID;
            odbcCmd.Parameters["@city"].Value = Row.City;
            odbcCmd.ExecuteNonQuery();
        }

    }

    public override void ReleaseConnections()
    {

        odbcConn.Close();

    }
}

Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.

Share via

Creating an ODBC Destination with the Script Component

Example

To configure this Script Component example

See Also

Concepts

Additional resources