CODEWITHSUNDEEP
ssisoledbetlsybaseadvantage-database-server
Tamila
Tamila

Reputation: 177

Error while extracting data from Sybase using OLE DB in SSIS

I am having problems extracting data from Sybase using Advantage 11 OLE DB Provider in SSIS (2017). I can connect to the database, see the list of tables, and, when selecting a table as a data source I can see the columns.
However, when I click Preview (or run the data flow task), I get this error:

The provider cannot derive parameter information from the command. Use SetParameterInfo. (Advantage OLE DB Provider)

I am using no parameters, my Data Access mode is set to Table or View (see attached screenshot)

enter image description here

In project properties, Run64bitRuntime is set to False.

What am I missing? Thanks!!

Upvotes: 2

Views: 1380

Answers (2)

Tamila
Tamila

Reputation: 177

So I finally solved this, but not with OLE DB. Instead, I used Advantage .NET Data Provider Release 11.1. Then in Visual Studio, I used ADO.NET connection manager, where I changed provider to Advantage Data Provider and that did the trick.

Upvotes: 0

Hadi
Hadi

Reputation: 37313

While searching on this issue, i found an old official reference that is related to advantage ole db provider release 1.0 (since 2000), they mentioned that:

Title:

"The Provider Cannot Derive Parameter Information From the Command. Use SetParameterInfo". Occurs When Using a Client Side Cursor with OLE DB

Problem Description:

"The provider cannot derive parameter information from the command. Use SetParameterInfo" occurs when setting the CursorLocation to use a client-side with the Advantage OLE DB Provider.

Solution:

This is a known issue in the 1.0 release of the Advantage OLE DB Provider, and is being looked into by the Advantage R&D team. The 1.1 release of the Advantage OLE DB Provider should have this problem fixed. Meanwhile, set the CursorLocation to use a server-side cursor as a work around. Server-side cursors work with the Advantage Database Server and the Advantage Local Server.

I searched on how to set CursorLocation to server side, maybe you have to set Advantage Server Type property in the connection string. Really i didn't worked with this type of connections but you can refer to the following articles to see some examples:

Also i found some official Microsoft articles concerning the CursorLocation property, but really i don't know where to change this property:

Update 1 - OLE DB Services

Try working around with the OLEDB Services values in the OLEDB connection manager:

enter image description here

Also try to change the OLEDB Services value manually in the connectionstring referring to the following article:

Update 2 - Other Providers

Try using other providers to connect to Sybase such as Sybase Adaptive Server Enterprise Data Provider or ODBC, there are many links that describe the whole process:

Upvotes: 1

Related Questions