Blog Post

SQL Server Integration Services (SSIS) Blog
3 MIN READ

Microsoft Connector for Oracle is released for SQL Server 2019 Preview

Tim.Chen's avatar
Tim.Chen
Icon for Microsoft rankMicrosoft
May 14, 2019

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.

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

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.  

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.

Updated May 14, 2019
Version 1.0
  • riftware's avatar
    riftware
    Copper Contributor
    May 25, 2023

    Is there any update on LDAP support?   Large enterprise environments are really against using tnsnames.ora for failover and modernization of a server (where the server name can change but the sid / servicenames are maintained).....they much prefer to update activedirectory/ldap as opposed to updating several hundred tnsnames.ora files.

     

     

  • MakingEasyLookHard's avatar
    MakingEasyLookHard
    Brass Contributor
    Jul 13, 2023

    I'm not seeing any new updates on what the timeline is for LDAP support. This is absolutely killing us! We scrapped our SQL 2019 upgrade thinking for sure it would be there by SQL Server 2022 and it is still not. Is there no timeline yet for adding back LDAP connectivity?

  • Tim.Chen's avatar
    Tim.Chen
    Icon for Microsoft rankMicrosoft
    May 13, 2020

    Alexandru1190 is you are running in Visual Studio by default it will run in 32bit mode because Visual Studio is 32bit. If you also have SQL Server 2019 installed on the same machine then you can run in 64bit mode. You mentioned that you downloaded the 64bit connector, so I suppose you are running the package in 64bit mode, in which case it will launch the package with SSIS bits that came from the SQL Server 2019 installation. 

     

    The version 15.0.2070.41 is the RTM version of SQL Server 2019. For the Oracle connector to work properly, it requires at least the CU1 (cumulative update) version of SQL Server 2019 to be installed. The latest one is already CU4 now. See https://support.microsoft.com/en-us/help/4548597/cumulative-update-4-for-sql-server-2019. Please upgrade your SQL Server installation to CU4 and try again. 

  • MakingEasyLookHard's avatar
    MakingEasyLookHard
    Brass Contributor
    Jul 18, 2023

    MattLance Tim.Chen Hey Matt.. do you hate this answer as much I do? 🙂 Tim.Chen I dont understand why you guys went silent on the LDAP issue. This is a big problem. Some idea on where things stand would be appreciated. We have to figure out whether we need to shop around for another solution that has the same capabilities as the Attuntity drivers or go straight ODBC and hope we get the efficiency that Attunity brought but either way it is a mountain of work for us and with 2016 aging out we need some clarity. 

  • ctrembea's avatar
    ctrembea
    Copper Contributor
    Aug 22, 2019

    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 ?

     

  • Zoe_Luo's avatar
    Zoe_Luo
    Icon for Microsoft rankMicrosoft
    Aug 26, 2019

    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.

  • frugecn's avatar
    frugecn
    Copper Contributor
    Aug 26, 2019

    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?

  • Zoe_Luo's avatar
    Zoe_Luo
    Icon for Microsoft rankMicrosoft
    Nov 07, 2019

    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.