This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(224.00000000000003, 2%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETC%3C/text%3E%3C/svg%3E)
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 9%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELY%3C/text%3E%3C/svg%3E)
Seems this issue is more SSIS related ?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
@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:
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.
Both VS2017 and VS2019 could be installed and coexist on the machine.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 45%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECD%3C/text%3E%3C/svg%3E)
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".
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
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(294.40000000000003, 37%, 38%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
Hi @Chiang,David ,
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
Hi @Tingyu Chiang ,
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.
I have verified - TNS service name, username, password are correct and it is still not working.
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
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
<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>
<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>
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;
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.
Hi @Tingyu Chiang ,
[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
<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"
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.
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
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]
I hope you are using VS2019 and Microsoft Oracle Connector (not Attunity).
Please share a screen shot of SSIS package, error, etc.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(284.8, 50%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EST%3C/text%3E%3C/svg%3E)
Hi,
I am also having the same connection issue. Please let me know if I am missing anything.
Thanks