Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

SSIS, "Class Not Registered" error...deployment or permissions issue? Expand / Collapse
Author
Message
Posted Friday, July 25, 2008 4:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 6:42 PM
Points: 146, Visits: 222
I'm getting an error with an SSIS package that reads from an Access DB on a network share and writes to the SQL Server (local to the SQL Agent). The error is long, but it basically seems to say:

...MySSISPackage Connection manager "MyAccessDB.mdb"....
...SSIS Error Code DTS_E_OLEDBERROR.....
...An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

However, the error only occurs when the SQL Agent attempts to run the package (i.e. when scheduled). No error when I run it from my local machine (package running with MY network credentials). I think this is a permissions issues with the SQL Agent that's running the SSIS package (it may not have read access to the MS Access DB). Still waiting for verification from the IT folks that the SQL Agent account has the correct permissions.

On the other hand, I'm dealing with two dynamics that I'm not used to (not to mention that I'm still finding my way around SSIS) so I thought I'd ask about them here:

1. In my environment, you cannot store/install SSIS packages on the SQL servers. To get around this, I've been storing the SSIS packages on a network file server share and pointing the SQL Agent to the share location. Does anyone else do this and if so, any problems?

2. I haven't been "installing" the SSIS packages...just moving the object file around...from the "bin" folder of my SSIS project's directory (in the VS2005, "projects" folder), to the network share. Is this ok to do?
Post #541317
Posted Monday, July 28, 2008 11:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 6:42 PM
Points: 146, Visits: 222
Ok, got the permissions needed for the SQL Agent service account and the package still fails. I'm left to take the error message literally....that there is some missing driver/software on the server that's needed to connect to (and read from) the MS Access DB (based on the mention of, "Class Not Registered" and failed Method Call to "AcquireConnection").

Anyone have any advice?


Here's the error message in detail (spacing added):


Executed as user: MYDOMAIN\!mySqlAgent. ...0.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:56:42 AM

Error: 2008-07-28 09:56:44.37

Code: 0xC0202009

Source: ABC_Refresh Connection manager "ABC_Interface.mdb"

Description: SSIS Error Code DTS_E_OLEDBERROR. 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". End Error

Error: 2008-07-28 09:56:44.37

Code: 0xC020801C

Source: Synch ABC_SynchStage MS Access tblABC_Details [13]

Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ABC_Interface.mdb" failed with error code 0xC0202009. There may be error messages posted before this with m... The package execution fa...
Post #542070
Posted Tuesday, July 29, 2008 9:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 6:42 PM
Points: 146, Visits: 222
I'm not one to make arbitrary changes just because someone tells me to, but I'm at a loss here so I tried doing what I've read in other posts. i.e. Change the Debugging Run64Bitruntime to False (previously set to true), but it didn't fix the problem.

I've also looked into the ProtectionLevel property. It's currently set to EncryptAllWithUserKey (as suggested) but the error still occurs. For giggles (grrrrr), I tried DontSaveSensitive....still fails with the same error.

Lastly, I looked into setting-up a config file to deploy with the SSIS package. I didn't try it because it doesn't make sense...what settings would I possibly need to set in a config file that would fix this problem....when the problem itself isn't even defined yet? (although I guess I threw logic out the window with the seemingly arbitrary ProtectionLevel and Run64BitRunTime changes suggested by others).

I'm still thinking it's a driver/software problem on the server because of the, "ClassNotRegistered" and failed method-call errors in the description.

Anybody have any advice? Anybody? Anybody at all?
Post #542806
Posted Tuesday, July 29, 2008 12:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 6:42 PM
Points: 146, Visits: 222
Update:

I've done about all I know to do....

1. 32-bit vs. 64-bit issue?
Tried changing SQL Agent Job step from SSIS to Operating System and, since dtexec.exe supposedly always runs as a 32-bit version, using dtexec.exe /File "\\share\myfile.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW.
No change...still gets error.

2. Debugging Run64BitRunTime setting?
Tried setting to False.
No change.

3. File system permissions for SQL Agent Service Account?
Confirmed Read/Write.
No change.

4. SecuritySettings - Maybe it's because I'm building the package under one account (my NT account) and then running it under another account that can't decrypt the file (SQL Agent Service Account)?
a. Tried using EncryptSensitiveWithPassowrd, setting a password, and then adding the /Decrypt "mypassword" switch to the dtexec.exe command line.
No change.

b. Tried using DontSaveSensitive security setting, no password on the command line.
No change.

5. Just try to validate the package using the /Validate switch.
No change.

All that I can think to do now is try to re-encrypt the package while logged in under the SQL Agent Service Account (need to get with our IT group about that). If that doesn't work....grrrrrr
Post #542928
Posted Monday, August 4, 2008 4:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 6:42 PM
Points: 146, Visits: 222
TaDa...and DUH! It was the provider. Changed from "Microsoft.ACE.OLEDB.12.0" to, "Microsoft.Jet.OLEDB.4.0" and all is well.

BTW - I changed all of the other properties back to their defaults and the package still works:
A. Run64BitRuntime=True
B. ProtectionLevel=EncryptSensitiveWithUserKey (even though the package is not getting executed under the same account it was authored with)

I should have went with my gut instincts on this one. Would have saved a lot of time...
Post #546426
Posted Thursday, August 7, 2008 2:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 11:25 AM
Points: 5, Visits: 76
I have tried setting my provider to Microsoft.Jet.OLEDB.4.0 and I am still getting the "Class Not Registered" error as well. I am trying to do some simple data conversions during the import to sql.

Can someone please help!!


Here is a dump of the return.

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 3:27:50 PM
Progress: 2008-08-07 15:27:52.04
Source: Step1_6tbls
Validating: 0% complete
End Progress
Error: 2008-08-07 15:27:52.25
Code: 0xC0202009
Source: Liberty2007 Connection manager "SourceConnectionOLEDB"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" H
result: 0x80040154 Description: "Class not registered".
End Error
Error: 2008-08-07 15:27:52.25
Code: 0xC020801C
Source: Step1_6tbls Source - tbAgent [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG
ER. The AcquireConnection method call to the connection manager "SourceConnecti
onOLEDB" failed with error code 0xC0202009. There may be error messages posted
before this with more information on why the AcquireConnection method call faile
d.
End Error
Error: 2008-08-07 15:27:52.25
Code: 0xC0047017
Source: Step1_6tbls DTS.Pipeline
Description: component "Source - tbAgent" (1) failed validation and returned
error code 0xC020801C.
End Error
Progress: 2008-08-07 15:27:52.25
Source: Step1_6tbls
Validating: 5% complete
End Progress
Error: 2008-08-07 15:27:52.25
Code: 0xC004700C
Source: Step1_6tbls DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-08-07 15:27:52.26
Code: 0xC0024107
Source: Step1_6tbls
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 3:27:50 PM
Finished: 3:27:52 PM
Elapsed: 2.313 seconds

Post #548689
Posted Monday, August 11, 2008 3:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 31, 2010 6:42 PM
Points: 146, Visits: 222
Your Connection Manager named, "SourceConnectionOLEDB" is failing to connect. There are so many different possible reasons, it's hard to say what's happening (maybe that's why nobody wanted to respond to my posts). Not to mention I'm newer at this than you as i don't even know how you produced all those log results (I've read about how to log the package, but haven't tried it).

I can try to help though...what is it you are trying to do? i.e. SSIS package that reads from Access into SQL05? If so, then here are some questions to ask:
1. Is the service account on the SQL Server authorized to read/write to the location of your Access DB?
2. Is there any security set on the Access DB?
3. Is the SSIS Package being executed on a 64-bit OS?
4. Does the error occur at design-time or just at run-time?

Just some initial questions to ask....
Post #550628
Posted Monday, August 18, 2008 3:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 11, 2009 8:20 AM
Points: 3, Visits: 17
My situation is similar. My SSIS package works fine from local computer but when I try to run it from SQL Agent Job it fails with the following error:

Message
Executed as user: NYC\NYCSQL. ...Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:17:10 PM Progress: 2008-08-18 17:17:10.57 Source: Load Output From BO Excel into Hub_MEM Validating: 0% complete End Progress Error: 2008-08-18 17:17:10.66 Code: 0xC0202009 Source: Load_MEM_Hub Connection manager "Excel Connection Manager 1" Description: SSIS Error Code DTS_E_OLEDBERROR. 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". End Error Error: 2008-08-18 17:17:10.66 Code: 0xC020801C Source: Load Output From BO Excel into Hub_MEM Excel Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error ... The package execution fa... The step failed.

My steps are as below:

1. Run a Webservice to generate an excel file - This step completes successfully
2. Data Flow - Excel Source - Succeeds
3. Data Flow - Load Excel file into SQL05 table - This is where it fails

My connection managers are "Excel Connection Manager 1" has "Provider=Microsoft.Jet.OLEDB.4.0" setting.

I am still getting this error. I think there is some setting I am missing. Grasshopper, please help!

Thanks
Post #554637
Posted Monday, August 18, 2008 8:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 21, 2010 8:30 PM
Points: 2, Visits: 174
"Changed from "Microsoft.ACE.OLEDB.12.0" to, "Microsoft.Jet.OLEDB.4.0" and all is well."

Where and how do u change it?
Post #554709
Posted Tuesday, August 19, 2008 1:28 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412
Just read the last two posts - It appears you are using 64bit ssis and Excel. Jet is not supported on 64 bit. Either run the package with 32bit dtexec or set the option Menu > Propject properies > Run in 64bit to false.




Cheers,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Post #554775
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse