VS2019 Oracle Connector Not Working with SSIS Package Configuration

chalim 1 Reputation point
2021-03-17T19:21:32.523+00:00

Hello,

I have an ETL SSIS package that works for SQL Sever 2012/Oracle 12c and uses the Attunity connector. However, I am trying to migrate SSIS packages to use SQL Server 2019 and Oracle 12c and the new Microsoft Connector for Oracle V1.0.

Here's my new environment - I have SQL Server 2019 (Amazon EC2) and Visual Studio 2019 and I am trying to use the Microsoft Connector for Oracle V1.0 as a connection manager to connect to my Oracle 19c (Amazon RDS). It works initially when I build a new connection manager and hardcode the TNS service name, username, and password. I use EZConnect format for the TNS service name [//]host[:port][/service_name].

However, when I add a package configuration to pull the connection string from my SQL Server configuration table, I run into problems with the Oracle connection. When I test the Oracle connection, I get "There was an error trying to establish an Oracle connection with the database server." When I run the package, it fails with "[Connection manager "OracleRDS"] Error: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server." I have a SQL Server connection and that one is okay. Also, I viewed the SSIS package's XML code and I noticed that the Oracle password was being stored and it was in all lowercase even though the actual password is mixed case. So, I do a test and I change the actual Oracle password in the database to be all lowercase to match the XML code and then the SSIS Oracle connection manager works!

  1. How do I get my mixed case password to work with package configuration?
  2. Is my Oracle connection string correct for my configuration table? I have tried with "SERVER" and "Data Source" and both.
    a. SERVER=//xxx.yyyy.zzz.rds.amazonaws.com:1521/ServiceName;USERNAME=user;WINAUTH=0;PASSWORD=MyPassword;
    b. Data Source=//xxx.yyyy.zzz.rds.amazonaws.com:1521/ServiceName;User ID=user;password=MyPassword;
    c. SERVER=//xxx.yyyy.zzz.rds.amazonaws.com:1521/ServiceName;USERNAME=user;WINAUTH=0;PASSWORD=MyPassword;Data Source=//xxx.yyyy.zzz.rds.amazonaws.com:1521/ServiceName;User ID=user;password=MyPassword;

My setup:
Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) Developer Edition (64-bit)
Visual Studio Professional 2019 (Version 16.9.1)
Oracle 12c (AWS RDS instance)
Microsoft Connector for Oracle V1.0 32-bit (version 2019.150.2000.110)
SQL Server Integration Services Projects extension version 3.12.1 (downloaded from VS2019 marketplace)

I changed the SSIS project properties TargetServerVersion = SQL Server 2019. DelayValidation = True for the SSIS package and the connection managers.

Thanks so much in advance!

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,449 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 24,926 Reputation points
    2021-03-17T20:02:23.317+00:00

    Hi @chalim ,

    You need to adjust few things.

    1. Modify SSIS package ProtectionLevel as DontSaveSensistive. This way no sensitive information like passwords are saved in the package. It could be done on the SSIS project level too. It will force to change that setting for every single package in the project.
    2. A proper Oracle connection string has 4 attributes:
      SERVER=//xxx.yyyy.zzz.rds.amazonaws.com:1521/ServiceName
      USERNAME=user;
      PASSWORD=MyPassword;
      WINAUTH=0;
    3. Parameterize the enire connection string as a Project level parameter.

    UPDATE
    To upgrade SSIS 2012 project to 2019 version, go to the project properties, and set the TargetServerVersion setting value as SQL Server 2019.

    Please see below.
    78984-ssis-targetserverversion-2.png

  2. chalim 1 Reputation point
    2021-03-18T02:47:04.993+00:00

    @YitzhakKhabinsky, thanks so much for your quick reply! I am using package deployment model, so I don't have the project parameter option. I am using package configurations (SQL Server config type) to manage the connection strings.

    So, the existing SQL Server 2012 packages actually do have SSIS package ProtectionLevel as DontSaveSensitive.

    Starting fresh, I just copied over the SQL Server 2012 SSIS solution again and tried upgrading the packages to SQL Server 2019 by changing the TargetServerVersion = SQL Server 2019. All the packages upgraded without error. I modified the Oracle connection string in my SSIS Configurations table and ran the package, but it still fails. Same errors.

    78860-image.png

    When I test the Oracle connection manager, I still get the error:
    "There was an error trying to establish an Oracle connection with the database server."

    Package ProtectionLevel = DontSaveSensitive
    DelayValidation = True

    78995-image.png

    However, I can still see the connection string when I View Code and the password is lowercase!
    79022-image.png

  3. Monalv-MSFT 5,891 Reputation points
    2021-03-18T07:34:42.437+00:00

    Hi @chalim ,

    1.How do I get my mixed case password to work with package configuration?
    Could you please share the example of your configuration file?
    Please check if you set the Connection String to configuration file correctly.
    Please refer to Lesson 5-2: Enable and configure package configurations.

    2.Please try to set expression in the property of ConnectionString and add the variable which will store the value of ConnectionString.

    Best regards,
    Mona

    If the answer is helpful, please click "Accept Answer" and upvote it.

    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.

  4. Ravi, Dhana K 1 Reputation point
    2021-05-21T16:11:13.887+00:00

    @Monalv-MSFT I'm facing same error, does it resolved yet?