Oracle Connector is not working in both VS2017 and VS2019

Tingyu Chiang 1

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.

Lukas Yu -MSFT 5,816 Reputation points

2020-11-09T02:34:00.747+00:00

Seems this issue is more SSIS related ?
Yitzhak Khabinsky 25,106 Reputation points

2020-11-09T03:04:37.75+00:00
@Tingyu Chiang ,
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.
Tingyu Chiang 1 Reputation point

2020-11-09T14:46:53.053+00:00

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.
Yitzhak Khabinsky 25,106 Reputation points

2020-11-10T03:12:12.51+00:00

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

9 answers

Pedro Soares 396 Reputation points

2022-05-03T16:53:50.67+00:00

Hi @Yitzhak Khabinsky ,

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?
Please sign in to rate this answer.
Yitzhak Khabinsky 25,106 Reputation points

2022-05-03T18:03:15.28+00:00

@Pedro Soares ,

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.

Pedro Soares 396 Reputation points

2022-05-03T18:06:15.363+00:00

Hi @Yitzhak Khabinsky ,

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?

Yitzhak Khabinsky 25,106 Reputation points

2022-05-03T18:42:45.873+00:00

@@Pedro Soares ,

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.
Sign in to comment
Pedro Soares 396 Reputation points

2022-05-03T18:52:07.83+00:00

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Pedro Soares 396 Reputation points

2022-05-07T11:12:29.137+00:00

Hi again @Yitzhak Khabinsky

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

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Chiang,David 6 Reputation points

2022-09-27T19:11:36.87+00:00

In your Solution Explorer, right click the .dtsx file, then select "View Code". After that, you will see xml like source code of your dtsx file. Find your Oracle Connector definition, and find the <OraConnectionString> tab. It has the connection string you specified. You can ask your Oracle DBA to see if it is correctly specified. Please be careful, don't change this dtsx file. If you changed it directly in this text file, then you have great chance to corrupt the whole dtsx file.
Please sign in to rate this answer.
SANDEEP TADISHETTY 41 Reputation points

2022-09-27T19:33:25.233+00:00

Hi David,

I do not see the OraconnectionString in View Code.

Thanks

SANDEEP TADISHETTY 41 Reputation points

2022-09-27T19:36:02.34+00:00

I used the same TNS connection string to connect Oracle database using an connection string expression in OLEDB source which is working perfectly fine but it is not working with Oracle SOurce component. Not sure what I am missing.

Data extract from OLEDB Source is very slow and that's why I want to use Oracle Source so that data extracts faster.

Thanks

Chiang,David 6 Reputation points

2022-09-27T20:05:36.407+00:00

If is possible, you haven't saved your configuration editor. Try to click ok without "Test Connection".

In your previous screen capture, your connection object name is "Oracle Connection Manager". Therefore, you should have the entry similar to below:

<DTS:ConnectionManager DTS:refId="Package.ConnectionManagers[Oracle Connection Manager]" DTS:CreationName="ORACLE" DTS:DTSID="{xxxxxxxxxxxxx}" DTS:ObjectName="Oracle Connection Manager"> <DTS:ObjectData> <DTS:ConnectionManager>

Your connect string should be inside that connection manager under <ObjectData>.

I just verified, OLEDB connect string is different. It has "Provider", "Data Source", etc. The one used in MS Oracle is different. See this link:
https://learn.microsoft.com/en-us/sql/ado/guide/appendixes/microsoft-ole-db-provider-for-oracle?view=sql-server-ver16

Here is a link to talk about SSIS Oracle Connection Manager.
https://learn.microsoft.com/en-us/answers/questions/548716/issue-with-ssis-oracle-connection-manager.html

At the end of the discussion, you will see the correct syntax of the connection string.

SANDEEP TADISHETTY 41 Reputation points

2022-09-27T20:28:04.587+00:00

Here is my code. Please let me know if my syntax is wrong and correct me.

<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[Oracle Connection Manager]"
DTS:CreationName="ORACLE"
DTS:DTSID="{7F1DE101-B68E-46B2-B200-D4935883EEDF}"
DTS:ObjectName="Oracle Connection Manager">
<DTS:ObjectData>
<DTS:ConnectionManager>
<OraConnectionString>SERVER=//XXX.XX.XX.XX:1521/CSS;USERNAME=XYZ;WINAUTH=0;</OraConnectionString>
<OraPassword
Sensitive="1"></OraPassword>
<OraRetain>False</OraRetain>
<OraInitialCatalog></OraInitialCatalog>
<OraServerName>///XXX.XX.XX.XX:1521/CSS</OraServerName>
<OraUserName>XYZ</OraUserName>
<OraOracleHome></OraOracleHome>
<OraOracleHome64></OraOracleHome64>
<OraWinAuthentication>False</OraWinAuthentication>
<OraEnableDetailedTracing>False</OraEnableDetailedTracing>
</DTS:ConnectionManager>
</DTS:ObjectData>
</DTS:ConnectionManager>

Thanks

SANDEEP TADISHETTY 41 Reputation points

2022-09-27T20:34:44.253+00:00

I also see error when I open the connection in SOurce.

Chiang,David 6 Reputation points

2022-09-27T21:07:49.387+00:00

Don't pay attention to that error message. It doesn't make sense.

In the screen capture above, click "New", then specify the correct "TNS service name". In your example, it should be like this

//XXX.XX.XX.XX:1521/CSS

The "CSS" is the service name defined in your whatever ora file. DON'T SPECIFY EXTRA ";" AT THE END.

And then, "Use Windows Authentication" is unselected, and specify your username, password.

If you are not sure about your service name, ORACLE DBA can verify that for you.

SANDEEP TADISHETTY 41 Reputation points

2022-09-27T23:57:57.743+00:00

I clicked on New but still got the error and I also did check with my Oracle admin and confirmed connection string has no issues.

TNS Name: //XXX.XX.XX.XX:1521/CSS
UserName = XYZ
Password = abc

Can you please also review if the view code is correct:

<DTS:ConnectionManager
DTS:refId="Package.ConnectionManagers[Oracle Connection Manager]"
DTS:CreationName="ORACLE"
DTS:DTSID="{7F1DE101-B68E-46B2-B200-D4935883EEDF}"
DTS:ObjectName="Oracle Connection Manager">
<DTS:ObjectData>
<DTS:ConnectionManager>
<OraConnectionString>SERVER=//XXX.XX.XX.XX:1521/CSS;USERNAME=XYZ;WINAUTH=0;</OraConnectionString>
<OraPassword
Sensitive="1"></OraPassword>
<OraRetain>False</OraRetain>
<OraInitialCatalog></OraInitialCatalog>
<OraServerName>///XXX.XX.XX.XX:1521/CSS</OraServerName>
<OraUserName>XYZ</OraUserName>
<OraOracleHome></OraOracleHome>
<OraOracleHome64></OraOracleHome64>
<OraWinAuthentication>False</OraWinAuthentication>
<OraEnableDetailedTracing>False</OraEnableDetailedTracing>
</DTS:ConnectionManager>
</DTS:ObjectData>
</DTS:ConnectionManager>

Thanks

Chiang,David 6 Reputation points

2022-09-28T13:21:47.067+00:00

The keywords used inside your connection string are different, but it should work.

A few months ago, I searched this problem. I recall one said this combination will work.

Latest upgraded VS2019.
SQL Server Integration Services project Version 3.15 (not the latest)
Latest Microsoft SSIS Oracle Connection version 15

Otherwise, I cannot help you at all.

SANDEEP TADISHETTY 41 Reputation points

2022-09-28T15:53:24.457+00:00

Thank you David and I really appreciate your help.

I was testing whole night and finally it worked in one scenario but not in another scenario and not it would be great if any one can help with this issue.

Scenario - 1: Oracle source database is outside the domain and using wallet connection for security reasons and TNS in this scenario is using TCPS protocol which is not working and got database connection error.

CSS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = XXX.XX.XX.XX)(PORT = 50000))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CSS)
)
)

TNS Name: //XXX.XX.XX.XX:50000/CSS
UserName = XYZ
Password = abc

Note: I tried to research whether if Oracle connection manager works for TCPS protocol but couldn't find any article and if not then I have to drop the whole process and go with any other alternative.

Scenario - 2: Oracle source database is with in our same domain and connected to the database with no issues and here TNS is using TCP protocol.

XYZ=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = YYY.YY.YY.YY)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = XYZ
)
)

TNS Name: //YYY.YY.YY.YY:1521/XYZ
UserName = XYZ
Password = abc

Thanks

Chiang,David 6 Reputation points

2022-09-28T15:58:05.78+00:00

Have you tried this?

https://learn.microsoft.com/en-us/answers/questions/1026079/oracle-connector-with-tcps-not-working-in-vs-2019.html

SANDEEP TADISHETTY 41 Reputation points

2022-09-28T16:01:50.46+00:00

I posted that question in separate thread yesterday and working on it. I will keep you posted again so that it might be helpful for any one in future.

Thanks

Chiang,David 6 Reputation points

2022-09-28T16:06:20.35+00:00

I didn't realized that is your thread, my apology...
Sign in to comment