General availability of Microsoft Connector for Oracle
Published May 24 2020 05:08 PM 34.8K 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.

70 Comments
Brass Contributor

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

Copper Contributor

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

Copper Contributor

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

Copper Contributor

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

 

 

Copper Contributor

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.

 

Copper Contributor

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

Copper Contributor

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.

Copper Contributor

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.

Copper Contributor

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.

Copper Contributor

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?

Copper Contributor

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).

Copper Contributor

Thanks @Junxiao_Ma

I am using Visual Studio Professional 2019, 16.7.6.

Copper Contributor

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

 

 

 

 

 

 

Copper Contributor

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? 

 

Copper Contributor

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

Brass Contributor

Hello,

 

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

Copper Contributor

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
Copper Contributor

@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?

Brass 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.

Copper Contributor

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.

Copper Contributor

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

Copper Contributor

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.

Copper Contributor

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.

Copper Contributor

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

Copper Contributor

I agree with others this is only half of a connection solution if it does not work in Execute SQL task. I'm getting the same dialog for purchase as @ykhabins 

 

The ODBC connection in my environment was configured using the Microsoft Oracle ODBC Driver v08.02.2314 included in the installer. I'd like to avoid the Oracle client or any other oracle driver at all costs but does not seem possible at this time. 

 

For SSIS development using Oracle OLEDB we have found we need the 32bit Oracle client on the development machines and this is such a mess. If anyone has gotten SSIS to work with the ORA unmanaged driver I would love to migrate to that. Again trying to avoid the oracle client and the lack of issuing DB commands using the Microsoft Connector in the Execute SQL task. 

Copper Contributor

 

Hi,
We are not able to create oracle data source connection in VS 2019(SSIS) version even after configured Microsoft oracle connector 1.0. Can you please suggest/share your inputs
Oracle_ConnIssue.PNG

 

 

 

Microsoft

@Ganesh1218 would you check whether any of below works? 

If user wants to use TNS service name in tnsnames.ora for connecting to Oracle Server, there are a few ways to achieve this:

  1. Install Oracle client and create tnsnames.ora in network\admin under Oracle client path. This is exactly the same with Attunity Oracle connector.
  2. Create tnsnames.ora under any local directory and add a TNS_ADMIN system environment variable which value is the tnsnames.ora directory path.
  3. Create tnsnames.ora under any local directory and set the direct path to OracleHome property in Oracle connection manager advanced editor.
Copper Contributor

Hello all,

 

I can successfully use the connectors and deploy them to our SQL Server 2016 Enterprise.

However, when I try to run the package directly from SSMS, under Integration Services Catalogs, I am getting an error: 

"The connection is not found.  This error is thrown by Connections collection when the specific connection element is not found."

Cannot find the connection manager with ID in the connection manager collection due to error code xxxxxxxxxx.  That connection manager is needed by "Oracle Source Connections [MSOraConnection]" in the connection manager collection of "Oracle Source".

 

Do I have to install these connectors as well on our SQL Server 2016? Or is there something wrong I am doing?

 

Thanks in advance for your help.

Copper Contributor

@glennpok, yes you have to install them on the server itself.  That is where it is running from when you deploy to the catalog.

Copper Contributor

@frugecn, Thanks for your reply. Very very helpful and I've opened a ticket for our DBAs to install the connectors.

Just one more question:  Should we install both 32-bit and 64-bit on the SQL Server 2016 Enterprise?  Or just the 64-bit.  Thanks.

Copper Contributor

It has been a really long time, but I think you can get away with just the 64-bit install.

Copper Contributor

@frugecn, after several tries, our DBA and I were able to make this connector work. We were initially getting errors on the server, even after the connectors were installed on it.

What we learned:

- I had to install both 4.0 and 5.0 64-bit on my laptop, even though our server only has 5.0

- Can't use existing projects (prior to server connector install) with packages using the connector.  I had to create a brand new project, then create the new package, then deploy.  

- But, tried redeploying the same package (even without doing any changes on it), it will fail.  Have to redeploy the project, not just the package.  I'd like to find out if there's a way to redeploy just a single package with the connector still working.

Hope this helps anyone who might have to do a lot of trial and error when trying to make these connectors work.  It's really superfast.  Our SSIS jobs that takes hours now finishes in minutes.

Thanks everyone.

 

Copper Contributor

Hi all,

we have just migrated our development data warehouse server from SQL Server 2017 to SQL Server 2019 including about 300 SSIS-Packages using the Attunity Connector. I'd like to share our experience and raise some questions. I have posted a copy of this comment over there: Microsoft Connector for Oracle is released for SQL Server 2019 Preview.

 

LDAP support

Most important thing first: there's no LDAP support. @Tim.Chen @Zoe_Luo @Chunhua @frugecn @steveo1770 : are there any news on this?

 

Performance

Performance seems to be identical to the Attunity Connector. Very few packages were running about 10-20 percent slower, but they were not running under the usual enviromental conditions. We will keep an eye on this.

 

[Microsoft][ODBC Oracle Wire Protocol driver]Invalid character value. Error in column 3.

Most of the migrated packages were running out of the box. About 10% of the packages generated an error like shown above in the source component (only the column number varied). The only solution to solve this behavior was recreating the source component in the data flow. I have briefly compared the SISS package's XML before and after and found no apparent differences. Any ideas on this?

I have found a related blog post: Error adding new column SSIS 2019 - MS Oracle Source connector

 

[Microsoft][ODBC Oracle Wire Protocol driver]Unable to convert column 2. Cannot convert from SQL type 93 to C type 2.

One single package returned in this error in the source component on execution. The solution to the problem was the same than above: recreate the source omponent in the dataflow. Any ideas?

 

[OLE DB Destination [14]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid data for type "numeric".".

On being executed one single package resulted in this error in the destination (!) component. The corresponding Attuntity version doesn't show this error. I have identified the column responsible for this error and its metadata is the same in both versions of the packages. Any ideas on this?

 

Thank you for reading and I'm looking forward to your comments,

Gerald

Copper Contributor

@Chunhua 

Is the Connection Manger compatible with SSL Oracle connections?

When I test with an older port 1512 connection it succeeds.

When I try to connect to a server that using Oracle SSL Secure Connections, I get a generic error message.

 

TITLE: Microsoft Visual Studio
------------------------------

There was an error trying to establish an Oracle connection with the database server.

------------------------------

Microsoft

@Gerald_AI Thank you for sharing us the feedbacks here.

LDAP support is in backlog, but not prioritized yet.

For the issues, would you create a support ticket for engineer to look into?  

 

Copper Contributor

Hi all,

 

we trying to migrate about 300 packages from VS2015/SQL Server 2016 to a cloud server with 2019.

 

Now we have a problem loading data into an Oracle TIMESTAMP(9) column.

The data viewer shows the correct data in CREATION_TSTP, data type is cast to (DT_STR,75,1252)

stephanvr_0-1637139806367.png

The package finishes without error and loads data into the oracle table:

stephanvr_4-1637140273967.png

but on the 'other' side there are no values in the column CREATION_TSTP, it's set to NULL.

stephanvr_1-1637139847096.png

The mapping and the data types: 

stephanvr_2-1637140044975.png

stephanvr_3-1637140085862.png

 

Exact the same package is running perfect under VS2015 and Attunity 4.

The display of the data types is identical DT_STR 75 on both sides under VS2015 and VS2019.

 

I tried casting it to various date types.

 

We are using/installed:

VS2019 16.11.6

Microsoft DataTools Integration Services 15.0.2000.170

Microsoft Connector for Oracle 15.0.2000.110 (both 32/64)

SQL Server 2019 (CU13)

Oracle Client 32/64

Oracle 19C

 

any ideas / hints?

 

Thanks

Stephan

Copper Contributor

I faced the same problem as @Ganesh1218 

I worked with ODAC 12 and 19 on my computer. I used 19 just to test it with Polybase. After uninstalling ODAC 19 connector is working fine.

 

Regards

Nebojsa

Copper Contributor

How to connect using kerberos authentication in attunity oracle connector ? Will attunity connector support kerberos authentication method or not..? Please guide @Chunhua

Copper Contributor

Posted originally to a wrong blog

 

I'm trying to figure out if and what Affinity connector to use. We had previously not been able to get it work on SQL Server 14 on Windows Server 2012 R2. We have upgraded to SQL Server 14 We are just upgrading our SQL Server. Currently our plan is to upgrade to SQL Server 2016. So our system will be

  • Windows Server 2012 R2
  • SQL Server 2016
  • Visual Studio 2017 with SSIS Version 14.0.3002.68
  • Oracle 19c.

The requirements for Microsoft Connectors v4.0 for Oracle say  Supported Oracle databases : Oracle 10.x; Oracle 11.x; Oracle 12c. Not sure if that just means it's not supported by Microsoft or that it will not work period with Oracle 19c. The OLE DB Source/destination are so much slower than the oracle ones and we would like to take advantage of the oracle destination/source ones.

 

Can this be done or do we have to wait and upgrade to SQL Server 2019?

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