Lookup with Oracle Database

Question

Lookup with Oracle Database

mklein2

SSChasing Mays

Points: 609
More actions
November 18, 2010 at 6:02 am

#388259

Hello,
I want to use a Lookup between a Dataset and an Oracle Database.
And I have a "Warning" on my lookup.
"Cannot retrieve the code page info from the OLE DB Provider...
When I execute my package I have these error.
-----------------------------------------
[Lookup [3056]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "PRDSIEB7.SIEBELCATS" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[DTS.Pipeline] Error: component "Lookup" (3056) failed validation and returned error code 0xC020801C.
Progress: Validating - 100 percent complete
[DTS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.
Validation is completed
[Connection manager "PRDSIEB7.SIEBELCATS"] Error: 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 ".
-----------------------------------------
In my connection managers, I can ping succefuly my Oracle database.
In my Lookup, I add my query, and I can preview the result. So I don't understand why it can't connect to my database.
Thanks
PS : I'm sorry for my english...

Viewing 10 posts - 1 through 9 (of 9 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

At first sight: the warning and the error are not related.

The error message says that your package crashes because the user or password are invalid. As you can preview results and the test connection succeeds, I can only ask this:

what is the protectionlevel of the package?

(click somewhere in the control flow to select the package and then look at the properties window)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mklein2 SSChasing Mays Points: 609 More actions · Answer 2

The protection level of my package is "DontSaveSensitive"

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

mklein2 (11/19/2010)
The protection level of my package is "DontSaveSensitive"

The word itself explains it all: nothing is saved.

Change the package protection level to something else:

encrypt sensitive with userkey or encrypt sensitive with password.

The first one will encrypt the credentials with your user key (some sort of combination of your user count and your computer name), meaning that if you open the package on your computer, the credentials will still be there. If someone else opens the package, they have to re-enter the credentials.

The second one will encrypt the credentials with a password that you will provide. Everyone who has the password can open the package and the credentials will be there. If they don't have the password, they can still open the package, but they have to re-enter the credentials.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mklein2 SSChasing Mays Points: 609 More actions · Answer 4

Thanks.

And how do I do if I want to do a lookup with an ODBC?

I don't have a Provider for this...

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

mklein2 (11/19/2010)
Thanks.
And how do I do if I want to do a lookup with an ODBC?
I don't have a Provider for this...

You mean you want to use a lookup component with an ODBC connection to the Oracle DB?

Why don't you use an OLE DB connection?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mklein2 SSChasing Mays Points: 609 More actions · Answer 6

I'm sorry, it's an other problem.

For Oracle I use an OLE DB connection. This is OK. But after I have to do an other Lookup with an ODBC connection. I find the solution to do an Data reader in other DataFlow and I stock my query in Excel file.

And I use this Excel file with Microsoft Jet Provider.

Regards,

Mathieu

PS : Sorry for my English.

Phil Parkin SSC Guru Points: 246913 More actions · Answer 7

mklein2 (11/22/2010)
I'm sorry, it's an other problem.
For Oracle I use an OLE DB connection. This is OK. But after I have to do an other Lookup with an ODBC connection. I find the solution to do an Data reader in other DataFlow and I stock my query in Excel file.
And I use this Excel file with Microsoft Jet Provider.
Regards,
Mathieu
PS : Sorry for my English.

You are having a problem accessing data in an Excel file, but what is it? Please provide the text of the error you are receiving.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

mklein2 SSChasing Mays Points: 609 More actions · Answer 8

No I don't have a problem for now, now it's in my reflexion 😉

But how do I empty the contents of an Excel file? Leaving the column header.

Regards,

Mathieu

Phil Parkin SSC Guru Points: 246913 More actions · Answer 9

Haha 🙂

Rather than emptying an existing Excel file, you may find it easier to have a blank 'template' file available somewhere on the network which contains only the headers you require and just copy that over to the file you wish to 'empty'. The end result is the same.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin