CODEWITHSUNDEEP
ssisoledbguptateamdevelopersqlbasessis-connection-manager
jeremiahgarrett
jeremiahgarrett

Reputation: 1

In SSIS how can I select SQLBASEOLEDB in the connection manager under the 'provider' dropdown?

I need to update a connection in SSIS to a Gupta SQLBase OLE DB provider but it does not show as an option in the dropdown.

I have registered the Gupta SQLBase OLE DB provider on my local machine for both 32 and 64 bit versions and I have also attempted to recreate the connection on this package and it still is not showing. What else can I try?

Upvotes: 0

Views: 236

Answers (1)

Steve Leighton
Steve Leighton

Reputation: 840

Your Gupta SQLBase OLE DB provider can be 32 or 64 bit.
Your SQL Server DTS can be 32 or 64 bit.
They need to match.

If your SQLBASEOLEDB.dll is 64 bit - you need to run the 64bit DTS,
If your SQLBASEOLEDB.dll is 32 bit - you need to run the 32bit DTS ,
or else the data source wont show.

To check which it is, open the SQLBASEOLEDB.dll in Notepad++.
Find the first occurrence of 'PE' ( exclude the quotes ).
If it is 'PE L' then it is 32bit.
If it is 'PE d+' or similar, then it is 64 bit.
You can do the same with DTSWizard.exe to check the bitness.

In a Command Prompt window, try and run the DTSWizard.exe from one of the following locations.

C:\ Program Files \Microsoft SQL Server\160\DTS\Binn for the 64-bit version.
C:\ Program Files (x86) \Microsoft SQL Server\160\DTS\Binn for the 32-bit version.

160 = SQL Server 2022.
This value depends on the version of SQL Server you have.
Change it accordingly.

Run the 64-bit version of DTS unless you know that your SQLBase data source is a 32- bit data provider.

For example, assuming your SQLBase is 64 bit, to launch the 64-bit version of DTS from the command prompt on SQL Server 2022, run the following command:

C:\ Program Files \Microsoft SQL Server\160\DTS\Binn\DTSWizard.exe

SQLBase SOURCE OLEdB provider will show as: SQLBase SOURCE OLEdB provider

Once you have created your .dtsx package, here's a real handy hint to speed things up a lot.

  1. Open the .dtsx package file using Notepad++ and edit the XML SQLBase SOURCE connection string line so it includes an OMS ( Output message size ) value and an Isolation Level of Read Only. SQLBase SOURCE connection string
  2. Edit the XML SQLServer TARGET connection string line so it includes a packet size. SQLServer TARGET connection string
  3. Ensure your SQLServer OLEdB provider is the latest generation (MSOLEDBSQL19 or MSOLEDBSQL ) not the ancient unsupported driver (SQLOLEDB) . If its not , you're using the wrong SQLServer OLEdB dll

#SQLBase #GuptaTeamDeveloper #OpenTextGupta

Upvotes: 0

Related Questions