Creating an ODBC Destination with the Script Component

SQL Server 2005 Integration Services (SSIS) does not provide a built-in component that can save data to an ODBC destination. However, you can easily create an ODBC destination by using the Script component. This topic uses the custom ADO.NET Destination that was demonstrated in Creating a Destination with the Script Component and modifies it to work with an ODBC connection manager to save data to an ODBC destination.

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

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

For more information about configuring the Script component for use as a destination in the data flow, see Creating a Destination with the Script Component.

Note

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

Note the following features that distinguish this code sample from the ADO.NET Destination sample demonstrated in Creating a Destination with the Script Component.

• 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, the SqlCommand expects named parameters.

To configure this Script Component example

1. Create an ODBC connection manager that connects 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.) 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 AdventureWorks sample database.

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

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

7. On the Script page, click Design Script and enter the script that follows in the ScriptMain class. Then close the script development environment.

8. On the Connection Managers page, add or create the ODBC connection manager with a descriptive name such as MyODBCConnectionManager.

9. Then close the Script Transformation Editor and 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

Change History

Release	History
15 September 2007	Changed content: Updated the code example to include the input name referenced in Step 6. Rewrote steps and added new steps to make the procedure clearer.

See Also

Concepts

Creating a Destination with the Script Component

Help and Information

Getting SQL Server 2005 Assistance