Explanation of the issue
Many of our servers run the 64-bit version of SQL Server and Integration Services and use components and /or providers that require 32-bit execution. There are several articles and blog / forum posts that address the problem with a couple of different methods depending upon what is needed but I haven’t stumbled upon a single source for all the methods put together in an easy to read way.
Error manifestation in Visual Studio
SSIS package “Package.dtsx” starting.
Information: 0x4004300A at Load Excel File, SSIS.Pipeline: Validation phase is beginning.
Error: 0xC00F9304 at Package, Connection manager “Excel Connection Manager”: 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.
Error: 0xC020801C at Load Excel File, Excel 2010 Destination [19]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Load Excel File, SSIS.Pipeline: component “Excel 2010 Destination” (19) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Load Excel File, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Load Excel File: There were errors during task validation.
SSIS package “Package.dtsx” finished: Failure.
SQL Agent job history manifestation
Executed as user: NT Service\SQLAgent$SQL2012. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 2:05:56 PM Error: 2013-10-01 14:05:57.05 Code: 0xC0209303 Source: Package Connection manager “Excel Connection Manager” Description: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0×00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0×80040154 Description: “Class not registered”. End Error Error: 2013-10-01 14:05:57.05 Code: 0xC001002B Source: Package Connection manager “Excel Connection Manager” Description: The ‘Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816 End Error Error: 2013-10-01 14:05:57.05 Code: 0xC020801C Source: Load Excel File Excel 2010 Destination [2] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2013-10-01 14:05:57.05 Code: 0xC0047017 Source: Load Excel File SSIS.Pipeline Description: Excel 2010 Destination failed validation and returned error code 0xC020801C. End Error Error: 2013-10-01 14:05:57.05 Code: 0xC004700C Source: Load Excel File SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-10-01 14:05:57.05 Code: 0xC0024107 Source: Load Excel File Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:05:56 PM Finished: 2:05:57 PM Elapsed: 0.891 seconds. The package execution failed. The step failed.
Missing Components
X86 DTExec.exe not installed
I embrace a minimalist’s approach to software installation on all my servers. This means that often my servers will only have the database engine installed and possibly a few other features (such as Integration Services) as needed by the system. In most situations, there is no need for developer tools on a production server, therefore, BIDS and other components are omitted.
When installing components in this way it is easy to overlook DTExec.exe. This application is required to execute SSIS packages and only the 64-bit version will be installed if you omit some of the optional features. In order to prevent the SQL Agent error given as an example above, you will need to install either the Business Intelligence Development Studio (2008), SQL Server Data Tools (2012), or Client Tools (MSDN.aspx)). I prefer to install the Client Tools when dealing with production servers.
Once installed, the 32-bit version of DTExec.exe will be available and your SQL Agent job will be able to execute in 32-bit mode. See below, Execution Properties – SQL Agent “Use 32-bit” check box, for more details on how to execute an SSIS package in 32-bit via SQL Agent job.
ACE OLEDB Provider not installed
While this is not technically a platform issue, as is the subject of this post, it does cause the same symptoms and thus is worth lumping in with this list of solutions. When attempting to interact with Microsoft Office files, such as Excel or Access, you need to have the right provider installed. Normally MS Office is installed as 32-bit even though the 64-bit version is available. Either way, if your server does not have Office installed you will not have the right providers necessary to execute your SSIS package.
Sticking to the minimalist’s mentality, I would never want to install a suite of tools like MS Office on my servers unless there was no other option. In this case, the option is to install the providers directly. The providers have a smaller footprint on your server and there are no licensing issues.
Typically you would use either the Jet 4.0 provider or the ACE OLEDB provider to interact with MS Office files. You might remember that in our example errors the system complained about the Microsoft.ACE.OLEDB.12.0 not being registered and the class not being found. The Jet provider is deprecated and not recommended for use.
Download site for the Microsoft Access Database Engine redistributable.
Execution Properties
SQL Agent “Use 32-bit” check box
In addition, to having the necessary components installed 64-bit installations of SQL Server must be told to use the 32-bit version of DTExec.exe when running a SQL Agent job. To set this option you must open the job and edit the job step for your SSIS package. Then navigate to the Execution Options tab and select the Use 32 bit runtime check box.
Visual studio 64-bit runtime mode
Just like the SQL Agent job, your Visual Studio will likely need to be set to run as 32-bit also. In order to do this you need to open the project properties by right clicking on the project in the solution explorer and selecting properties or going to the menu bar and clicking Project and then
Leave a Reply