May 13, 2011 at 10:25 am
Hi,
I have a SS 2005 SSIS package with a Native OLE DB connection manager to an mdb file. When I execute from BIDS, everything works fine. However if I execute it using dtexec or SQL Agent it fails with the error below. Does anyone know why?
Thanks
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 16:19:48
Error: 2011-05-13 16:19:49.13
Code: 0xC0202009
Source: Update Property Connection manager "Property Aggregates"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file '\\mi-uk-fb-02\uw$\Property Division\Property Aggregates.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
End Error
Error: 2011-05-13 16:19:49.13
Code: 0xC020801C
Source: Data Flow Task Property and Speciality Aggregates [474]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Property Aggregates" failed with error code 0xC0202009. There may be error messages posted be
fore this with more information on why the AcquireConnection method call failed.
End Error
Error: 2011-05-13 16:19:49.13
Code: 0xC0047017
Source: Data Flow Task DTS.Pipeline
Description: component "Property Aggregates" (474) failed validation and returned error code 0xC020801C.
End Error
Error: 2011-05-13 16:19:49.13
Code: 0xC004700C
Source: Data Flow Task DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2011-05-13 16:19:49.13
Code: 0xC0024107
Source: Data Flow Task
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 16:19:48
Finished: 16:19:49
Elapsed: 0.422 seconds
NULL
May 13, 2011 at 11:40 am
The Microsoft Jet database engine cannot open the file '\\mi-uk-fb-02\uw$\Property Division\Property Aggregates.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Go to \mi-uk-fb-02\uw$\Property Division\Property and check the permissions for the SQL Server Agent Service Account.
If you log on using the SQL Server Agent Account you can verify that it is a permissions issue.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 16, 2011 at 4:25 am
Thanks, I will try that. Just one thing is confusing me though. If the connection manager has a valid user name and password specified in it, why does SQLAgent also need permissions?
May 16, 2011 at 4:45 am
c.jowett (5/16/2011)
Thanks, I will try that. Just one thing is confusing me though. If the connection manager has a valid user name and password specified in it, why does SQLAgent also need permissions?
There is more than one area where permissions are needed. The SQL Agent permissions are required to permit folder/file access at the o/s level.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply