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

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.UPDATE: Read this for the full story - SSIS in 32- and 64-bits.

You can also set the 32 bit runtime environment using Project Properties -> Debugging -> Run64BitRuntime = False (in Visual Studio) N A T

That property ONLY APPLIES AT DESIGN TIME. It is ignored by DTEXEC.Search for "Run64BitRuntime" on these pages:Integration Services in Business Intelligence Development Studio Connectivity and SQL Server 2005 Integration Services Integration Services Considerations on 64-bit Computers

Dude this is the answer I was looking for - worked like a charm :)

This is the winning answer! Thank you.

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

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

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".

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

Or just execute the package remotely using another process. Read Loading and Running a Remote Package Programmatically.

thanks NAT this work for me "You can also set the 32 bit runtime environment using Project Properties -> Debugging -> Run64BitRuntime = False (in Visual Studio)"Diseo de Pginas Web Desarrollo de Software www.naranjaweb.com www.gmobilesys.com

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?

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

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.

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.

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

You did ask good question. Thank you

You did ask good question. Thank you

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.

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

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

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

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.

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.

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

If you have anyone (individual or company) you might suggest for SSIS expert consulting services, please comment back. Thank you

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.

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

Thank you it worked fine after changing the properities.

This just worked fine for me. Excellent. Thank You.Mitul P. Suthar Web Developer and Database Analyst http://www.bsintelligence.wordpress.com

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

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/

Worked for me..Thanks!

Works perfect with BIDS. Thanks for the tip.

Hi foxjazz2, please have a look at my blog entry: SSIS Goodie #1: Excel + SSIS + 64 bit = DTS_E_OLEDB_EXCEL_NOT_SUPPORTED ? Best regards Daniel