Dynamic Oracle Connection in SSIS

Question

Dynamic Oracle Connection in SSIS

najeeb_khan_337

SSC Veteran

Points: 230
More actions
March 28, 2009 at 3:16 am

#132305

Dear All,
I have a package that connects to oracle and dumps some data into text file. It works fine till here. Now I want to get the same data from multiple oracle instances. For this reason I store the connection string in a variable which looks like this "Server=SRV1;Provider=MSDAORA.1;Pwd= PWD1;User ID= USR1". When I assign variable to ConnectionsString expresion property of Connectiono, the OLEDB source becomes invalid and give error like this
TITLE: Microsoft Visual Studio
------------------------------
Error at RunningRoutines [Connection manager "DXBT.APPS"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E4D Description: "ORA-01017: invalid username/password; logon denied
".
Error at Data Flow Task [OLE DB Source [622]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DXBT.APPS" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Thanx for your support
Najeeb

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

najeeb_khan_337 (3/28/2009)
Dear All,
I have a package that connects to oracle and dumps some data into text file. It works fine till here. Now I want to get the same data from multiple oracle instances. For this reason I store the connection string in a variable which looks like this "Server=SRV1;Provider=MSDAORA.1;Pwd= PWD1;User ID= USR1". When I assign variable to ConnectionsString expresion property of Connectiono, the OLEDB source becomes invalid and give error like this
TITLE: Microsoft Visual Studio
------------------------------
Error at RunningRoutines [Connection manager "DXBT.APPS"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E4D Description: "ORA-01017: invalid username/password; logon denied
".
Error at Data Flow Task [OLE DB Source [622]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DXBT.APPS" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Thanx for your support
Najeeb

The error message is self-explanatory . You are not using the correct username / password combination. Check your credentials.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

najeeb_khan_337 SSC Veteran Points: 230 More actions · Answer 2

I do MsgBox with variables containing the username and password. its shows the correct values. it seems it is somehow taking wrong username & password because recentlly i discovererd that everytime i get error there is a file generated in the location of my text file detination. the name of file is sqlnet.txt the content is

The username below is not the one which i am passing.

**********************************************************************

Fatal NI connect error 12560, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe)(HOST=Machine_name)(USER=Username))))

VERSION INFORMATION:

TNS for 32-bit Windows: Version 10.1.0.4.0 - Production

Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.1.0.4.0 - Production

Time: 28-MAR-2009 11:06:20

Tracing not turned on.

Tns error struct:

ns main err code: 12560

TNS-12560: TNS:protocol adapter error

ns secondary err code: 0

nt main err code: 530

TNS-00530: Protocol adapter error

nt secondary err code: 126

nt OS err code: 0

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

Check this message thread. Your listener and tnsnames have to be setup properly.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 4

Hi !

I am also having the same issue any progress on this issue ?