Excel error 64-bit version of SSIS
I have a 64bit system and installed ssis on my system.How do I changed the ssis project to 32 bit. I have this error: Information: 0x4004300A at Data Flow Task, 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 Data Flow Task, Excel Source [1]: 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 Data Flow Task, SSIS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.Joe
March 20th, 2009 9:40pm

Dude this is the answer I was looking for - worked like a charm :)
Free Windows Admin Tool Kit Click here and download it now
June 14th, 2009 4:00am

This is the winning answer! Thank you.
June 15th, 2009 2:40am

You need to execute your package using the 32-bit SSIS runtime. Depending on how you're running it now, the way to do that can change.If you're using a SQL Agent Job to run your SSIS package, then:If you're using SQL Server 2008, there should be a checkbox on the Job Step page to run the package in 32-bit mode.If you're using SQL Server 2005, you will have to change the Job Step from an Integration Services Step to an Operating System step, and specify a DTEXEC command line instead. The DTEXEC command line needs to specify the DTEXEC.EXE from the Program Files (x86) folders, NOT the DTEXEC.EXE from the Program Files folders.If you're executing the package using DTEXEC via some command-line scheduling process, you need to specify the 32-bit version of DTEXEC by explicitly running the DTEXEC.EXE from the Program Files (x86) SQL Server folder. I am an instant fan Todd. I will be definitely reading your blog. It nice that someone has the "right" answers.Production DBA
Free Windows Admin Tool Kit Click here and download it now
September 23rd, 2009 1:44pm

How is it possible to control in which bit-mode my code runs? I have a small application from which users can execute SSIS pakcages. When I run the packages from SSMC, they're fine and when I run them through an agent job, the checkbox to run in 32-bit mode makes the job succeed. However, when I call the packages from code, which works fine for other packages not dealing with Excel, they fail with the same error as thread starters: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.I've done the Run64BitRuntime=false on the SSIS project and redeployed, but that didn't seem to change anything. Any ideas?Thanks,Harlan
February 7th, 2010 10:39pm

It doesn't matter what you do to the project or package itself. Anything you do there is like what you do to the code in your application. It's all about what "compiler" you execute it in at runtime - the 32-bit one, or the 64-bit one. If you're coding in .Net, that decision is made at runtime (assuming your code is compiled to MSIL, not native). Therefore, if your code is executed on a 64-bit system, it will run as 64-bit, and if you use the API to "LoadPackage" and "Execute" it will execute in your 64-bit process space.To ensure your package gets executed in 32-bit process space, you're going to need to ensure your app only runs in a 32-bit process space - as in compile it for 32-bit native only. Or, use System.Diagnostics.Process.Start to directly call the 32-bit DTExec application "from the command line".
Free Windows Admin Tool Kit Click here and download it now
February 8th, 2010 4:05am

Thanks for your quick reply. I did compile in x86 and added compilerOptions="/platform:x86" to my web.config, however this has to run under the same app pool as my sharepoint solution and the app pool has the setting Enable 32-Bit Applications set to false, which I assume has to be set to true for my app to work, which if I did would leave mye sharepoint returning http 500 for any request. With the setting disabled I'll be getting errors because the dlls are in the wrong format after being recompiled for x86.The reason it has to share the app pool is that it's installed in av virtual directory under my sharepoint, to make it avaiable on the same secure address. Guess I just have to change servers. Thanks again,Harlan
February 8th, 2010 5:09pm

Or just execute the package remotely using another process. Read Loading and Running a Remote Package Programmatically.
Free Windows Admin Tool Kit Click here and download it now
February 8th, 2010 7:16pm

This makes sense, however, the server I am trying to execute this package on is a 64 bit server with Sql Server 2008 installed and the business intelligence studio has been installed along with all tools. However, when I look at Program Files(X86) and sql server I can't find any folder that contains dtexec.exe. Any thoughts?
February 11th, 2010 7:55pm

Find it with Windows Explorer (Ctrl + F) in my case it is in DISC:\Program Files (x86)\Microsoft SQL Server\100\DTS\BinnDiseo de Pginas Web Desarrollo de Software www.naranjaweb.com www.gmobilesys.com
Free Windows Admin Tool Kit Click here and download it now
February 11th, 2010 9:01pm

Using Windows exploxer is a given. However, I have done so and it's not there under x86 but the full system was installed to include the business intelligence studio (visual studio) and the tools kit.
February 11th, 2010 9:07pm

You need to select a specific set of installation options to get the 32-bit runtime installed as well. Check here Considerations for Installing Integration Services - search for "32" and you'll land on the note.
Free Windows Admin Tool Kit Click here and download it now
February 11th, 2010 9:41pm

and here? DISC:\Program Files\Microsoft SQL Server\100\DTS\Binn (without x86)Diseo de Pginas Web Desarrollo de Software www.naranjaweb.com www.gmobilesys.com
February 11th, 2010 10:33pm

You did ask good question. Thank you
Free Windows Admin Tool Kit Click here and download it now
March 1st, 2010 6:30pm

Todd: I am running an SSIS 2005 package (programatically created) on a 2005 instance installed on a 64 bit box. Per your instructions in posts above, i am launching the package using the "OS Command" option of the job step and specifically calling the 32 bit version of DTEXEC....however i continue to get the "Version of component" issue that i had when i was callling the 64 bit version of DTEXEC. Any thoughts ? thanks My Command Line C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE /SQL "\EDIS_3_test19115" /SERVER "emaslcdb2\sql2005_staging" /DECRYPT EDIS /CHECKPOINTING OFF /REPORTING E Results from job history...... Executed as user: EMASSIST\z_sqlagent. ...ge Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:49:23 PM Error: 2010-04-19 14:49:24.05 Code: 0xC0048020 Source: LoadStagingData LoadStagingData (DTS.Pipeline) Description: The version of component "Excel Source" (1) is not compatible with this version of the DataFlow. End Error Error: 2010-04-19 14:49:24.06 Code: 0xC0048020 Source: ProcessStagingData ProcessStagingData (DTS.Pipeline) Description: The version of component "OLE DB Command" (64) is not compatible with this version of the DataFlow. End Error Error: 2010-04-19 14:49:24.07 Code: 0xC0048020 Source: LoadStagingData DTS.Pipeline Description: The version of component "Excel Source" (1) is not compatible with this version of the DataFlow. End Error Error: 2010-04-19 14:49:24.07 Code: 0xC0048021 Source: LoadStagingData Excel Source [1] Description: The component is m. The step failed.
April 19th, 2010 11:58pm

I believe you're still having that problem because even though you're explicitly calling the 32-bit version of DTExec, you're executing in the same process space as the 64-bit package got started in. You've got to get out of that process space. Perhaps my blog entry isn't correct in what I've written there - and you're a perfect candidate to test it out. When you're running the package, do you ever see more than one instance of DTExec pop up in Task Manager? Probably not. What you probably need to do is run a 32-bit CMD.exe, and use that to launch DTExec. On a 64-bit box, CMD.exe should be at %windir%\SysWow64. You'll need to use the "/C" argument to construct a full command line to launch DTExec with - and it will execute it asynchronously. But it will be in a different process space - a 32-bit one. If that works, let me know details so I can amend my blog post more clearly. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2010 2:59am

hmmm...no luck. Here's my command.... C:\Windows\SYSWOW64\CMD.EXE /C "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE" /SQL "\EDIS_3_test19115" /SERVER "emaslcdb2\sql2005_staging" /DECRYPT EDIS /CHECKPOINTING OFF /REPORTING E The above syntax actually didn't work from sqlagent job step...but as a test, i opened up the 32 bit command window (c:\windows\syswow64\cmd.exe) and then executed "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE" /SQL "\EDIS_3_test19115" /SERVER "emaslcdb2\sql2005_staging" /DECRYPT EDIS /CHECKPOINTING OFF /REPORTING E Then got the result below (a little cut off on the right). Basically, the same result in my prior test...complaining that the version of the components are not compatible with the data flow. Any other causes of the message, "the version of component xxxxx is not compatible with this version of the data flow", that you're aware of ? C:\>"c:\program files (x86)\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE" /SQL " EDIS_3_test19115" /DECRYPT EDIS /CHECKPOINTING OFF /REPORTING E /SERVER "emasl db2\sql2005_staging" Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:13:27 PM Error: 2010-04-19 22:13:28.11 Code: 0xC0048020 Source: LoadStagingData LoadStagingData (DTS.Pipeline) Description: The version of component "Excel Source" (1) is not compatible w th this version of the DataFlow. End Error Error: 2010-04-19 22:13:28.12 Code: 0xC0048020 Source: ProcessStagingData ProcessStagingData (DTS.Pipeline) Description: The version of component "OLE DB Command" (64) is not compatibl with this version of the DataFlow. End Error Error: 2010-04-19 22:13:28.14 Code: 0xC0048020 Source: LoadStagingData DTS.Pipeline Description: The version of component "Excel Source" (1) is not compatible w th this version of the DataFlow. End Error Error: 2010-04-19 22:13:28.14 Code: 0xC0048021 Source: LoadStagingData Excel Source [1] Description: The component is missing, not registered, not upgradeable, or m ssing required interfaces. The contact information for this component is "Excel Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corpora ion; All Rights Reserved; http://www.microsoft.com/sql/support;1". End Error Error: 2010-04-19 22:13:28.14 Code: 0xC0047017 Source: LoadStagingData DTS.Pipeline Description: component "Excel Source" (1) failed validation and returned err r code 0xC0048021. End Error Error: 2010-04-19 22:13:28.14 Code: 0xC004700C Source: LoadStagingData DTS.Pipeline Description: One or more component failed validation. End Error Error: 2010-04-19 22:13:28.14 Code: 0xC0024107 Source: LoadStagingData Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:13:27 PM Finished: 10:13:28 PM Elapsed: 0.936 seconds
April 20th, 2010 7:24am

OK - this is starting to sound more like you don't have the 32-bit SSIS actually installed. On a completely "default" server install, it isn't. It only gets installed if you install the "complete" management tools or BIDS. Have you done that? Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2010 8:11am

Todd: Funny you should mention. I am working on that today and will comment back when done. On a separate note, my project lead is looking for an SSIS consultant to review our application. Our asp.net c# app generates SSIS packages programatically based on data files imported by end users (excel, access, etc..). It builds the packages, stores them in sql server, and builds sqlagent jobs to run them. It also saves records that error out of the load process and allows users to correct data and re-load. So it's a pretty complete process with a number of complexities. My question is whether you would consider acting as our consultant, reviewing our SSIS approach and design. We've hit a number of roadblocks to-date (including this issue re: 32/64 bit) and are therefore hoping to have an experienced SSIS developer weigh in. We would like to mitigate the likelihood of future issues cropping up. Let me know if you are interested. Thanks.
April 20th, 2010 4:33pm

You were correct..after installing BIDS for 2005, job ran ok. The original OS command worked fine without having to shell out into the SYSWOW64 environment...so your original post was correct.
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2010 6:11pm

Thanks - I'll look at my blog post again to make sure that advice is somewhere in there. Thanks for the offer - but I'm not consulting at the moment. Talk to me now on
April 20th, 2010 6:48pm

If you have anyone (individual or company) you might suggest for SSIS expert consulting services, please comment back. Thank you
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2010 6:59pm

Todd: Following up on prior discussion, i'm executing the 32 bit dtexec to call my ssis package. If i call the package directly (via DTEXECUI for example), the package runs ok. However when i attempt to invoke the package from a sql server job step, i get an error . My OS Command used on the job step and the message being returned are shown below. I'm assuming it has something to do with the account under which my job step is running ? The only choice i have when setting up a job step is to run under the Sql Server Agent Service Account. I have set this account up with all the roles i would think necessary to execute DTS packages on my target database. Any ideas come to mind ? Thank You. OS Command: C:\program files (x86)\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE /SQL "EDIS_4_Test001" /DECRYPT EDIS /CHECKPOINTING OFF /REPORTING E /SERVER "emasldb2\sql2005_staging" Job Run Results Executed as user: EMASSIST\z_sqlagent. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:36:49 AM Could not load package "EDIS_4_Test001" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed. Source: Started: 7:36:49 AM Finished: 7:37:04 AM Elapsed: 15.023 seconds. Process Exit Code 5. The step failed.
April 23rd, 2010 5:22pm

The roles for the account executing the job are not relevant. The error describes that the login failed - unfortunately is says a timeout is the cause, but that's probably not it. Your likely problem is the package ProtectionLevel is set to the default of EncryptSensitiveWithUserKey, which makes the password unreadable by the job. You can either change the ProtectionLevel, or use a Proxy to execute the job under the account of the person that designed the package. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
April 23rd, 2010 7:00pm

Thank you it worked fine after changing the properities.
September 30th, 2010 9:26pm

This just worked fine for me. Excellent. Thank You.Mitul P. Suthar Web Developer and Database Analyst http://www.bsintelligence.wordpress.com
Free Windows Admin Tool Kit Click here and download it now
December 1st, 2010 9:49pm

Hello All, I'm calling my SSIS packages from my .NET 3.5 web application. I'm developing on WinXP with Office 2007 installed. I've installed my application on a 64bit Windows 2008 R2 Server but it fails with the error "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." What are my options for executing my packages in the 64bit environment from code? I am totally stuck on this and any assistance would be greatly appreciated. Mike
December 17th, 2010 12:48am

Hello All, I'm calling my SSIS packages from my .NET 3.5 web application. I'm developing on WinXP with Office 2007 installed. I've installed my application on a 64bit Windows 2008 R2 Server but it fails with the error "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." What are my options for executing my packages in the 64bit environment from code? I am totally stuck on this and any assistance would be greatly appreciated. Mike You have to find a way to run your IIS under 32bit . If not possible, then you have to investigate third-party solutions. CozyRoc provides commercial and enhanced source and destination Excel support where you can execute both under 32bit and 64bit mode. These are the relevant components: Excel Connection Manager Excel Source Plus Excel Destination Plus Excel TaskSSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 12:56am

Worked for me..Thanks!
January 27th, 2011 10:05pm

Works perfect with BIDS. Thanks for the tip.
Free Windows Admin Tool Kit Click here and download it now
April 16th, 2011 12:58am

You can also set the 32 bit runtime environment using Project Properties -> Debugging -> Run64BitRuntime = False (in Visual Studio) N A T It's work for me. Thank you.
September 22nd, 2011 3:14pm

Thanks for the note, this fixed it for me.
Free Windows Admin Tool Kit Click here and download it now
October 17th, 2011 2:39am

Thank you NAT. Few words , great help .machag01
November 19th, 2011 7:12am

Hi, you can simply do Set the Run64BitRuntime property to false.. Check the below article on how to do that and issues associated with that as well http://beyondrelational.com/blogs/jeffwharton/archive/2011/10/23/ssis-consuming-microsoft-access-or-microsoft-excel-data-sources-in-64-bit-environments.aspxThanks Karthikeyan Anbarasan http://f5debug.net/
Free Windows Admin Tool Kit Click here and download it now
November 21st, 2011 10:25am

Thanks N A T. It was so easy
December 21st, 2011 10:53pm

Thanks dacraka.. This worked for me
Free Windows Admin Tool Kit Click here and download it now
January 20th, 2012 4:45pm

Thanks Man. Worked like a magic for me. ~Pardeep
April 26th, 2012 12:00pm

Thanks for short and perfect answer.
Free Windows Admin Tool Kit Click here and download it now
July 18th, 2012 3:22am

Project Properties, Debugging,set Run64BitRuntime to false. Thanks for this answer. It worked for me perfectly. I am not happy though that this is project based. I would like a setting the remains even are logging off BIDS.
July 20th, 2012 1:53pm

Best one... I was looking for this option... thanks :)
Free Windows Admin Tool Kit Click here and download it now
August 9th, 2012 1:14am

Hi Todd I have a weird problem In my SSIS package when am making the below excel connection manager the job fails from job. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::FullPath] + ";Extended Properties=\"Excel 12.0;HDR=YES\";" The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available But when i make the excel connection as Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FolderPath] + "\\"+ @[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\; and check 32bitruntime in the job it works fine. Both cases I am able to execute the package properly from BIDS. How can I fix this problem. Thanks
August 10th, 2012 1:12pm

Not sure..but maybe this will help: http://support.microsoft.com/kb/934653- will
Free Windows Admin Tool Kit Click here and download it now
August 12th, 2012 7:31am

You can also set the 32 bit runtime environment using Project Properties -> Debugging -> Run64BitRuntime = False (in Visual Studio) N A T Thanks for the solution... :)
September 3rd, 2012 12:18pm

Hi, This site helped immediately: FAQ - How to run SSIS Packages using 32-bit drivers on 64-bit machine from prgamatic works. If your SSIS package is referencing any 32-Bit DLL or 32-Bit drivers from your package then you must use 32-Bit version of DTExec to execute SSIS package. Common example of 32-Bit driver is : Microsoft Jet Driver (MS Access or Excel). There is no 64-Bit version of Jet Driver so any packages using Jet driver (i.e. Excel or MS Access Database Connections) must be called using 32 bit DTExec. When you are designing such SSIS packages in Visual Studio and if you have reference to any 32-Bit driver/dll then make sure you change Project Property Run64BitRuntime to False before you Debug your package in BIDS otherwise your package will try to load 64-Bit dlls instead of 32-Bit. To change this setting - Right click on Project Node - Under Debugging option change Run64BitRuntime to False peonysmiles
Free Windows Admin Tool Kit Click here and download it now
September 4th, 2012 2:34pm

Thank You!
September 26th, 2012 4:29pm

Thanks, this worked for me.
Free Windows Admin Tool Kit Click here and download it now
November 15th, 2012 1:32pm

Thanks it has worked for me to.
January 21st, 2013 9:41am

I can not begin to tell you how many post I have read on this subject which do not even mention this solution. I have not read about this in my SSIS books and if Microsoft mentions it in their "documentation" it has eluded me and many others. Thanks.
Free Windows Admin Tool Kit Click here and download it now
January 28th, 2013 10:24am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics