Connection Error?

[ptheriault]

I have a x64, SP1 Developer edtion install of 2005. I have installed the ORAClient tools for 10G. I created a SSIS package to import data from an Oracle database as follows.

start -> run -> DTSWizard...
I walked through the steps to import the data using a query. I stored the password to the Oracle instance in the package.
I saved the package to MSDB.
I ran the package without any errors.

Now I've opened the package through BIDS and I get the following error.

Error at Cumulative_earned[Connection manager "SourceConnectionOLEDB"]: An OLE DB error has occured. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

Error at Data Flow Task[Source - Query[1]]: The AcquireConnection method call to the connection manager "SourceconnectionOLEDB" filed with error code 0xC0202009.

Additional Information:
Exception from HRESULT: 0xC020801C(Microsoft.SqlServer.DTSPiplineWrap)

Does anyone know what this means? I've tried to execute the saved package again from with Management Studio but it fails to valid.

Anyone?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

I assume that when you edit the package it's on the same machine that you created the package on?

Did you install the 32bit or 64bit version of the Oracle client?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

yes, I'm on the same server and I installed the full 64 bit version of the client. The wierd thing is it will connect to the Oracle instance the first time I create the package and run it. But when I try to open it up in BIDS I get that error. Have you ever seen that? I wish it told me which class isn't registered.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

That's a new one to me. When if you schedule the package and run it from the job?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

What type of encryption are you using on the package?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Cat,
Where you been? I've been waiting for you to get to this question. I didn't set any encryption on the package unless it does that by default. It's very wierd. I can't do anything with the objects that use that connection.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

I've been out of state, out of mind and visiting my family. @=) Sorry about that. @=)

If the package is saved as "Don't save sensitive", it'll erase all the passwords every time you open it and you'll have to re-enter them before you can run it in BIDS.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

I can see the encrypted password in the properties of the connection. Is there a patch for SSIS that I might be missing? I only have SP1 installed.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Cat,
I found this post of people getting the same error.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=160340&SiteID=1

One person fixed by doing this.

when i chage the sequence container transaction property from Required to Supported, it is working fine.

Do you know where the container transaction property is?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

Should be in that properties window when you highlight the container. I.E., the dockable properties window,not the one that pops up when you right-click and hit edit/properties.

Again, though, check the dockable properties window of the package to see what encryption level the package is set to. Let me know that if the above solution doesn't work.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Cat,
I'm sorry, I'm still trying to figure this stuff out. I know this is a stupid question...
but is the container what I see on the control flow tab?

I'm just not finding these properties. I am looking in the docked properties window.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Ok, I found the encryption. It is encryptSensitiveWithuserKey. Is that the correct value?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Ok, I think I'm making progress.
Now I get an error.

Test Connection failed because of an error in initializing provider. The OraOLEDB.Oracle.1 provider is not registered on the local machine.

The problem is... I know that it is because I am using it that provider for a link server.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

One thing of note here...
The oracle client was installed after my SQL tools.
Do you think that matters?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

Paul,

Bear with me. I'm doing a little guesswork here. @=)

I don't think the Oracle client install after the SQL Tools should affect anything. Though, the error makes me wonder where or not you actually installed the client or just opened up a compressed exe file that was essentially zip file? I did that yesterday. Wasted 2 hours trying to figure out why my install didn't work when all I did was uncompress the install files. @=)

The Encrypt Sensitive with User Key... Are you the owner of the package? If not, try retyping in the passwords in all the connection objects and then see if you get the same error.

If this is the issue, there's a good chance it'll error out again simply because you don't have the private key of the user who created the package. Try changing it to "Don'tSaveSensitive" or "EncryptSensitiveWithPassword" if you absolutely need it to encrypt the password stuff. BTW, SSIS, doesn't show the passwords even if the package isn't saved sensitive. The former just doesn't save the passwords within BIDs at all, so you have to retype them every time you open the package to play with it.

Did you actually use a Sequence Container for the tasks? Or a ForNext container? Or is it just individual tasks in the DataFlow container?

For an actual Sequence Container, the symbol is 3 blue boxes set in an upside L, with a green arrow pointing from the center down and and a red arrow pointing from the center across to the right. Regardless of what container you use, the property is actually called TransactionOption and it is under TRANSACTIONS in the dockable properties window (very last choice).

Let me know how things go.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Thanks for the advise Cat. I changed the encryption to DontSaveSensitive. I then retyped the password for the SourceConnectionOLDEDB object. I then tried to open the connection and I get these following.

The specified provider is not supported. Please choose different provider in connection manager.

So I tried to create a new connection.
I right clicked in the connection manager and selected new OLE DB connection. From the drop down list I selected Native OLE DB\Microsoft OLE DB Provider for Oracle. I enter in the server and password and it fails because it doesn't think the provider is installed. ARGHHHH!!!

I have no problem using this provider to create link servers or using it with the import wizard.
I think the only thing left to try it to un-install it.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

Sounds like you're on the right track. Maybe an uninstall and reinstall will help.

Or maybe SSIS doesn't like your provider. I've noticed a lot of the older providers have been dumped from SSIS compatibility. Have you checked your version?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

It's the drivers from the 10G install. Are there any Patches for SSIS that I am missing?


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

Do you have SP1 and the post SP1 hot fix installed?


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

I don't have the post SP1 Hot Fix. Do you have a link to it?

- Paul
- If at first you don't succeed, find out if the loser gets anything.