Running SSIS Packages in 32-bit

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: 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 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). 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 2010 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.

ssis-job-32-bit-step-1

ssis-job-32-bit-step-2-2

Then navigate to the Execution Options tab and select the Use 32 bit runtime check box.

SQL-Agent-use-32-bit-runtime-2

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 <project_name> Properties. Once here you need to set Run64BitRuntime to FALSE.

Visual-Studio-2008-Project-Properties-Run64BitRuntime

This article has 33 comments

  1. When i am running my SSIS package it is showing the below errror any help??
    Executed as user: DEVDOMAIN\SVR248$. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 7:40:50 AM Error: 2014-10-28 07:40:50.90 Code: 0xC0209302 Source: ExcelToTablePackage Connection manager “Excel Connection Manager 1” Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”. End Error Error: 2014-10-28 07:40:50.90 Code: 0xC020801C Source: Import Excel Data to TempNeaPayers Excel Source [52] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager 1” failed with error code 0xC0209302. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2014-10-28 07:40:50.90 Code: 0xC0047017 Source: Import Excel Data to TempNeaPayers SSIS.Pipeline Description: component “Excel Source” (52) failed validation and returned error code 0xC020801C. End Error Error: 2014-10-28 07:40:50.90 Code: 0xC004700C Source: Import Excel Data to TempNeaPayers SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2014-10-28 07:40:50.90 Code: 0xC0024107 Source: Import Excel Data to TempNeaPayers Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:40:50 AM Finished: 7:40:50 AM Elapsed: 0.75 seconds. The package execution failed. The step failed

  2. Nice post Derik. I am stumped as my SSIS Access import job works fine on both my development machine and server. I thought it was because it was on my dev machine that has so VS and Office installed, so I installed a fresh copy of SQL Server 2012 on a new 2008 R2 Server. However, the SQL Agent job fails when running the same job on the server as well! I have both 32 and 64 bit versions of DTExec on the Server and can see the 32 bit version started in the task manager. The job history doesn’t seem to give any details either. I’m stumped… Ideas? Thanks!

    • You say that, on the server which works, you have Office installed but you don’t mention Office for the fresh copy. This leads me to believe that you need to install the ACE provider. Have you tried that already?

  3. The SSIS package works when I execute it manually from SSIS Catalog in SSMS both on the Dev and Server boxes. Neither will execute successfully when running as a SQL Agent job. Since it works in SSIS package executed manually, I don’t think it is an ACES issue. I also check the box to run the package with the 32-bit runtime under Advanced Configuration. I get 2 errors:

    1. SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “MyConnectionMgr” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    2. 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.”.

    Thanks for any ideas!

    • Eric, I have a couple conflicting comments regarding your issue. First, the ACE provider. If your SSIS Catalog is on a different server than the server which is hosting the SQL Agent job, then the executing server is different and could materialize as one working while the other doesn’t. Second, your error messages sound like you have solved the problem of running in 32-bit mode and that you are using the ACE provider correctly. Error 2 seems fairly clear that there is a file locking or file permissions issue for your to contend with now. One common practice is to create a template .xlsx file, then have your SSIS package copy the template at the beginning of each execution with a date/time stamp in the file name. Then you can populate it. This prevents issues where someone has your working copy open and you can’t write to it.

  4. Hey,

    I have installed ACE OLDDB driver on the SQL Server. I have SQL Job which runs the package using “Use 32 bit Runtime” but still i am getting that error “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.”

    Any ideas? why i am still getting even after changing to use 32 bit runtime.

    • This part of the error message, “The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available,” leads me to believe that your package is not running in 32-bit mode as you expect. If you have the SQL Agent check box checked, do you have the 32-bit version of DTExec.exe installed? If you install SQL Server 64-bit, it will only install the 64-bit exe. You would have to add the Client Tools shared feature in order to have the 32-bit version of DTExec.exe available.

  5. Hi Derik – thank you for a great article. I’m wet-behind-the-ears-new to SQL Server and you’ve helped tremendously! I have managed to setup everything to the point where your article seems to resolve my remaining issue, but am running into what I’m assuming is a very silly problem:

    When I get to the Job Properties / Steps configuration, there is no 32 bit check box (and neither the menu set shown in your screenshot). All I have are tabs for Package and Configuration.

    I’m sure I’m missing something silly, and would appreciate a pointer in the right direction a lot.

    Thanks
    Wilhelm

  6. Hi,

    I have created the full backup job through maintenance plan,but now it’s giving error “Could not load package “Maintenance Plans\DBBackup” because of error 0x80040154. Description: Class not registered Source: Started: 1:08:50 PM Finished: 1:08:50 PM Elapsed: 0.031 seconds. The package could not be loaded. The step failed.”
    I have changed the option 32 bit runtime from job step properties.Still getting the same error.Could you please provide the way to resolve the issue.

    Environment:SQL Server 2012 Standard Edition 64bit

  7. Hi Derik,

    I am running into the issue of “The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered.” I am receiving this error during design time itself, it is not even showing the excel sheet’s names in the drop down. Hence, I am not even able to go ahead from there. I tried installing the said driver but that didn’t solve the problem either. I even changed the provider name in connection string to Microsoft.JET.OLEDB.4.0 but it still didn’t help. Any help will much appreciated.

    • Couple follow up questions:
      When you say design-time, you are in Visual Studio and this error is occurring when executing the package? If so, have you disabled Run64BitRuntime? (see this post’s Visual studio 64-bit runtime mode section)
      The part where I was confused was regarding your drop-down box comment. Are you receiving this error before you even execute the package, when you are trying to configure the Excel Connection Manager? If so, please send me screenshots to derik (at) sqlhammer (dot) com, so that I can understand your problem better.

      • Hi Derik,

        I am receiving this error even before I try to run it. I am unable to get the list of columns even after creating the connection manager. I am sending the snapshots to the said email id.

        Thanks for the help!

        Regards,
        Shreya K

  8. Hi

    Thanks for this post. I have read it top to bottom including all the comments but I still cant solve my issue. Im really hoping someone can help. Here are the details.

    I have SQL Server 2008 R2 (named instance) and SQL Server 2012 installed on my local machine. I am using 2008 for this project. It accesses an excel file stored in a network folder and inserts the data into a database on the same server that package runs on.

    1. It runs fine in BIDS
    2. I then import it into SQL Server and ‘Run package’ with this string in the ‘Command line’ and it runs perfectly:

    /SQL “\ContractorPersonnel_Master” /SERVER “MyLocalMachineName\mssqlserver2008” /X86 /CHECKPOINTING OFF /REPORTING V /SET “\Package.Variables[User::ServerName].Value”;”MyLocalMachineName\mssqlserver2008″ /SET “\Package.Variables[User::ProjectFolder].Value”;”\\ServerName\shared\Contractor Personnel Dashboard\Test” /SET “\Package.Variables[User::ErrorMailAddress].Value”;”MyEmailAddress”

    3. I then create a job, owned by myself. The step that executes the package is ‘Run as’ a Proxy using my AD credentials so that it can access the excel file stored on the network location. I use the exact same command line as above. It throws the following error. And just to be clear, because my package throws an error, it moves the excel file to an error sub folder, so the issue is not that my package running under my credential cannot access the excel file. It just cant seem to open it.

    Executed as user: MyADAccount. portNewDataPA40 Description: There were errors during task validation. End Error Error: 2015-08-05 10:10:07.62 Code: 0xC0202009 Source: ContractorPersonnel_SAP_Imports 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: 2015-08-05 10:10:07.62 Code: 0xC020801C Source: ImportNewDataQuals 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: 2015-08-05 10:10:07.63 Code: 0xC0047017 Source: ImportNewDataQuals SSIS.Pipeline Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C. End Error Error: 2015-08-05 10:10:07.63 Code: 0xC004700C Source: ImportNewDataQuals SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2015-08-05 10:10:07.63 Code: 0xC0024107 Source: ImportNewDataQuals Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:10:04 AM Finished: 10:10:09 AM Elapsed: 4.649 seconds. The package execution failed. The step failed.

  9. Hi,

    I am unable to run SQL agent job in 32 bit in a 64-bit environment. It successfully executes when i ran interactively in visual studio but fails in executing a job..

    following error:

    Message
    Executed as user: KMX\svccafsysdev. Microsoft (R) SQL Server Execute Package Utility Version 11.0.5058.0 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:23:41 AM Error: 2015-08-19 10:23:43.34 Code: 0x00000001 Source: Login + File Paths Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:23:41 AM Finished: 10:23:43 AM Elapsed: 1.61 seconds. The package execution failed. The step failed.

    Login + File paths is a script task which read’s and write’s some folder path and excel files….Basically it fails executing a script task..

    Appreciate your help..

    Thanks

  10. Hello Sir –

    I am getting following error while running my package from SQL server 2014 server
    Both deployment server as well as agent job servers are 2014.

    “To run a ssis package outside of sql server data tools you must install Lookup of Integration Services or higher.”

    Initially package was designed in 2008 R2 later was upgraded to 2012.

    Please guide. Please let me know in case you need any more information. Thank you.

  11. Hi Derik,

    I have some 32bit — 64bit problems. There are many answers but could not locate the right one.

    I am using visual studio 2012 shell, have installed “AccessDatabaseEngine_x64_2010.exe”
    and trying to update existing SSIS packages which put the results to an excel .XLSX.
    Following message occurs every time, no matter which switch is put on or off.

    Error: 0xC0209302 at CommonName, Connection manager “Excel CommonName”:The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000.
    An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”.
    Error: 0xC001002B at CommonName, Connection manager “Excel CommonName”: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.

    I am testing on SQL Server machine where the databases are located.
    Any help would be welcome thanks!

    • Did you install the 64 bit version of ACE because you wanted to run your package in 64 bit mode? I have never attempted to use 64 bit mode to deal with Excel files before. I might recommend installing the normal ACE provider, verifying that you have Run64BitRuntime = false, and re-test.

  12. Simple put most powerful and useful blog ..You have done a great help at the right time….Thank you so much .

  13. Hello Derik,

    I have an issue with 32/64 bit mode.
    Its sql server 2008 r2 ssis package having a lookup task and it fails when I run it in 32 bit mode but runs fine when I run it in 64 bit mode. Lookup task references a sql table.

    Any thoughts on technical info will greatly help.

  14. Hi,
    I get this error when i run my package in Visual studio Data Tools 2015. But it runs successfully when i deploy in sql server 2016/2012 and run through a job.
    “Retrieving the COM class factory for component with CLSID {8957A18C-F62B-4A46-83FC-E8792FFD06DC} failed due to the following error: 8007007e The specified module could not be found. (Exception from HRESULT: 0x8007007E).”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.”

    Please suggest me.

    Thank you.

  15. I had a job that has been running fine for over a year and with Microsoft updates installed on 10/11/2017, now I am getting the error that you described. I have checked and the DTExec.exe is installed and the 32 bit mode is still enabled. Any thoughts

Leave a Reply

%d bloggers like this: