Issue

The SQL server Integration package which transfers the data from data source like excel to SQL Server database fails when executed from SQL Agent job using proxy account

ERROR

Date                      4/25/2013 4:16:34 PM

Log                         Job History (SSISTest)

Step ID                 1

Server                   Myserver\SQL2008STD

Job Name                            SSISTest

Step Name                         SSISJob1

Duration                              00:00:01

Sql Severity                        0

Sql Message ID                 0

Operator Emailed                           

Operator Net sent                          

Operator Paged                               

Retries Attempted                          0

Message

Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4276.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  4:16:34 PM  Info: 2013-04-25 16:16:34.81     Code: 0x4004300A     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-25 16:16:35.04     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager 1”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Access Database Engine”  Hresult: 0x80004005  Description: “The Microsoft Access database engine cannot open or write to the file ”. It is already opened exclusively by another user, or you need permission to view and write its data.”.  End Error  Error: 2013-04-25 16:16:35.05     Code: 0xC020801C     Source: Data Flow Task to move data from MS Excel to SQL server database 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 code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-25 16:16:35.07     Code: 0xC0047017     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-25 16:16:35.07     Code: 0xC004700C     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-25 16:16:35.09     Code: 0xC0024107     Source: Data Flow Task to move data from MS Excel to SQL server database      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:16:34 PM  Finished: 4:16:35 PM  Elapsed:  0.764 seconds.  Process Exit Code 1.  The step failed.

Cause:

Missing permission on PROFILE directory of SQL server Agent service account for Proxy account .

BufferTEMPstorage path and BlobTempstoragepath are defaulted to TEMP and TMP environment variables for SQL Server agent start up account by default.

When you use proxy account to execute a package then proxy account should have access to temp and TMP folder of SQL Server agents start up account profile.

TEMP=C:\Users\StartupaccountofSQLAgent\AppData\Local\Temp

TMP=C:\Users\ StartupaccountofSQLAgent\AppData\Local\Temp

If your start up account of SQL Server agent is Local service or Network service then proxy account should have permission for TMP and TEMP folder located under C:\Windows\ServiceProfiles for Local service and Network service

If the proxy account doesn’t have access to this location SSIS job would produce the below error:

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

04/08/2013 11:26:19,ExcelTC,Error,0,UKLONDT642483,ExcelTC,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\MyUser1.  The last step to run was step 1 (ExcelTC\Package).,00:00:01,0,0,,,,0

04/08/2013 11:26:19,ExcelTC,Error,1,UKLONDT642483,ExcelTC,ExcelTC\Package,,Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.   

Started:  11:26:19  Info: 2013-04-08 11:26:19.94     Code: 0x4004300A     Source: Data Flow Task SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-08 11:26:19.98     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager”     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: “Unspecified error”.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC020801C     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” 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: 2013-04-08 11:26:19.98     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:26:19  Finished: 11:26:19  Elapsed:  0.405 seconds.  Process Exit Code 1.  The step failed.,00:00:01,0,0,,,,0

04/08/2013 11:10:11,ExcelTC,Error,0,UKLONDT642483,ExcelTC,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\MyUser1.  The last step to run was step 1 (ExcelTC\Package).,00:00:01,0,0,,,,0

04/08/2013 11:10:11,ExcelTC,Error,1,UKLONDT642483,ExcelTC,ExcelTC\Package,,Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:10:12  Info: 2013-04-08 11:10:12.65     Code: 0x4004300A     Source: Data Flow Task SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-08 11:10:12.78     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager”     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: “Unspecified error”.  End Error  Error: 2013-04-08 11:10:12.78     Code: 0xC020801C     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” 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: 2013-04-08 11:10:12.79     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:10:12  Finished: 11:10:12  Elapsed:  0.593 seconds.  Process Exit Code 1.  The step failed.,00:00:01,0,0,,,,0