General availability of Microsoft Connector for Oracle

Published 05-24-2020 05:08 PM 15.2K Views
Microsoft

Microsoft Connector for Oracle is now generally available. 

 

Microsoft Connector for Oracle provides compatibility with Attunity Oracle Connector in functionality and performance. Packages designed with Attunity Oracle Connector can run with Microsoft Connector for Oracle without any changes. 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
  • SQL Server Data Tools (SSDT) for Visual Studio 2019

 

To install the connector for Oracle database, download and run the installer from the latest version of Microsoft connector for Oracle. Then follow the directions in the installation wizard. 

 

Design new SSIS packages

Open SSDT 15.9.3 or later, Oracle components can be found in SSIS Toolbox, under Common Category.

1.png

 

And in Connection Managers, Oracle connection manager is displayed with type “ORACLE”.

2.png

Microsoft Connector for Oracle provides similar user experience with Attunity Oracle Connector. User can design new packages based on previous experience.

 

Execute packages targeting SQL Server 2019

Existing SSIS packages which have been designed with Attunity Oracle Connector will be automatically upgraded to use Microsoft Connector for Oracle. The icons will be changed as well.

 

Execute package targeting Pre-SQL Server 2019

If the project is targeting Pre-SQL Server 2019, corresponding Attunity Oracle Connector and Oracle client are required to be installed on the SSDT machine for package execution.

 

Attunity Oracle Connector download links:

 

Please refer to this page for more detail usage.

35 Comments
Senior Member

What about Azure SQL DB? Or Azure Synapse (formerly known as...)?

Occasional Visitor

For Synapse you'd be able to use Polybase for Oracle connection. 

Occasional Visitor

Does this setup only work with SSIS on SQL Server 2019, or can we use the updated SSDT with the Attunity connectors if SSIS is running on a previous version (in my case SSIS on SQL Server 2012). I haven't been able to update my SSDT since v15.8.0 because of the Attunity connectors.

Microsoft

@EMorgoch If the project is targeting Pre-SQL Server 2019, works for Attunity connectors as before

Microsoft

@EdPearson nothing changes on connectivity to Azure SQL DB - OLEDB connection manager or ADO.NET connection manager, Synapse (previous Azure SQL DW) - Azure SQL DW Upload Task

Senior Member

Hei

 

I'm trying to upload some data from oracle to SQL Server using this connector

the varchar2 sometimes is being mapped with DT_WSTR and sometimes with DT_STR

 

in oracle side we are trying to get data from a a view and from a table

on the view datatype of one of the columns is VARCHAR2(6 BYTE) and it is mapped with DT_WSTR which requires nvarchar on sql server
on the table the data type of a coulmin is VARCHAR2(6 BYTE) and it is mapped with DT_STR which requires varchar on sql server

 

the same connection from the same PC is used in both cases

 

is there any documentation on how this mappings are done?
or any indication on what should we look at to decide whether to use varchar or nvarchar on sql server

I'm trying to automate the process and I can't find any reason why one or the other datatype mapping is picked

 

 

Senior Member

Issue when trying to use a view as source

Views are not listed under Oracle source "Name of the table or the view:"

 

but the package works fine if you use the SQL command and do a select * from view or when view name is set to property [Oracle Source].[TableName]

 

Microsoft

Hi @lb1l4l1,

 

It's a current limitation that you cannot select view under the "Name of the table or the view:". We have plan to fix it. Currently you can use SQL command or go to Advanced Editor =>Component Properties and set the view name in 'TableName' field directly as the workarounds.

 

For the varchar2 issue, I confirmed that the VARCHAR2 type in Oracle will always be mapped to DT_STR in SSIS. Can you go to the Advanced Editor=>Input and Output Properties tab => External Columns and check the data type there? The data type in 'External Columns' is what SSIS maps the column to. You can change the data type in 'Output Columns' but not in 'External Columns'.

Also please make sure you installed latest Oracle Connector.

 

Senior Member

@Junxiao_Ma, because VARCHAR2 in Oracle can hold unicode characters, why wouldn't it be mapped to DT_WSTR?  

Occasional Visitor

Hello,

 

