March 17, 2014 at 1:23 pm
I have created a basic extraction from an excel file (network drive) through Visual Studio. I use the Import wizard to create the ETL and then save the package. When I run the package within Visual Studio it runs fine but when I try to schedule the job I get errors: 0xC0202009
"The Microsoft office Access database engine cannot open or write to the file '\\misvna.....xlsx'
the AcquireConnection method call to the connection manager "sourceConnectionExcel" failed with error code 0xC0202009
I have flagged Use 32 bit runtime in the Job
and Run64BitRuntime as false in the Project (VS)
thanks
March 17, 2014 at 1:48 pm
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
03/17/2014 15:43:48,Vool,Error,,VM-8F47-3969,Vool,,,The job failed. The Job was invoked by User NAM\jl04638. The last step to run was step 1 (vooool).,00:00:02,0,0,,,,0
03/17/2014 15:43:48,Vool,Error,1,VM-8F47-3969,Vool,vooool,,Executed as user: VM-8F47-3969\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 3:43:48 PM Error: 2014-03-17 15:43:50.01 Code: 0xC0202009 Source: Package1 Connection manager "SourceConnectionExcel" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Office Access Database Engine" Hresult: 0x80004005 Description: "The Microsoft Office Access database engine cannot open or write to the file '\\misvnascfs... Report.xlsx'. It is already opened exclusively by another user<c/> or you need permission to view and write its data.". End Error Error: 2014-03-17 15:43:50.01 Code: 0xC020801C Source: Data Flow Task 1 Source - 'Volume RAW DATA$' [47] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2014-03-17 15:43:50.01 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: Source - 'Volume RAW DATA$' failed validation and returned error code 0xC020801C. End Error Error: 2014-03-17 15:43:50.01 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-03-17 15:43:50.01 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:43:48 PM Finished: 3:43:50 PM Elapsed: 1.42 seconds. The package execution failed. The step failed.,00:00:02,0,0,,,,0
March 18, 2014 at 2:14 am
Two questions: is the file open when the package is executed? Does the user that the package is executed under have access to that share location?
March 18, 2014 at 6:37 am
the file is not open. I should have access as the package runs in Visual Studio and in the import wizard from SSMS.
March 18, 2014 at 8:33 am
The key here is "The Job was invoked by User NAM\jl04638" and then "Executed as user: VM-8F47-3969\SYSTEM". I suspect that the executing user does not have rights to the file. When in design mode SSIS will use your current user rights to execute the package. Once the package has been deployed SQL Server Agent runs the package and it will use the default SQL Server Agent service account, which I believe in this case does not have rights to the file you are attempting to read.
Kirk T.
MCITP SQL Server 2008 Developer
March 18, 2014 at 8:44 am
Yes it's a permission issue. Try to run the SSIS package using Agent SSIS proxy using your ID as credential.
--
SQLBuddy
June 6, 2014 at 4:15 pm
El problema es de permisos. En el administrador de sql server en el servicio del agente sql hay que configurar el usuario de ejecución. Ese usuario debe tener permisos sobre el archivo que deseas ejecutar.
Saludos.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply