SSIS Job failed (0xC0202009)

Y Zhu 6 Reputation points
2020-09-23T07:09:05.297+00:00

I have an SSIS package with a simple data flow task that moves data from an Excel file to SQL Server. The package ran well in the Visual Studio, but the job failed in the SQL Server Agent.

I used a service account to run this job. This service account has access to both the Excel source and the SQL Server destination table.

Does anybody have an idea?

Error Message:

Microsoft (R) SQL Server Execute Package Utility Version 12.0.6372.1 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 1:32:22 AM Error: 2020-09-23 01:32:27.66 Code: 0xC0202009 Source: Copy Excel Data to Staging Table Excel Source [2] 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: "Unexpected error from external database driver (1).". End Error Error: 2020-09-23 01:32:27.67 Code: 0xC004701A Source: Copy Excel Data to Staging Table SSIS.Pipeline Description: Excel Source failed the pre-execute phase and returned error code 0xC0202009. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:32:22 AM Finished: 1:32:47 AM Elapsed: 25.203 seconds. The package execution failed. The step failed.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,620 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,449 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,656 Reputation points
    2020-09-23T07:19:00.833+00:00

    Does the service account do have also write permission on the folder? It's mandatory, because when you open/access a XLSX file a new log file gets created.

    Better use a proxy account with the required permissions: Create a SQL Server Agent Proxy

    1 person found this answer helpful.
  2. Monalv-MSFT 5,891 Reputation points
    2020-09-23T08:49:11.493+00:00

    Hi @Y Zhu ,

    Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Unexpected error from external database driver (1).".

    Could you please share the connectionstring of the excel connection manager?

    Please download and install the ACE driver.
    The following links will be helpful:
    Download Microsoft Access Database Engine 2016 Redistributable
    Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing

    Best Regards,
    Mona

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.