Sybase ASE OLE DB Connection in SQL Server Integration Services 2005

I am building a SQL Server 2005 Integration Services package to migrate data from a Sybase Adaptive Server Enterprise 12.5.4 database to a SQL Server 2005 Database. The Sybase Database is the backend for a vendor product which our team does not have the authority to change or manage.

In my initial stages of development, to access the Sybase database, I created a SQL Server Linked Server utilizing the Microsoft OLE DB Provider for ODBC Drivers on my destination database server. With this linked server created, I created my SSIS package with a single Data Source - connecting to the MS SQL Server "Destination" Database. Next, in my SSIS package data flow tasks, I configured the OLE DB Source to access single Data Source defined in the package, using SQL commands to access the source linked server (simply using SELECT col1, col2, col3 FROM [SybaseLinkedServer].[Data开发者_Go百科base].dbo.[Table]). The OLE DB Destination within the data flow task utilizes the same connection manager as the OLE DB Source, but makes use of the Table or view - fast load Data access mode.

Later, in the project, I obtained the Sybase ASE OLE DB Provider (The v12.5.4 OLE DB Provider was no longer available, we had to purchase the v15.7 ASE SDK). Thinking that removing the Linked Server from the solution would improve performance, I modified the SSIS package to include a new OLE DB Data Source utilizing the Sybase ASE OLE DB Provider to access the Sybase Database directly, and modified the OLE DB Source to use the new ASE OLE DB Data Source with the Table or View Data access mode.

To my surprise, switching the solution from the Linked Server using the OLEDB Provider for ODBC Drivers to the direct OLEDB connection using the Sybase ASE OLEDB Provider produced significantly poorer results, far from my expectations. The results from test runs using each method are available at this hyperlink.

Has anyone experienced this type of issue and/or is this behavior to be expected? Is there a better way to configure this to extract data from Sybase ASE to MS SQL Server 2005?

---

i had the exact same issue and i ended up using linked server to connect to sybase because it was the fastest way and most reliable, i had great difficulty trying to connect to sybase using SSIS sql 2008 directly. even though i was able to connect to sybase using Sql 2000 DTSs.

but there is a very interesting driver provided by datadirect http://www.datadirect.com/products/other/ssis/index.html

i downloaded it and tried it for 30 days, and it was amazing fast and very easy its very light when comparing it with sybase drivers, i mean not much to install and it works very good with SSIS.