Attempting to use 2019 for 2014 SSIS package and most work except if there is a connection to Oracle

Question

I had to switch to 2019 after my license for VS 2013 went stale so I can't edit in 2013 anymore. Our server environment is current all 2014. After a long fight, I was able to get most my packages open in 2019 EXCEPT if they have an Oracle connection.
When I have a package with an Oracle connection, the package displays a red X and open even show me any part of the package. The message is MVS is unable to load this document: Object reference is not set to an instance of an object.
There are 6 errors from log

Error Error loading 'MyPackage.dtsx' : Object reference not set to an instance of an object.. C:\Projects\SSIS\MyPackage\MyPackage\MyPackage.dtsx

Error Error loading MyPackage.dtsx: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "". C:\Projects\SSIS\MyPackage\MyPackage\MyPackage.dtsx

Error Error loading MyPackage.dtsx: The component metadata for "MyPackage_ADM_SCORES_VW for last two years and only placement" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. C:\Projects\SSIS\MyPackage\MyPackage\MyPackage.dtsx

Error Error loading MyPackage_.dtsx: Component Package_ADM_SCORES_VW for last two years and only placement, clsid {7CDF593F-DE06-4ABD-B356-7976EF7AC8E0} failed to initialize due to error 0x80131600 "". C:\Projects\SSIS\MyPackage_\MyPackage_\MyPackage_.dtsx

Error Error loading MyPackage_.dtsx: The connection type "ORACLE" specified for connection manager "CACHE" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. C:\Projects\SSIS\MyPackage\MyPackage_\MyPackage_.dtsx

Error Error loading MyPackage_.dtsx: The managed pipeline component "DTSAdapter.ORACLESource.1" could not be loaded. The exception was: Could not load type 'DTSAdapter.ORACLESource.1' from assembly 'Microsoft.SqlServer.PipelineHost, Version=15.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'.. C:\Projects\SSIS\MyPackage\MyPackage_\MyPackage_.dtsx

I"ve serched, installed attunity 5.0, reinstalled, etc to no avail and searched for an answer but have not found anyone where the page for the package doesn't open

Any help would be greatly appreciented

Answer 1

Hi @Leslie, Linda (lesliel) ,

Since SQL Server 2019 CU1, here is a new component named Microsoft Connector for Oracle in SSIS.

Microsoft Connector for Oracle enables the ability to export data from and load data into Oracle data source in an SSIS package.

The following Microsoft SQL Server products are supported by Microsoft Connector for Oracle:

Since SQL Server 2019 CU1
SQL Server Data Tools (SSDT) 15.9.3 or later for Visual Studio 2017
Microsoft SQL Server Data Tools (SSDT) for Visual Studio 2019

The following Oracle database versions of data source are supported:

Oracle 10.x
Oracle 11.x
Oracle 12c
Oracle 18c (without Windows Authentication support)
Oracle 19c (without Windows Authentication support)

You may install it for a try.

Details please refer to oracle-connector.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Answer 2

Well, I finally got VS 2017 to work and open my Oracle Package after reinstalling it again and ensuring I had the SSIS tools added.
STILL can't use VS 2019 on my packages with Oracle but at least I can open and upgrade to 2016 I suppose AND publish to my current environment of 2014

Answer 3

Adding the above does resolve some of the errors but I am still getting this error and can't open the package at all

Error Error loading 'MyPackage.dtsx' : Object reference not set to an instance of an object.. C:\Projects\SSIS\MyPackage\MyPackage\MyPackage.dtsx

This is the only error and this package works fine in VS 2013 Community Edition and I need to be able to keep it compatible with SQL Server 2014.

Do these drivers need to be install on my SSIS server as well until we can migrate to SSIS 2019?

I need to be able to update these packages to 2019 but if I can't open them in 2019, its hard to upgrade them to SSIS 2019.

Answer 4

So I can't open my 2014 package in VS 2019 IF there are Oracle drivers? How do I edit it or update it?

When I open the package to UPGRADE it, using Upgrade all, none of the packages show up in the list to apply the password to.

So 2 problems,
I can't edit my 2014 package with Oracle Connection
I can't upgrade my 2014 packages

Do I need to rewrite ALL my packages that connect to Oracle?

VERY FRUSTRATED all due to I can't access my 2013 Community Edition of VS

Answer 5

Hello

I had the same situation:

• VS 2013 SSIS solution. Packages have Attunity Oracle Connection (V3.0, for SQL Server 2014 Integration Services)
• Solution needs to be redesigned / migrated to a new SQL Server 2019. New development client has Visuals Studio 2019 installed

After the installation of this component, I could open the SSIS Packages in VS 2019 without any exception:

Microsoft Connector for Oracle V1.2

• https://www.microsoft.com/en-us/download/details.aspx?id=104113
• MicrosoftSSISOracleConnector-15.0.2000.215_amd64.msi

Please note:

• It wasn't the goal to EXECUTE these exsiting packages, or to deploy them to a SQL Server
• The only goal was to OPEN the packages in Visual Studio 2019, so that the developer can have a look whats the content.

Greetings,
Roli Kreis