July 25, 2008 at 4:04 pm
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?
July 28, 2008 at 11:20 am
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...
July 29, 2008 at 9:55 am
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:
July 29, 2008 at 12:05 pm
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:angry:
August 4, 2008 at 4:45 pm
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...
August 7, 2008 at 2:54 pm
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
August 11, 2008 at 3:12 pm
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....
August 18, 2008 at 3:34 pm
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
August 18, 2008 at 8:40 pm
"Changed from "Microsoft.ACE.OLEDB.12.0" to, "Microsoft.Jet.OLEDB.4.0" and all is well."
Where and how do u change it?
August 19, 2008 at 1:28 am
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,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 19, 2008 at 6:28 am
Crispin,
Thanks for your response. I tried setting the Project properties Run 64 Bit to False but I still get the same error (class not registered).
I have a similar package in which I use "Flat File Source" and load it to table (instead of using EXCEL source) and that one has no problem.
Thanks,
August 19, 2008 at 6:55 am
can you logon to the server and run it interactivly?
If so, open CMD and set your path as "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn" or whereever you installed SQL.
Using that dtexec, try run it. Does it run?
Are you sure the server can see the path to the file? (Was the text file in the same place?)
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 19, 2008 at 2:51 pm
Sorry for my ignorance,
i cant find these options in BI.
🙁
August 19, 2008 at 2:58 pm
My dtexec gives error as it says WEBSERVICE cannot run with this edition. But I have requested access to this folder where the excel files exist. May be it is a permission issue, thats why it works from one folder (for flat file) but not from another (excel file). I will update you as soon as I am able to test this.
Thanks for the help on this.
Newbee - You can find options if you select Properties of Package (for 64 bit) or for Excel (for JET engine option).
August 20, 2008 at 12:13 am
manjotk (8/18/2008)
"Changed from "Microsoft.ACE.OLEDB.12.0" to, "Microsoft.Jet.OLEDB.4.0" and all is well."Where and how do u change it?
Actually, I just created a New Connection Manager by doing the following:
1. Right clicked in the, "Connection Manager" space.
2. Selected, "New OLE DB Connection..."
3. Clicked the, "New..." button.
4. Selected, "Microsoft Jet 4.0 OLE DB Provider" in the "Provider:" box at top and clicked OK.
5. Under, "Database file name:" I clicked the, "Browse..." button and navigated to a network share that contains the desired Access DB (making sure that the SQL Agent on the server that will schedule and execute this package has permissions to the network share).
6. In my case, the Access DB isn't password protected or anything so I just left the, "Admin" User Name and blank Password checkbox unchecked. Clicked the, "Test Connection" button (though this really isn't checking anything more than the fact that the Access DB had no User Name or Password.
7. "Ok" my way out of the dialogs and viola...new Connection Manager created.
8. Now I changed everything in my SSIS package to use the NEW Connection Manager I just created.
9. Finally, I built the package, copied the dtsx file from my SSIS project's "bin" folder to a location accessible by the SQL Agent scheduled to execute the package, created a new SQL Agent job on the scheduling server, pointed the job to the dtsx file, scheduled it, and done.
Alternatively...
I guess I could have just edited the, "Provider" portion of the "ConnectionString" property of the existing Connection Manager (the one that wasn't working because it was set to use the ACE provider.) If I had done it this way...
1. Right click on the existing Connection Manager
2. Select, "Properties"
3. In the Properties pane, changed the ConnectionString property from something like this:
Data Source=\etworkshare\mydb.mdb;Provider=Microsoft.ACE.OLEDB.12.0;
...to this:
Data Source=\etworkshare\mydb.mdb;Provider=Microsoft.Jet.OLEDB.4.0;
I didn't do it that way because I wanted to keep the original Connection Manager around in case my assumption about the Provider being the culprit was wrong.
Hope that helps.
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply