SSIS Package loading data from Excel fails when run as a job

2

I have created a SSIS Package using Visual Studio 2015 that takes an Excel file and imports it into a database. The job has been running smoothly for weeks, but for some reason, it failed this morning. When I run it as its own package, it runs perfectly but whenever it is in a job, even if it's the only step in the job, it fails. It is running using 32-bit runtime. The error I receive is pasted below.

Executed as user: NA\SQL_INTDB01$. Microsoft (R) SQL Server Execute Package 
Utility  Version 13.0.1601.5 for 32-bit  Copyright (C) 2016 Microsoft. All 
rights reserved.    Started:  9:41:48 AM  Error: 2018-01-04 09:41:49.36     
Code: 0xC0202009     Source: STRATImport 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 Access Database Engine"  Hresult: 0x80004005  
Description: "Failure creating file.".  End Error  Error: 2018-01-04 
09:41:49.36     Code: 0xC020801C     Source: Data Flow Task 1 Excel Source 
[71]          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: 2018-01-04 09:41:49.36     Code: 0xC0047017     Source: Data Flow 
Task 1 SSIS.Pipeline     Description: Excel Source failed validation and 
returned error code 0xC020801C.  End Error  Error: 2018-01-04 09:41:49.36     
Code: 0xC004700C     Source: Data Flow Task 1 SSIS.Pipeline     Description: 
One or more component failed validation.  End Error  Error: 2018-01-04 
09:41:49.36     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:  9:41:48 AM  
Finished: 9:41:49 AM  Elapsed:  0.438 seconds.  The package execution 
failed.  The step failed.
sql
sql-server
excel
visual-studio
ssis
asked on Stack Overflow Jan 4, 2018 by BH57 • edited Jan 4, 2018 by Hadi

1 Answer

2

Microsoft Access Database Engine has thrown the following error:

Failure creating file

This error is thrown when the Excel connection manager cannot access to the chosen path, it might be caused by:

  1. You are using a network path / try replacing with a local path
  2. it might be pointed to your local server, but when it is deployed to another server it will throw error
  3. you do not have permission to access the selected path from the account that is running the SSIS
  4. path does not exists

References

answered on Stack Overflow Jan 4, 2018 by Hadi

User contributions licensed under CC BY-SA 3.0