I was working through a request from a customer to import an excel spreadsheet into SQL Server on a recurring basis. I decided to create an SSIS Package to accomplish this and then call the package from a job in SQL Server Agent. However, when I ran the job, I received an error that the connection to my Excel file was not supported (as follows):
Executed as user: CRH-W764SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 8:43:38 AM Error: 2014-10-06 08:43:39.05 Code: 0xC00F9304 Source: test Connection manager "SourceConnectionExcel" Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. End Error Error: 2014-10-06 08:43:39.05 Code: 0xC020801C Source: Data Flow Task 1 Source - Sheet1$ [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2014-10-06 08:43:39.05 Code: 0xC0047017 Source: Data Flow Task 1 SSIS.Pipeline Description: component "Source - Sheet1$" (1) failed validation and returned error code 0xC020801C. End Error Error: 2014-10-06 08:43:39.05 Code: 0xC004700C Source: Data Flow Task 1 SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-10-06 08:43:39.05 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: 8:43:38 AM Finished: 8:43:39 AM Elapsed: 0.078 seconds. The package execution failed. The step failed.
Resolved: Simple Change to SQL Server Agent Job
One simple change to the SQL Server Agent Job resolved this issue. I went into the properties of the job and edited the step for the import from Excel. Under the Execution Options tab, there is a checkbox to Use 32 bit runtime. Checking that box and saving my job allows the import to run successfully.
Job Step Properties Screen
Related Articles:
Uncovering Hidden Characters in SQL Database
Training on Microsoft SQL Server Virtual Labs
