Microsoft Connector for Oracle is released for SQL Server 2019 Preview
Published May 14 2019 01:19 AM 55.8K Views
Microsoft

Microsoft Connector for Oracle  is the replacement of Attunity Oracle Connector starting from SQL Server 2019 and now it is released for preview. 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.

 

Microsoft Connector for Oracle is supported in:

 

  1. SQL Server 2019

User can download Microsoft Connector for Oracle V1.0 (SQL Server 2019) - Preview for executing Oracle packages in SQL Server 2019. For Microsoft Oracle Connector, Oracle client is not required.

 

  1. SQL Server Data Tools (SSDT)

Microsoft Connector for Oracle has is shipped in SSDT for VS 2017 since version 15.9.0, as well as in SSIS projects for VS 2019. Users do not need extra installation of Oracle connector when designing packages targeting SQL Server 2019.

 

Using Oracle Connector in SQL Server Data Tools (SSDT) 15.9.x for VS 2017 or SSIS Projects for VS 2019

Microsoft Connector for Oracle has been shipped in SSDT for VS 2017 since 15.9.0 and SSIS Projects for VS 2019. User can use Microsoft Connector for Oracle to design packages targeting to SQL Server 2019 and pre-2019.

Design new SSIS packages

Open SSDT 15.9.x, 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.

 

In design time, no matter which SQL Server version the project is targeting, no extra dependency is required to be installed. 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.

 

Open and Execute existing SSIS packages

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.

 

After successfully opening existing packages, users can redesign or execute package, or change the target SQL Server version for the project.

 

Execute SSIS packages

Execute packages targeting SQL Server 2019

If the project is targeting SQL Server 2019, there is no extra dependency for execution. Oracle client is not required. User can directly execute and debug package.

 

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:

The required Attunity Oracle Connector and Oracle client platform version depends on project debug config, as the picture shown.  

3.png

If Run64BitRuntime is set to True, x64 version Attunity Oracle Connector and Oracle client are required to be installed.

If Run64BitRuntime is set to False, x32 version Attunity Oracle Connector and Oracle client are required to be installed.

 

Some FAQs:

 

  1. Exception from HRESULT: 0xC0011001 when opening existing package

It is a known issue in SSDT 15.9.0 and 15.9.1, when package uses features like Expressions in the connection manager. User can remove Expressions as a workaround. The issue will be fixed in next SSDT release (15.9.2).

 

  1. Cannot connect to Oracle Server

When using TNS service name in tnsnames.ora, user may encounter this error if the environment is not set right. There are few ways to fix:

  1. Add TNS_ADMIN to system environment variable, which value is the path of tnsnames.ora file.
  2. Set OracleHome and OracleHome64 in Oracle connection manager to tnsnames.ora path in x86 and x64 Oracle client respectively.
  3. Use format [//]host[:port][/service_name] in the TNS service name field in connection manager to connect instead.

 

  1. Exception deserializing the package "Value does not fall with the expected Range" when running package

This will happen in package execution if package is targeted to pre-SQL Server 2019, and corresponding Attunity Oracle Connector is not installed. Just install the corresponding Attunity connector to mitigate the error.

139 Comments
Copper Contributor

Thi is really a cool , so now SQL2019 can cun any Oracle Packages using connector? 

Microsoft

@BhavinSQL SQL 2019 can now run previous versions of SSIS packages that have Attunity Oracle connectors. 

Copper Contributor

Can connect to only one Oracle Server. With all others received errors in dataflow component. Although connection itself successfully verifies with TNS name, login and password.

 

Error Validation error. Data Flow Task: Data Flow Task: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "M1" failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Package.dtsx 0


Error Validation error. Data Flow Task: Package: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server. Package.dtsx 0

Microsoft

@AnatoliS ,thanks for trying Oracle connector. Can you help confirm that, does the test connection success in connection manager for the Servers which has DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER error? And is the working server using same connection method with others? The connection can be configured with TNS names, and [//]host[:port][/service_name] format. Did you installed Oracle client? Do you have NAMES.DIRECTORY_PATH setting in sqlnet.ora under Oracle client path? Thanks.

Copper Contributor

@Zoe_Luo, thank you for your involvement. Yes,  the connection test is OK for all the servers. Error appears in data flow task Oracle Source component during validation. Yes, all servers are using same connection method, only I change is TNS service name. Oracle clients, both 32 and 64 bits are installed. Attunity version in VS2017 with SSDTBI 15.8.0 works with all servers. In sqlnet.ora this setting is commented

SQLNET.AUTHENTICATION_SERVICES= (NONE)
#NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Copper Contributor

with //server:port/service variant - the same effect

The only difference between servers as DBA told that working is SE edition, others EE

Microsoft

@AnatoliS , I just checked my server is EE as well, but not able to repro this error. Did you see a red flag on your Oracle source component in SSDT before executing the package?

Untitled.png

Copper Contributor

Yes, same as yours 

 

Annotation 2019-05-31 190656.png

Copper Contributor

Please set Delay Validation property to True and check.

Microsoft

@AnatoliS , for better further investigation, I have sent you a message. Please have a check. 

Copper Contributor

@Tim.Chen @Zoe_Luo - any chance of this being back-ported to 2017?  I'd love to remove the extra dependencies and slim down our ETL VM images by dropping the Attunity connector and Oracle client installs, but we're just now migrating from 2012 to 2017 since 2019 is still in CTP.

Copper Contributor

I've recently updated to 15.9.2 for Visual Studio 2017.  I have all the Attunity Drivers install since 2.0 (2.0, 3.0, 4.0, 5.0).  When I load an SSIS project that is targeting SQL Server 2017, my project Oracle connection manager shows a red X and when I double click on it, it says Class not registered.  I uninstalled Attunity 5.0 and reinstalled it, but got the same error.  Any ideas how to solve that problem?

 

 

Microsoft

@frugecn , could you please have a screenshot about the errors? Thanks. 

Microsoft

@Bowers_Neil we currently do not have a plan to back port this to 2017. We will certainly consider this if more customers are asking for it. 

Copper Contributor

@Zoe_Luo, unfortunately in my troubleshooting, I deleted the offending connection manager and re-added it.  I can't reproduce the class error at the moment.  I am finding, though, that even though I have the Oracle Client installed with a sqlnet.ora pointing to go to an LDAP lookup, the connection manager is not able to find the Oracle database.  It will work if I go //host:port/service_name.  If I upload the package to an SSIS Catalog that doesn't have the new Oracle Connection, will that even work?  Is it going to use the Attunity Drivers or the new Oracle Connection drivers when the Target Server is SSIS 2017?

Microsoft

@frugecn , if you upload the package to SSIS Catalog in SQL Server 2017, it will use Attunity Driver, same as today. The new Oracle connector supports only in SQL 2019 currently. When you say the connection manager is not able to find the Oracle database, were you using the TNSname to connect? If yes, did you try the options in the doc about setting TNS_MAIN or OracleHome? May I know how your sqlnet.ora and tnsnames.ora are configured? 

Copper Contributor

@Zoe_Luo, we are not using tnsnames.ora to connect because there are too many to keep them synced in a tnsnames.ora file on all of the machines.

 

We have an ldap.ora file that points to our OID server and in the sqlent.ora it references LDAP first, then tnsnames.  I frankly don't even have a tnsnames.ora file on my box.

 

sqlnet.ora

names.directory_path=(ldap,tnsnames)

diag_adr_enabled=on
sec_protocol_error_trace_action=alert
sec_protocol_error_further_action=delay,2
sec_max_failed_login_attempts=2
sqlnet.encryption_server=requested
sqlnet.encryption_types_server=(aes256,aes192,aes128,3des168,3des112)
sqlnet.encryption_client=requested
sqlnet.encryption_types_client=(aes256,aes192,aes128,3des168,3des112)
sqlnet.expire_time=10
sqlnet.inbound_connect_timeout=5

ldap.ora

DEFAULT_ADMIN_CONTEXT = "dc=anywhere,dc=com"
DIRECTORY_SERVERS= (oid.anywhere.com:4545:4546)
DIRECTORY_SERVER_TYPE = OID
 
Microsoft

@frugecn , what was the value you set to TNS Service Name when you cannot connect to the server?

clipboard_image_0.png

Copper Contributor

I have also recently updated to SSDT 15.9.2 with VS2017 and receive the following error when executing the package :-

 

  • Error: OCI error encountered. ORA-12154: TNS:could not resolve the connect identifier specified

 

I have Attunity Drivers installed 4.0 and 5.0 (x64) installed targeting SQL Server 2016 and using LDAP 

 

The strange thing is the test connection in design time succeede but fails when running in the IDE. Deploying to SSISDB Catalog sems to work as well.

 

Has anyone encountered this issue ?

 

Microsoft

@ctrembea , does the error happen when executing the package and targeting SQL Server 2016? Did you set Run64bitRuntime to true since you have X64 installed? Thanks.

Copper Contributor

@Zoe_Luo, yes I had set the Rn64bitRuntime to true, but still get the error when executing th package.

 

 

 

Copper Contributor

@Zoe_Luo, Sorry, I've been out of pocket the last few weeks.  For my TNS Service Name I use one of the Aliases available in OID.  So like Appprd1.

 

@ctrembea, will using //ServerName:port/ServiceName work?  I think that's the right syntax.  That's the only way I could get it to work.  It is definitely not able to query using the LDAP.ora settings from what I can see.

Copper Contributor

thanks @frugecn  I had setup the TNS Service name using //Servername:port/Servicename. and clicking on test conection worked, it is just when I execute the package it fails

 

Microsoft

@ctrembea , since you are targeting to SQL Server 2016, it looks like the Attunity Connector 4.0 or the Oracle client is not installed correctly. Does the same project work using SSDT before version 15.8? Previously we had customer encountering same issue due to the Oracle client is not installed correctly, the oci.dll is not able to be found since it was not installed under client\bin folder somehow. You can check with process explorer , after executing the package, do not stop debugging, find dtsdebughost.exe in process explorer to see if oci.dll is loaded or not, and AttunitySSISOraAdapters.dll (Attunity connector dll) is also required to be loaded.

 

@frugecn , let me confirm with driver provider to see if LDAP is supported and get back to you later. Thanks.

Copper Contributor

@ctrembea, do you have both the 32-bit and the 64-bit Oracle clients installed?

Microsoft

@frugecn , confirmed that LDAP is not supported by the driver. Is it a blocking issue for you? We may request the enhancement if there is strong need. Thanks.

Copper Contributor

@Zoe_Luo, there is a work around, but because I can't use the same format between the Attunity Driver and the new driver, it will make it much more difficult to maintain, especially when they change dns names but not OID aliases.  Can we request the enhancement?

Copper Contributor

We have both 32bit and 64bit oracle clients installed.

 

@frugecn, at the moment our only choice is to say with SSDT 15.8.0 as we are targeting SQL Serve 2016 and our oracle connectivity is using LDAP which I don't think the organiation will move away to TNS. We would like to upgrade to the latest version of SSDT to make use of the fixes especially around SSAS Tabular.

 

If we could request an enhancement to support LDAP that would be appreciated as it is blocking as from upgrading SSDT. Thanks

 

Microsoft

@frugecn@ctrembeadriver provider was incorrect about LDAP support before. It is actually supported. We are working on investigating why your scenario does not work. Will update when we have a conclusion. Thanks.

Microsoft

@frugecn , could you please enable odbc tracing in ODBC data source administrator, use OID alias to connect and share us trace logs about connection failure? So we can further investigate the detailed error. Thanks.

Microsoft
@ctrembea , you mentioned that the connection only fails when executing the package, right? In this case, it is not the LDAP support issue. Then package targeting to SQL2016, Attunity connector will be used at runtime. So it must be Attunity connector required environment is not set correctly. Can you use process explorer  to check the dll loading as I mentioned above? Thanks.   
Copper Contributor

I am running VS2017 SSDT v15.9.12 on SQL 2017 Integration Services.

I have the Oracle Client and the Microsoft Connector Version 5.0 for Oracle by Attunity targeting SQL Server 2017 both installed.

Should I be seeing see the  red Icon like I did in prior version for SQL 2012?

clipboard_image_0.png

In SQL 2017 with SSDT v015.9.12, I am not seeing the red icon on the connectors.  Is that normal now with the Microsoft Connector for Oracle included in SSDT?

 

Microsoft

@ckallingerAAH , yes, in new SSDT, the icon is changed and should be like:

connectors.png

 
Copper Contributor

@Zoe_Luo, I've tried tracing both the 32-bit and 64-bit ODBC source administrator.  I can't get a trace log file to produce.

Copper Contributor

@Zoe_Luo The documentation states that the issue with HRESULT: 0xC0011001 was supposed to be resolved in SSDT version 15.9.2. However, the issue still persists with that version of SSDT.

Are there any known work-arounds besides changing the code to remove all expressions? Thanks in advance for any information provided.

Microsoft

@NickciufiIMA , we do not expect there is such error after SSDT 15.9.2. Can you share your package/or minimal repro package so we can know the rootcause for your failure? Thanks.

Copper Contributor

@Zoe_Luo my issue is with the connection itself. As detailed elsewhere in this thread, our connection uses project-level variables in order to define the connection.

I've added the code for the connection below, while redacting the DTSID, as well as the server name and user name being used as login credentials. As mentioned, the exception from HRESULT: 0xC0011001 error persists.

Thanks in advance for any assistance you can offer.



<?xml version="1.0"?>
<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:ObjectName="MDM Destination-Oracle"
DTS:DTSID="{xxxxxxxx-3832-412F-A2BE-D7EF1AA2FE9A}"
DTS:CreationName="MSORA">
<DTS:PropertyExpression
DTS:Name="Password">@[$Project::mdm_password]</DTS:PropertyExpression>
<DTS:PropertyExpression
DTS:Name="ServerName">@[$Project::mdm_server]</DTS:PropertyExpression>
<DTS:PropertyExpression
DTS:Name="UserName">@[$Project::mdm_username]</DTS:PropertyExpression>
<DTS:ObjectData>
<OracleXMLPackage>
<OraConnectionString>SERVER=[ServerName] ;USERNAME=[UserName];ORACLEHOME=;ORACLEHOME64=;WINAUTH=0</OraConnectionString>
<OraRetain>False</OraRetain>
<OraInitialCatalog></OraInitialCatalog>
<OraServerName>SemarchyDEV</OraServerName>
<OraUserName>SEMARCHY_MDM</OraUserName>
<OraOracleHome></OraOracleHome>
<OraOracleHome64></OraOracleHome64>
<OraWinAuthentication>False</OraWinAuthentication>
<OraEnableDetailedTracing>False</OraEnableDetailedTracing>
<OraPassword
Sensitive="1"></OraPassword>
</OracleXMLPackage>
</DTS:ObjectData>
</DTS:ConnectionManager>

Microsoft

@NickciufiIMA , thanks for reporting the issue, and yes, it is still able to repro in 15.9.2. New SSDT which includes the fix will be released before mid of Oct. For now, either you can remove the expression or update the package code to:

<?xml version="1.0"?>
<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"
DTS:ObjectName="MDM Destination-Oracle"
DTS:DTSID="{xxxxxxxx-3832-412F-A2BE-D7EF1AA2FE9A}"
DTS:CreationName="MSORA">
<DTS:ObjectData>
<OracleXMLPackage>
<OraConnectionString>SERVER=[ServerName] ;USERNAME=[UserName];ORACLEHOME=;ORACLEHOME64=;WINAUTH=0</OraConnectionString>
<OraRetain>False</OraRetain>
<OraInitialCatalog></OraInitialCatalog>
<OraServerName>SemarchyDEV</OraServerName>
<OraUserName>SEMARCHY_MDM</OraUserName>
<OraOracleHome></OraOracleHome>
<OraOracleHome64></OraOracleHome64>
<OraWinAuthentication>False</OraWinAuthentication>
<OraEnableDetailedTracing>False</OraEnableDetailedTracing>
<OraPassword
Sensitive="1"></OraPassword>
</OracleXMLPackage>
</DTS:ObjectData>
<DTS:PropertyExpression
DTS:Name="Password">@[$Project::mdm_password]</DTS:PropertyExpression>
<DTS:PropertyExpression
DTS:Name="ServerName">@[$Project::mdm_server]</DTS:PropertyExpression>
<DTS:PropertyExpression
DTS:Name="UserName">@[$Project::mdm_username]</DTS:PropertyExpression>
</DTS:ConnectionManager>

 

Thanks.

Microsoft

@frugecn , sorry for delay response, I just found that my previous comment was not published successfully. The driver provider mentioned that LDAP is supported but driver is not able to read local LDAP.ora. So I would like to know how LDAP.ora is used in your scenario, can you help me understand, so I can know what the gap is? The LDAP.ora is located on client machine, right? You mentioned before that "For my TNS Service Name I use one of the Aliases available in OID.  So like Appprd1.", may I know where appprd1 is defined? It is defined in the OID server, which is defined in LDAP.ora, and it is oid.anywhere.com in your sample?

Copper Contributor

@Zoe_Luo , the LDAP.ora file is on the client machine.  It has 3 parameters, DEFAULT_ADMIN_CONTEXT, DIRECTORY_SERVERS, and DIRECTORY_SERVER_TYPE.

 

Appprd1 is defined in the OID Server which the server to look is defined in the LDAP.ora file as you described.

 

Microsoft

@frugecn , thank you for your confirmation. I will discuss with driver provider to see how we can support your scenario. Thanks.

Copper Contributor

@Zoe_Luo 
Are there any news on this?
I have also similar problems since I updated my development environment with latest SSDT/SSIS in Visual Studio 2017, and targeting sql 2016 SSIS in my development. Packages with Attunity sources doesnt work anymore (the source component gets correctly replaced though). I have both Attunity V4 and V5 installed.
I took a look in Process Explorer while debugging as you mentioned somewhere above, and it looks like AttunitySSISOraAdapters.dll is not loaded...

Any advise?


Microsoft

@Septplla , what is the error you have? Can you share your error message and your settings about connection? thanks.

Microsoft

@Septplla , what is the error you have? Can you share your error message and your settings about connection? thanks.

Copper Contributor

@Zoe_Luo
I have very simple packages, just retrieving data from a Oracle db and moving it to a SQL server table.
The packages are created in Visual Studio 2015 for SQL server 2016 using Attunity Source component, and they all work fine in that development environment.
When moving/upgrading the packages to a new project in our new(er) development environment, Visual Studio 2017 with SSDT 15.9.2 the problem starts.
The Attunity components seems to be replaced by the new Oracle source correctly but when opening the package I get these validation error messages:

clipboard_image_0.png

 

The MAXPROD_Attunity connection manager is configured with SSISconfiguration and the connection string looks like this:

clipboard_image_1.png

The Server name MAXPROD is defined in out TNSNAMES.ORA-file.

Both Attunity V4 and V5 are installed.

 

The validation error when trying to run the package in VS 2017 is this, pretty much the same as when opening the package.

clipboard_image_2.png

 

* One question that just popped up when reading the error message more thouroughly, is if an ODBC connection is needed?
Since the validation error says: "here was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server"

 

But wait, I just discovered, that if I set DelayValidation to True, then I can execute the package without any errors!
So this seems to be a problem just in the validation step...

Microsoft

@Septplla , would you mind to have a try with format [//]host:[:port][/service_name] to see if connection works, rather than using MAXPROD defined in TNSNAMES.ORA? Besides, can you share your MAXPROD definition in tnsnames.ora? Thanks.

Copper Contributor

@Zoe_Luo 
Thanks for your advise! :)
Using [//]host[:port][/service_name] in my connection string instead of the TNS name did the trick. :smile:

(But of course it would be preferred if TNS names can be used...)

FYI, the MAXPROD definition in our TNSNAME.ORA looks like this:

 

clipboard_image_0.png

 

 

Microsoft

@Septplla , thanks for your quick response. TNSNAME.ORA is supported. Can you try the approaches mentioned in the article in order to locate TNSNAME.ORA?

1. Add TNS_ADMIN to system environment variable, which value is the path of tnsnames.ora.

2. Set OracleHome and OracleHome64 in Oracle connection manager to tnsnames.ora path in x86 and x64 Oracle client respectively.

Either way should work. Please let me know if it still does not work.

Besides, in your TNSNAME.ORA, the definition name is MAXPROD.WORLD, it is the same name you used in connection? Thanks.

Copper Contributor
I have a Windows 2019 server with SQL Server 2019 installed. When I extract data from an ORACLE source by the new Microsoft Connector for Oracle, the performance is really bad. The former Attunity drivers were a lot faster (on Win2016 and SQL Server 2016). The specs of the 2019 server and SQL Server installation are the same as on the other 2016 server. I tried different numbers in "batch size" property of the connector, but that doesn't solve it. Does anybody have a solution or suggenstions to solve this?
Microsoft

@evdhoning , can you share your performance data, table definition, sample data in table and the settings in connector, so we can see if we can have a repro? Thanks.

Version history
Last update:
‎May 14 2019 01:19 AM
Updated by: