Attunity connector for SQL Server 2019 Standard Edition

SANDEEP TADISHETTY 41

Hi,

I installed both 32-bit and 64- bit versions of Attunity Oracle connector from Microsoft website but still do not see connectors in my Visual Studio 2019. Please let me know the process since I am trying to extract data from Oracle 19c source to SQL Server 2019 and it is very slow with OLEDB Source connection from Oracle.

Versions and Editions:

OS: Windows Server 2022 Standard Edition

SQL Server: SQL Server 2019 Standard Edition (15.0.4249.2)

Visual Studio: Visual Studio Enterprise 2019 ( 16.11.18)

Oracle Source Database: Oracle 19c

Note: I have also installed Oracle 32 bit and Oracle 64 bit admin client.

https://www.microsoft.com/en-us/download/details.aspx?id=58228

Thanks

Accepted answer

ZoeHui-MSFT 37,596 Reputation points

2022-09-21T01:53:34.483+00:00

Hi @SANDEEP TADISHETTY ,

Please note that SQL Server Integration Services Projects 4. versions* are not support Oracle connector yet.

Please try to use version3.16: https://ssis.gallerycdn.vsassets.io/extensions/ssis/sqlserverintegrationservicesprojects/3.16/1645603822968/Microsoft.DataTools.IntegrationServices.exe

Don't forget to re-start VS after you installed the Microsoft Connector for Oracle.

The oracle source and oracle destination will appear in common component.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.
ZoeHui-MSFT 37,596 Reputation points

2022-09-22T05:40:44.597+00:00

Hi @SANDEEP TADISHETTY ,
Could you please have a check of the version your SSIS project?

SANDEEP TADISHETTY 41 Reputation points

2022-09-22T15:51:34.477+00:00

I see Version 4.1

Thanks

ZoeHui-MSFT 37,596 Reputation points

2022-09-23T01:17:38.41+00:00

Hi @SANDEEP TADISHETTY ,
Please note that SQL Server Integration Services Projects 4.* versions are not support Oracle connector yet.

Please try to use version3.16: https://ssis.gallerycdn.vsassets.io/extensions/ssis/sqlserverintegrationservicesprojects/3.16/1645603822968/Microsoft.DataTools.IntegrationServices.exe

SANDEEP TADISHETTY 41 Reputation points

2022-09-23T04:50:52.48+00:00

Thanks ZoeHui for your help recommending version 3.16. I tested on new SQL machine and it worked but I need to convince management to go with enterprise edition which costs a lot if it doesn't work on standard edition. I can see the Oracle source and Oracle destination in Data Flow Component which is under Common but still need to test the package running under SQL agent as a job.

OS: Windows 2019 Standard Edition

SQL: SQL Server 2019 Enterprise Edition ( Can you also confirm whether if this works or doesn't work under standard edition since I did not test V3.16 on SQL server which has standard edition and is there any alternative solution like third party Oracle connectors )

Visual Studio: Visual Studio 2019 16.11

Integration Services Project Extension: Version 3.16

Thanks,
Sandeep

ZoeHui-MSFT 37,596 Reputation points

2022-09-26T01:42:38.443+00:00

Hi @SANDEEP TADISHETTY ,
As mentioned in Microsoft Connector for Oracle V1.2:
Required operating environment : Enterprise or Developer edition of SQL Server 2019 Integration Services
You may use OLE DB provider in SSIS to connect to Oracle for standard edition, a sample here.
If you want to use third party, you may do an online research.

SANDEEP TADISHETTY 41 Reputation points

2022-09-27T18:32:08.47+00:00

I got below connection error message. I am able to connect to Oracle Source database using OLEDB Source but when I try to connect to same Oracle database using Oracle Source component got below error.

Error: there was an error trying to establish an oracle connection with the database server.

Thanks
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

Yitzhak Khabinsky 25,956 Reputation points

2022-09-20T21:17:35.63+00:00

Hi @SANDEEP TADISHETTY ,

Microsoft has very performant Microsoft Oracle Connector for SSIS.
It will work on SSIS run-time server with SQL Server 2019 Enterprise Edition only.
SQL Server 2019 with SSIS, Standard Edition will not work for such setup.
Please sign in to rate this answer.
SANDEEP TADISHETTY 41 Reputation points

2022-09-20T21:22:31.613+00:00

Thank you for quick response but I am assuming it should at least show the connector in Visual Studio so that I can change the edition from Standard to Enterprise.

Please also let me know if there are any other alternatives to make the process faster using SQL Server 2019 Standard Edition.

Thanks

Olaf Helper 45,101 Reputation points

2022-09-21T06:19:18.623+00:00

show the connector in Visual Studio so that I can change the edition from Standard to Enterprise.

???
You can not change the Edition of your SQL Server/SSIS in SSDT. The Connector is only supported by SSIS Enterprise Edition, see
Integration Services features supported by the editions of SQL Server => Integration Services - Advanced sources and destinations

You have to use the common ODBC or OleDB data provider for Oracle.

SANDEEP TADISHETTY 41 Reputation points

2022-09-21T14:54:57.967+00:00

I meant to change the SQL database connection to SQL Server/SSIS enterprise edition on a different server and install the Attunity connector on that new machine.

Can you also let me know if there is any third party company that provides for oracle connectors for faster data regardless of SQL Server/SSIS editions.

Thanks
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Attunity connector for SQL Server 2019 Standard Edition

1 additional answer

Your answer