New SSIS Custom Data Flow Destination component for faster data transfers to Adaptive Server

Adaptive Server ADO.NET Data Provider distribution now includes a SQL Server Integration Services (SSIS) Custom Data Flow Destination component, which performs faster data transfer in to Adaptive Server destinations. The faster data transfers use the Adaptive Server bulk-insert protocol supported by AseBulkCopy class. This component, named SybaseAdaptiveServerAdoNetDestination, is installed along with the Adaptive Server ADO.NET Data Provider and the assembly files in: %SYBASE%\DataAccess\ADONET\SybaseAdaptiveServerAdoNetDestination.dll (32–bit systems) and %SYBASE%\DataAccess64\ADONET\SybaseAdaptiveServerAdoNetDestination.dll (64–bit systems).

1. Copy the SybaseAdaptiveServerAdoNetDestination.dll to C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents and C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents.

2. From any Microsoft SQL Server directory on your local drive, register the SybaseAdaptiveServerAdoNetDestination.dll assembly.

   If you have not run the SDK setup on this machine, see the DDEX Provider for Adaptive Server section in the Adaptive Server Enterprise ADO.NET Data Provider Users Guide to manually register the Adaptive Server ADO.NET Data Provider and the DDEX Provider.

3. Start SQL Server Business Intelligence Studio.

4. On the Toolbox tab, right-click Data Flow Destinations and select Choose Items. The Choose Toolbox Items window appears.

5. Select the SSIS Data Flow Items tab. Click Sybase Adaptive Server Enterprise ADO NET Destination, then click OK. Select Toolbox | Data Flow Destinations to see the Sybase Adaptive Server ADO NET Destination component.

6. To create an SSIS project, select File | New | Project | Integration Services Project menu. Create or drag and drop a Control Flow object from the Control Flow Items toolbox.

7. From the Data Flow Destinations and Data Flow Sources Toolbox tab, drag and drop Sybase Adaptive Server ADO NET Destination Component and ADO NET Source Component onto the Data Flow tab.

8. If a source or destination connection is not available in Connection Managers window, right-click in the Connection Managers window, and select New ADO.NET Connection. Select the already existing Data connection, or click New.

9. To create a new connection to the destination Adaptive Server, click New button in the Configure ADO.NET Connection Manager window, and then select Sybase Adaptive Server Enterprise Data Provider.

10. In the Connection Manager window, enter your connection properties.

11. To enable bulk insert, in the Additional Connection Props text box, enter:

    enablebulkload=1
    

    See AseBulkCopy in the Adaptive Server Enterprise ADO.NET Data Provider Users Guide for more details about utilizing bulk insert functionality.

12. Click OK.

13. For the ADO.NET Source in your Data Flow, setup the connection and data access mode. After you connect the data flow path from your ADO.NET Source, right-click Sybase Adaptive Server ADO NET Destination Component, and choose Show Advanced Edit.

14. From the Connection Manager tab, select ASE connection from the Connection Manager field. From the Component Properties tab, set the TableName property to the destination table name.

15. Select the Input Columns tab, and select the Name check box. This will select all the columns specified by the source table.

16. Click OK.

The connection is established. See Microsoft SSIS documentation for more information about data transfer.