Oracle connectivity problem

[logmeinsucker]

Hi

I have a simple package to extract from Oracle 9i tables and load into SQL Server 2005 (64bit).

Debug is no problem and completes successfully.

However when I attempt to deploy the package via SQL Server I recieve the following verification errors:

Error: The AcquireConnection method call to the connection manager <database> failed with error code 0xC0202009

Error: component <taskname> failed validation and returned error code 0xC020801C

......

Error; An OLE DB error has occurred. Error code 0x80040154. An OLE DB record is available. Source "Microsoft OLE DB Service Components" Hresult: 0x80040154 description: "Class not registered"

This is causing me to lose more hair than I care to and any help on this would be gratefully recieved.

Alex.

 

[Catadmin]

Is this when you're actually trying to load the package into SSMS or when you're trying to run the package from SSMS?

If the former, please tell us the steps you're using to load the package.

If the later, please tell us how you're executing the package.

Thanks,


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"

 

[logmeinsucker]

These are the steps I followed:

1. Create Package
2. Modify the Deployment Utility, set TRUE for CreateDeploymentUtility in package properties
3. Locate the bin/Deployment folder on the target server
4. Execute the *.SSISDeploymentManifest file

The errors are generated when I install the package by executing the *.SSISDeploymentManifest file which initiates the Install Package wizard.

I select to validate package on install and it is this process which generates the error messages.

Hope this helps you.
Ale.x

 

[Catadmin]

Alex,

Okay, it sounds to me as if the install is validating each and every connection manager object & variable within your package. Does your SQL Server Agent account run under a local account or a domain user account? Is the package set to "Save Sensitive with User Key"? Do you use Windows Authentication with your connection managers or SQL Server Auth?

If the package is saved with a User Key and you use Windows Auth, the problem is probably that the SSA is a different user than yourself and can't connect to the DB(s) to validate the connections. You might try using "Don't Save Sensitive" and see if that changes anything.

Do you have any configuration files or anything outside of the package that is required to run the package? Besides the in-Package connection managers &/or flat files?

If not, try using the Import Package -> File System technique and pull in the package that ends in .dtsx. See if that gives you any errors or issues.



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"