SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS, "Class Not Registered" error...deployment or permissions issue?


SSIS, "Class Not Registered" error...deployment or permissions issue?

Author
Message
Uncle Moki
Uncle Moki
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 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?
Uncle Moki
Uncle Moki
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 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...

Uncle Moki
Uncle Moki
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 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? Crazy
Uncle Moki
Uncle Moki
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 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....grrrrrrAngry
Uncle Moki
Uncle Moki
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 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...
Glenn Lee
Glenn Lee
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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
Uncle Moki
Uncle Moki
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 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....
aharuray
aharuray
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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
manjotk
manjotk
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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?
Crispin Proctor
Crispin Proctor
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1447 Visits: 414
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search