question

chalim-1832 avatar image
0 Votes"
chalim-1832 asked YitzhakKhabinsky-0887 commented

VS2019 Oracle Connector Not Working with SSIS Package Configuration

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
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @YitzhakKhabinsky-0887 and @Monalv-msft,

I can reproduce this problem when I create a brand new package with package configuration using Configuration Type = SQL Server. When the Oracle password has any uppercase letters, the package will fail. Otherwise, it will succeed (lowercase and special characters works). Please see screenshots below.

  1. When Oracle password is mixed case:
    Package Configuration:

79254-image.png


SSIS Configuration table:
79234-image.png


Oracle Connection Manager Test Connection Fails:
79361-image.png



Package execution progress:
79248-image.png


0 Votes 0 ·
image.png (19.9 KiB)

IMHO, it is a bug.
You need to open a support ticket with Microsoft.
But I doubt that they will agree to fix it.

0 Votes 0 ·
chalim-1832 avatar image chalim-1832 YitzhakKhabinsky-0887 ·

I thought it was a bug as well. Seeing as to how Microsoft probably won't fix this, What are my other options? Thanks for your taking the time to reply to my posts. It is very much appreciated!

0 Votes 0 ·

Apologies @YitzhakKhabinsky-0887 , I had deleted my previous post because I forgot to blank out something in one of my images and I think your reply got deleted as well as a result. :(

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @chalim-1832,

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@YitzhakKhabinsky, thanks so much for your quick reply! Before I go making more changes to the SSIS packages, I wanted to ask:

What is the proper way to upgrade SSIS packages created in SQL Server 2012 to 2019? The existing SQL Server 2012 packages do have SSIS package ProtectionLevel as DontSaveSensitive, but I started playing around with the ProtectionLevel to try to get the packages to work in in Visual Studio 2019.

0 Votes 0 ·

@chalim-1832,

I updated the answer, please check its UPDATE section.

0 Votes 0 ·
chalim-1832 avatar image
0 Votes"
chalim-1832 answered YitzhakKhabinsky-0887 edited

@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



image.png (91.0 KiB)
image.png (16.6 KiB)
image.png (17.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@chalim-1832,

(1) Don't worry about the password attribute in the connection string. Attributes are case insensitive.

(2) Right click on the Oracle connection in the connections pane, select Edit entry.
Type there all the parameters, and click 'Test connection' button. Make sure it is working.

Also, please share a screen shot of that dialog box here.

(3) Your 2nd screen shot of the package connection is irrelevant.The package needs to read the connection from the .dtsconfig file.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @chalim-1832 ,

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.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @chalim-1832 ,

Please put forward your need in the following link:

SQL Server Feedback Forum

Best regards,
Mona


0 Votes 0 ·

Thanks @Monalv-msft . I have submitted the bug. I will work on converting to project deployment model to resolve this issue.

0 Votes 0 ·
RaviDhanaK-8429 avatar image
0 Votes"
RaviDhanaK-8429 answered YitzhakKhabinsky-0887 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.