question

TingyuChiang-4430 avatar image
0 Votes"
TingyuChiang-4430 asked Swares answered

Oracle Connector is not working in both VS2017 and VS2019

I am planning to use VS2017 SSIS to access oracle database. Everything looks working. I see Oracle Source task (by Attunity, orange color). I can drag it to a Data Flow task. I can create a new Oracle Connector Manager with the TNS service name, username, password. I tested the connection. It says "Test connection succeeded". In the Oracle Source, I specified the SQL command, and click the "Review" button, it shows the data from Oracle database. But when I execute the data flow, it gives me the error:

[SSIS.Pipeline] Error: The component metadata for "Oracle Source, clsid {4F885D04-B578-47B7-94A0-DE9C7DA25EE2}" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.

[SSIS.Pipeline] Error: Cannot find the connection manager with ID "{8B86A6FB-6F84-45AB-ADCC-8F692341FA4C}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "Oracle Source.Connections[MSOraConnection]" in the connection manager collection of "Oracle Source". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.

[SSIS.Pipeline] Error: Oracle Source failed validation and returned error code 0xC004800B.
[SSIS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.
Error: The connection "{8B86A6FB-6F84-45AB-ADCC-8F692341FA4C}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

I also tried VS2019, with the newest Microsoft Oracle connector. The "Test Connection" in the connector manager is failed. It says - "There was an error trying to establish an Oracle connection with the database server.", but without any detail information.

sql-server-integration-services
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Seems this issue is more SSIS related ?

0 Votes 0 ·

@TingyuChiang-4430,
Let's concentrate on VS2019 and Microsoft Oracle Connector.
That connector doesn't need Oracle Client installation, and Oracle TNSes, which is a huge benefit.

Please share the following:
- Oracle connection details
- Visual Studio full version
- SQL Server Integration Services Project (VS2019 extension) version
- screen shots
- etc.

0 Votes 0 ·

For VS2019, do I need to uninstall VS2017, its older version of SSIS, and an old oracle client before installing the new VS2019? I have an old Oracle SQL Plus installed 4 years ago.



0 Votes 0 ·

Both VS2017 and VS2019 could be installed and coexist on the machine.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered ChiangDavid-7014 edited

Hi @TingyuChiang-4430 ,

1.Error: The connection "{8B86A6FB-6F84-45AB-ADCC-8F692341FA4C}" is not found.
Please verify that a connection manager in the connection manager collection, Connections, has been created with that ID.

2.I also tried VS2019, with the newest Microsoft Oracle connector. The "Test Connection" in the connector manager is failed. It says - "There was an error trying to establish an Oracle connection with the database server."
Please check if you set the TNS service name, username, password successfully.

Best Regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have verified - TNS service name, username, password are correct and it is still not working.

0 Votes 0 ·
TingyuChiang-4430 avatar image
0 Votes"
TingyuChiang-4430 answered YitzhakKhabinsky-0887 edited

I apologized that I mixed VS2017 and VS2019 problems together. I will focus on VS2017 in this thread mainly, because only the package execution failed. However, I have both VS2017 and VS2019 installed, and VS2019 is installed after VS2017 running into the problems.

The connection manager ID is found in the .dtsx file.
<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[Oracle Connector 1]"
DTS:CreationName="MSORA"
DTS:DTSID="{8B86A6FB-6F84-45AB-ADCC-8F692341FA4C}"
DTS:ObjectName="Oracle Connector 1">
<DTS:ObjectData>
<OracleXMLPackage>
<OraConnectionString>...
...

The TNS service name, username, password are correct. The "Test Connection" is run successfully. SQL "Preview" shows the data from oracle database.

Below lists the software installed under VS2017.


Microsoft Visual Studio Enterprise 2017
Version 15.9.28
VisualStudio.15.Release/15.9.28+28307.1274
Microsoft .NET Framework
Version 4.8.03761

Installed Version: Enterprise

...
Oracle Developer Tools for Visual Studio 19.3.1.0
Oracle Developer Tools for Visual Studio Copyright (c) 2005, 2020

...
SQL Server Analysis Services 14.0.1016.285
Microsoft SQL Server Analysis Services Designer
Version 14.0.1016.285

SQL Server Data Tools 15.1.62002.01090
Microsoft SQL Server Data Tools

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 14.0.3002.113

SQL Server Reporting Services 14.0.1016.285
Microsoft SQL Server Reporting Services Designers
Version 14.0.1016.285

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Your error:
Error: The connection "{8B86A6FB-6F84-45AB-ADCC-8F692341FA4C}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

Please share your entire connection string from the properties pane, just obfuscate its real values.

As I explained earlier, Microsoft Oracle Connector doesn't need Oracle Client installation, and Oracle TNSes, which is a huge benefit.

Check it out here:

General availability of Microsoft Connector for Oracle

It seems that you need to update SSDT: SQL Server Data Tools (SSDT) 15.9.3 or later for Visual Studio 2017


0 Votes 0 ·

<OraConnectionString>SERVER=myServer;USERNAME=myUser;ORACLEHOME=;ORACLEHOME64=;WINAUTH=0</OraConnectionString>
<OraRetain>False</OraRetain>
<OraInitialCatalog></OraInitialCatalog>
<OraServerName>myServer</OraServerName>
<OraUserName>myUser</OraUserName>
<OraOracleHome></OraOracleHome>
<OraOracleHome64></OraOracleHome64>
<OraWinAuthentication>False</OraWinAuthentication>
<OraEnableDetailedTracing>False</OraEnableDetailedTracing>
<OraPassword
Sensitive="1"
Encrypted="1">adfadfadfasdfadsfasdfdfadsfasdfasdfsdafasdfdsaf</OraPassword>
</OracleXMLPackage>
</DTS:ObjectData>
</DTS:ConnectionManager>

0 Votes 0 ·

<OraConnectionString>SERVER=myServer;USERNAME=myUser;ORACLEHOME=;ORACLEHOME64=;WINAUTH=0</OraConnectionString>
<OraRetain>False</OraRetain>
<OraInitialCatalog></OraInitialCatalog>
<OraServerName>myServer</OraServerName>
<OraUserName>myUser</OraUserName>
<OraOracleHome></OraOracleHome>
<OraOracleHome64></OraOracleHome64>
<OraWinAuthentication>False</OraWinAuthentication>
<OraEnableDetailedTracing>False</OraEnableDetailedTracing>
<OraPassword
Sensitive="1"
Encrypted="1">adfadfadfasdfadsfasdfdfadsfasdfasdfsdafasdfdsaf</OraPassword>
</OracleXMLPackage>
</DTS:ObjectData>
</DTS:ConnectionManager>

0 Votes 0 ·

As documented here: oracle-connection-manager
EzConnect format: [//]host[:port][/service_name]

The entire connection string (LDAP style) should be like follows:
SERVER=//server:1521/service_host;USERNAME=someUser;PASSWORD=somePassword;WINAUTH=0;


0 Votes 0 ·
TingyuChiang-4430 avatar image
0 Votes"
TingyuChiang-4430 answered YitzhakKhabinsky-0887 edited

YitzhakKhabinsky said. I need to use EzConnect format described in the oracle-connection-manager document.

However, I am using another option "tnsname.ora". The document says using the connect descriptor name defined in the tnsnames.ora file that located in the admin folder of the Oracle client. I used that before and that should work too. As I mentioned, TNS name option is working in the connection manager configuration; "Test Connection", "Preview" Oracle data are all working but not the package execution. While executing the package, it failed and the error message is

[SSIS.Pipeline] Error: The component metadata for "MyDataSource, clsid {4F885D04-B578-47B7-94A0-DE9C7DA25EE2}" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @TingyuChiang-4430 ,

[SSIS.Pipeline] Error: The component metadata for "MyDataSource, clsid {4F885D04-B578-47B7-94A0-DE9C7DA25EE2}" could not be upgraded to the newer version of the component.

Could you please share the information about the component "MyDataSource" ?

May I know if you can share the components in your ssis package?

Best Regards,
Mona

0 Votes 0 ·
             <component
               refId="Package\Sequence Container\Data Flow Task\Oracle Source"
               componentClassID="Attunity.SSISOraSrc"
               contactInfo="Oracle Source;Microsoft Connector for Oracle by Attunity; Attunity Ltd.; All Rights Reserved; http://www.attunity.com;7"
               description="Microsoft Oracle Source Component by Attunity"
               name="Oracle Source"
               usesDispositions="true"
               version="7">

In Windows Registry. it is
"Managed Component Host"
in "DTSPipeline.dll"

"DTS.ManagedComponentWrapper.5"
"DTS.ManagedComponentWrapper"



0 Votes 0 ·

Thanks for sharing.
So you are currently using Attunity Oracle connector...

I would strongly suggest to start using VS2019, Microsoft Oracle Connector.
All the details in my answer.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

@TingyuChiang-4430,

Let's concentrate on VS2019 and Microsoft Oracle Connector.
That connector doesn't need Oracle Client installation, and Oracle TNSes, which is a huge benefit.

Production server:
(1) MS SQL Server 2019 Enterprise Edition, SSIS run-time environment on the server.
(2) MS Oracle Connector.

Dev. machine:
(1) VS any edition. Visual Studio 2019 Community Edition should be fine,
(2) SQL Server Integration Services Projects for VS2019.
(3) MS Oracle Connector.

Useful links:
General availability of Microsoft Connector for Oracle
Microsoft Connector for Oracle
Download latest version of Microsoft connector for Oracle
SQL Server Integration Services Projects for VS2019


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried VS2019. I used the EzConnect format in VS2019. It has the exact same problem like VS2017 - "The PerformUpgrade method failed". The "Test Connection" and "Preview" are working like a charm.

EzConnect format: [//]host[:port][/service_name]

0 Votes 0 ·

I hope you are using VS2019 and Microsoft Oracle Connector (not Attunity).

Please share a screen shot of SSIS package, error, etc.

0 Votes 0 ·
ChiangDavid-7014 avatar image
1 Vote"
ChiangDavid-7014 answered Monalv-msft commented

It is working in VS2019 now.
I specify the TNS service name with the Oracle connection EzConnect format: [//]host[:port][/service_name].
I also change the SSIS Project Solution Configuration Properties->TargetServerVersion to "SQL Server 2019".

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The Microsoft Oracle Connector is working well in VS2019. However, the "Script Component" will be hung forever during the debug session. After setting the breakpoint, when debugger hits the breakpoint, if I accendently change the c# code, then VS2019 show "Plesae wait for an editor command to finish..." error and hung forever. "Cancel" button won't doing any better in this case.
41069-vs2019.pdf


0 Votes 0 ·
vs2019.pdf (67.0 KiB)

Hi @ChiangDavid-7014 ,

The Microsoft Oracle Connector is working well in VS2019.

It seems that you solved this issue.

Please ask a new question in this forum if you get new error.

Best Regards,
Mona

0 Votes 0 ·

So some good news on your end. 😊
As I said earlier, that setup like in my Answer in this thread, should usually work without any problem.

  • What is your VS2019 version? (VS2019 v.16.8.1 is the latest)

  • What is your SQL Server Integration Services Projects extension version? (v.3.10 is the latest)

0 Votes 0 ·
Swares avatar image
0 Votes"
Swares answered YitzhakKhabinsky-0887 commented

Hi @YitzhakKhabinsky-0887 ,

Sorry to dig this topic but I'm having the same error: Failed to deploy the project. Fix the problems and try again later.:Unable to create the type with the name 'DTS.ConnectionManagerOracle.1'.

I've noticed you outlined the Enterprise Edition in bold. Does the connector work for standard edition?

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Swares,

Unfortunately, no.
It is a must to have MS SQL Server with SSIS Enterprise Edition for Microsoft Oracle Connector to work.

In the past, I spoke directly with the SSIS PM on this very issue, asking to drop demand for the Enterprise Edition.
The PM declined my request, justifying that MS is making more $$$ on the SQL Server Enterprise Edition.

0 Votes 0 ·
Swares avatar image Swares YitzhakKhabinsky-0887 ·

Hi @YitzhakKhabinsky-0887 ,

We are under a PoC with a frozen image of a oracle database. I have currently an installed instance of SQL Server Standard which was installed by mistake as Standard, I believe. Can it be upgraded to Developer for PoC purposes? Or can Enterprise be used for PoCs without any licencing cost?

0 Votes 0 ·

@@Swares,

Please to follow the process outlined here:

https://www.brentozar.com/archive/2020/06/how-to-upgrade-sql-server-evaluation-edition-to-developer-edition/

it will show you all available options.

0 Votes 0 ·
Swares avatar image
0 Votes"
Swares answered

I undertand, the tricky thing is, this is a public organisation, so I have limited control on those servers. I cannot run the installation center to check the options :) however I believe that Standard does not allow the change to Developer, as they are fundamentally different, in the way that Standard is licensed for production use and Developer is not :( if this is the case then I would need to ask for Developer install and then backup / restore from the Standard instance. After that, then uninstall Standard.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Swares avatar image
0 Votes"
Swares answered

Hi again @YitzhakKhabinsky-0887

I tried but Standard to Developer it's not a supported upgrade path.

199850-image.png


Altough it was possible to upgrade Integration Services, it doesn't do the trick. The project doesn't deploy, the instance itself needs to be Enterprise / Developer.

199864-image.png



image.png (9.8 KiB)
image.png (42.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.