Creating an ODBC Destination with the Script Component


Applies To: SQL Server 2016 Preview

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.

System_CAPS_ICON_note.jpg 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.

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 AdventureWorks sample database. The example passes the first and fourth columns, the intAddressID** 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

  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 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 Connection Managers page, add or create the ODBC connection manager with a descriptive name such as MyODBCConnectionManager.

  8. On the Script page, click Edit Script, and then enter the script shown below in the ScriptMain class.

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

    using System.Data.Odbc;  
    public class ScriptMain :  
        OdbcConnection odbcConn;  
        OdbcCommand odbcCmd;  
        OdbcParameter odbcParam;  
        public override void AcquireConnections(object Transaction)  
            string connectionString;  
            connectionString = this.Connections.MyODBCConnectionManager.ConnectionString;  
            odbcConn = new OdbcConnection(connectionString);  
        public override void PreExecute()  
            odbcCmd = new OdbcCommand("INSERT INTO Person.Address2(AddressID, City) " +  
                "VALUES(?, ?)", odbcConn);  
            odbcParam = new OdbcParameter("@addressid", OdbcType.Int);  
            odbcParam = new OdbcParameter("@city", OdbcType.NVarChar, 30);  
        public override void MyAddressInput_ProcessInputRow(MyAddressInputBuffer Row)  
                odbcCmd.Parameters["@addressid"].Value = Row.AddressID;  
                odbcCmd.Parameters["@city"].Value = Row.City;  
        public override void ReleaseConnections()  

Creating a Destination with the Script Component

Community Additions