Execute Package Utility | Change connection manager

Question

Hi folks,

I have a package with 2 connections. One is for SQL Server and the other one for Oracle (defined as Linked Server on SQL Server).

I was able to change, on Data Source tab, correctly the Connection String for SQL Server. But for Oracle server I need to change not only Data Source but user and password too. My question is, how change password information?

My connections string looks like:

Data Source=SERVORACLE; User ID=myuser; Provider=OraOLEDB.Oracle.1; Persist Security Info=True;

Thanks,

Answer 1

Hi @Monalv-MSFT ,

Sorry for the delay. Let me tried to explain our scenario.

In the development environment we use a Linked Server. On production environment, our Linked Server is different and we don't have access to it from development and we don't have Visual on production environment neither.

We created a Linked Server to Oracle on production environment and test it from query and it's working find. After that, we create a Job on SQL Server Management with the DTSX package. We edited the Data Source Connection String and we change the server name for SQL and the name of the Linked Server and username on the connection string too:

Example of original Connection String:
Data Source=LS_DEV;User ID=user_adm;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;

Connection String after changes:
Data Source=LS_PROD;User ID=user_prd;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;

When we run the job, the folllowing error appear:

*Message  
Executed as user: NTAS\_sqlagent. Microsoft (R) SQL Server Execute Package Utility  Version 15.0.4043.16 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved.    Started:  7:22:24 PM  Error: 2020-12-16 19:22:24.80     Code: 0xC0016016     Source: TEST_PACKAGE      Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2020-12-16 19:22:44.49     Code: 0xC0202009     Source: TEST_PACKAGE Connection manager "OracleConn"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80004005  Description: "ORA-**12154: TNS:could not resolve the connect identifier specified"***  

I really appreciate any help.

Answer 2

It does not look like you are using a linked server - you are using an Oracle driver and connection in your SSIS package. It appears that you are using an ODBC Data Source as the source to your OracleConn connection.

The error you are getting is an Oracle error: *Description: "ORA-**12154: TNS:could not resolve the connect identifier specified"****

This error is stating that it cannot find the server identified in your ODBC connection - or that the defined username is not valid.

Answer 3

Hi @MariaBD ,

On this new environment, the linked server has a different name and use a different user to connect.

Please create a new Oracle Connection Manager and use new user name and password in SSIS package .

Use Oracle Authentication: Select this to use Oracle database authentication. If you use this authentication, enter your Oracle credentials as follows:
User name: Type the user name used to connect to the Oracle database.
Password: Type the Oracle database password for the user entered in the user name field.

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.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?

Answer 4

Thanks @Yitzhak Khabinsky ,

On this new environment, the linked server has a different name and use a different user to connect. So, I only need to change the name that appears on Data Source and in User Id for the new ones defined on linked server. My understanding is correct?

Regards,

Answer 5

Hi @MariaBD ,

Linked Server is defined on the SQL Server instance level. It means that when you are connected to the SQL Server database, the linked server becomes available, and there is no need is a second separate connection.

If you need to change Oracle connection it needs to be done in SSMS for linked server directly. Nothing to do with SSIS.