I'm using Oracle destination adapter.  I'm getting the error below while trying to select the destination table in the "Name of the table or the View" drop down list box.  I already created many data flow tasks using the same Oracle connection without any problems.  It has always allowed me to select an Oracle destination table. 

 

I'm using the following:

  • VS 2019 Version 16.7.4
  • SQL Server Integration Services Project 3.10
  • Microsoft Connector for Oracle V1.0  Version 15.0.2000.110 date published 5/13/2020

 

qui1que_1-1600882730554.png

 

 

Microsoft

Hi @qui1que ,

This error usually indicates the connection issue. Did you successfully select the table in other Oracle Destination using the connection manager? Or every Oracle Destination has issue? You can try to use 'Advanced Editor' and directly input the table name in the 'Component Properties' tab. It will work if there's no connection issue.

Senior Member

I extract data from 200+ tables from an Oracle database into SQL server and I am running into connection errors while the packages are running.  This was not experienced with using Attunity 5.0.  The are 6 packages running in parallel pulling data.

  • Found that moving from a project connection to a package connection helped considerably although connection issues were still occuring.
  • Found that restricting the number of active package running in parallel (and therefore data flows) reduced the number of issues (48 core server).  However, reducing the number of parallel tasks extended the runtime out of the acceptable time range.
  • Most of the dataflows in the same failed package will complete without error. 
  • The data flow that fails seems to random.

This is the error returned from the data flow on failure.  

Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "Connection OracleSource" failed with error code 0xC0014009.

Visitor

Is the Oracle connector in all SQL 2019 Editions available or is it an enterprise feature?

Microsoft

Hi @Markus_Krenn , it's an enterprise feature.

Visitor

I tried to deploy the SSIS Package with the Oracle Connector to Developer Edition (SQL Server 2019 RTM-CU8), but deployment failed with error (Failed to deploy project. For more information, query the operation_messages view for the operation identifier '2444'. (Microsoft SQL Server, Error: 27203). Without Oracle Connection deployment works, also Debugging in Visual Studio works.

Do you have any ideas to solve this? Comes the problem from Developer Edition / do I have to use Enterprise also during development?

Thanks, Markus

Microsoft

Hi @Markus_Krenn, the developer edition is supported. Can you run the query 'select * from catalog.operation_messages where operation_id = 2444' against SSISDB to see the detail about the failure?

Visitor

Thanks @Junxiao_Ma

The message is: Failed to deploy the project. Fix the problems and try again later.:Unable to create the type with the name 'DTS.ConnectionManagerOracle.1'.

Microsoft

@Markus_Krenn , which version of SSMS do you use for the deployment? You need SSMS18.0 and upper with Oracle Connector installed or you can use your Visual Studio to deploy it (right click the project and click deploy button).

Visitor

Thanks @Junxiao_Ma

I am using Visual Studio Professional 2019, 16.7.6.

Senior Member

hi,

I'm experiencing issues connecting with one of our Oracle sources

Visual studio is crashing when I try to test the connection. (This is happening in multiple PCs where we have tried)

 

To rule out as much as possible I have tried:

1. Change the connection to point to another oracle server: it works

2. Connect to the failing oracle server with a different tool (SQL Developer): it works

3. ping the tns with tnsping: it works

4. Deployed a failing package to a SQL Server 2017 and run: it works

 

I'm using Visual studio 16.8.1

integration services 3.10

Connection manager for oracle (this) version 1, 32 and 64 bit installed

Attunity 5, 32 and 64 bit

 

Visual studio crashes when I try with target version 2019 and 2017

 

The same server worked fine until some change was done by the provider which required to change tns entry on tnsnames file in our side

But as I said before the connection is working when using other tools from the same machine or when packages are deployed on a SSIS 2017 server with Attunity 5 installed

It is only from VS that it is failing even though it worked before the tns change

 

 

 

 

 

 

Occasional Visitor

I have designed my SSIS package in VS ENT 2019 version 16.7.7 having Oracle Connection Manger.

I have Oracle 12c Client Installed on my machine along with attunity driver (V5).

The package works fine on local machine (loading data from Oracle to storing to Oracle Destination) using Oracle Source and Oracle Destination.

When I deploy the package on SSIS Server (SQL Server 2014), I am getting below error.

 

Package1:Error: The connection type "ORACLE" specified for connection manager "Oracle Connection Manager" 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.

 

SQL Server 2014 has been installed on Windows server 2012. It has same oracle client (12c) as my local machine along with relevant attunity drivers and Microsoft Connectors for Oracle which is there on my local machine.

Microsoft

@Viral_Kothari what is the targetServerVersion you set in designer? 

 

Senior Member

To add a bit more info on the issue I posted yesterday 

The same solution which is crashing with Visual studio 2019 it works with Visual Studio 2017

 

Edit: It is working on Visual Studio 2017 (SSDT stand alone 15.8)

Does not work with SSDT 15.9 which requires this component as well

New Contributor

Hello,

 

Is it possible to execute Oracle package in SSIS Execute SQL Task via Microsoft Oracle Connector?

Visitor

Hi - how can we update the data to Oracle using this connector. A an alternate, I am trying to use OLE DB Command to run update command. But it throws error "Provider does not provide parameter information and SetParameterInfo has not been called". Not sure what other alternate is available. I am stuck on updating data.

 

Any quick help will be greatly appreciated!

Microsoft
Visitor

@Chunhua thank you for your response.

 

I didn't find any information on how to update the data from the link you shared. Can you provide an example?

New Contributor

@Chunhua,

 

The existing Oracle Destination functionality is just for INSERT operations.

What is missing is that SSIS Execute SQL Task is not supported for Oracle Connector connections. This will allow to execute direct UPDATE, or call Oracle modules or procedures.

 

As of today, such attempt to execute SSIS Execute SQL Task gives an error, saying that you are not licensed for that, and contact DataDirect company  to buy a license.

 

ODBC Oracle Wire Protocol Driver 2021-01-25 080144.png

 

Please add such functionality.

Microsoft

@gsinghpt , are you using the OLE DB Command Task? It is not supported to use Oracle connection manager in OLE DB Command Task. To update the Oracle (not insert), please use SSIS Execute SQL Task. You need to find a ODBC driver for Oracle or OLDB driver for Oracle and setup the corresponding ODBC/Oracle connection manager and use it in the SSIS Execute SQL Task. In Execute SQL Task, you can write your own query, then you can update the data Oracle.

Microsoft

@ykhabins , how are you using the SSIS Execute SQL Task to connect to Oracle? Are you setting the ODBC connection manager to the SSIS Execute SQL Task and using the DataDirect ODBC driver for Oracle in the connection manager? The DataDirect ODBC driver for Oracle shipped with SSIS is only for internal use in Microsoft Connector for Oracle. You need to acquire an ODBC driver for Oracle by yourself.

Visitor

SSIS Execute SQL Task cannot be used with Data Flow Task for each row. I am using OLE DB Command Task. But getting this error

"Provider does not provide parameter information and SetParameterInfo has not been called".

 

Wondering why such a basic functionality of Upsert or just Update is not covered by the tool.

Regular Visitor

Hi, 

I'm having a heck of a time getting this working for local development. I have VS2019 Professional(16.8.4), VS2017 SSDT(15.9.31), and VS2017 (15.9.31) installed, along with 4 versions of the Microsoft Connector for Oracle by Attunity (5.0x32, 5.0x64, 4.0x32, 4.0x64), and SQL Server Integrations Services Oracle Connector (15.0.2000.110).

When I create a Connection Manager I can successfully connect to our Oracle Database using an entry in the tnsnames.ora file. I preview results in the Data Flow. However when debugging the package in both VS 2019 and 2017 targeted at SQL 2016, I receive the following error [Connection manager "abc"] Error: Oracle Home not found.

I have EncryptSensitveWithUserKey and Run64BitRuntime = True

Regular Visitor

To follow-up on my question above I tried the //host:port/service and when trying to debug the package it fails with the Oracle Home not found message.

Regular Visitor

I figured this out, running the package as 32bit, instead of 64, I received the following message: [Oracle Source [15]] Error: OCI error encountered. ORA-00933: SQL command not properly ended. I was terminating the sql command with a semi-colon, which apparently the connector does not like.

Occasional Visitor

Hi all,

I used SSIS 2012, Oracle database 19c, 18c, Oracle client 19c. But can not connect to Oracle by OLEDB. can you hepl me.

Thank all

Version history
Last update:
‎May 24 2020 05:08 PM
Updated by: