Sunday, 9 September 2012

Switch package from 64bit to 32bit

Case
My Data Flow Task with an Excel connection doesn't run. It throws the following error:

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.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS 2008 error example (gives poor hints)

Error: 0xC0209303 at GetDataFromExcel, Connection manager "Excel Connection Manager": The requested OLE DB provider Microsoft.Jet.OLEDB.4.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".
Error: 0xC020801C at Data Flow Task, Excel Source [8]: 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.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: Excel Source failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS 2012 error example (gives better hints)


Solution
If you have a 64bit machine, with connections that only support 32bit (like Excel, Access and old ODBC connections) you will get an error like above. The solution is to run your package in 32bit mode. This can be done within Visual Studio for debugging or within SQL Server Management Studio for scheduled packages.

This solution will also allow you to debug a Script Task, because that also doesn't work in 64bit mode.

BIDS / Visual Studio
Running in 32bit mode is a Project Property. Setting it will affect all packages within the project.
Right Click the SSIS project and select Properties in the context menu. Go to the Debugging pane and select false under Run64bitRuntime.
SSIS 2008

SSIS 2012





























SQL Server Management Studio
Edit your job and then edit the right jobstep. Go to the Execution Options pane and check "Use 32 bit runtime". This property will only effect the package (+child packages) called in this jobstep.
SSMS - Use 32 bit runtime
























32 bit problems
Besides running 64 bit problems there are also 32 bit problems. For example, Fuzzy Lookup will not run in 32 bit. It opens SQLDUMPER.EXE in a command line / dos box but with out errors. The solution for that is to run in 64 bit mode. So running an Excel source and a Fuzzy Lookup within the same package could be a bit of a challenge.

C:\Program Files (x86)\Microsoft SQL Server 100\Shared\\SQLDUMPER.EXE






















Note: For 32bit execution via dtexec.exe you need to pick the right folder:
32bit => C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
64bit => C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe

Note 2: if you use 32bit then the process can use less memory!
Geplaatst door Joost van Rossum
Labels: , ,

27 comments:

  1. Unknown8 October 2012 at 05:29

    I am new to BI.
    From the solution here I see that we change the properties at the project level in BIDS. This changes properties of all the packages in that project.
    Is there any way where we can change the properties of the packages in BIDS only to run in 32 bit mode?

    ReplyDelete
    Replies
    1. Joost van Rossum8 October 2012 at 07:31

      No it's always on project level within BIDS. If you want it for one specific package, then you can create a separate project within your solution for 32 packages.

      Delete
  2. Anonymous30 November 2012 at 00:12

    This worked like a charm! Thanks!

    ReplyDelete
  3. Anonymous14 December 2012 at 02:20

    Just what I needed. Pulling my hair out until I spotted this.

    ReplyDelete
  4. Anonymous26 March 2013 at 16:50

    Thank you!

    ReplyDelete
  5. Anonymous29 April 2013 at 06:04

    Thank you so much for taking the time to document this. By far the best information I could find and fixed the problem immediately.

    ReplyDelete
  6. Anonymous22 May 2013 at 04:52

    Thank you very much.

    ReplyDelete
  7. Anonymous27 May 2013 at 15:19

    Thank you.. This helped..

    ReplyDelete
  8. Unknown2 July 2013 at 18:34

    Visual Studio 2010 works fine when running on 32 bit, but SQL Server 2012 does not work fine on 32 bit with excel destination. What are your thoughts?

    ReplyDelete
    Replies
    1. Joost van Rossum8 July 2013 at 07:56

      What's the Excel version? Which errors do you get? Perhaps post you question here: SSIS MSDN forum

      Delete
  9. Anonymous31 August 2013 at 19:58

    thanks!!

    ReplyDelete
  10. Unknown6 November 2013 at 18:28

    This saved the day- THANK YOU!

    ReplyDelete
  11. Anitha12 November 2013 at 19:55

    Thanks . Helped me a lot!!!

    ReplyDelete
  12. Unknown26 March 2015 at 04:19

    Awesome stuff! Thank you very much - you just made my day!

    ReplyDelete
  13. Unknown24 July 2015 at 05:29

    Thank you

    ReplyDelete
  14. Unknown24 August 2015 at 02:05

    Thank you very much! Worked like a charm.

    ReplyDelete
  15. ubifriends4 November 2015 at 18:42

    Thank you very much

    ReplyDelete
  16. Unknown6 January 2016 at 19:15

    Thank you so much , It is working fine :)

    ReplyDelete
  17. Yanda Panda4 June 2016 at 00:59

    Thank you! The 32-bit checkbox in SSMS was eluding me!

    ReplyDelete
  18. Unknown10 October 2016 at 16:24

    Hi,
    I was looking everywhere for this solution
    I also think that the line
    32bit => C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
    should be
    32bit => C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe

    in my system 32bit version of dtexec.exe is located in a different than article's folder

    thank you

    ReplyDelete
  19. Unknown19 January 2017 at 15:19

    Thank you a lot. This is for VS2015
    From Solution Explorer. Right click on the Name of the package SSIS,
    not the the solution 'SSIS'(1 project...)

    I worked perfect.

    ReplyDelete
  20. Frankmontana8822 February 2017 at 21:37

    Thanks for this very valuable info, you saved my life, I wasted more than 1 day of work trying to solve this. :)

    ReplyDelete
  21. Dharma Reddy28 September 2017 at 16:42

    Thank you for the post, It saved me lot of time.

    ReplyDelete
  22. Syed Shahid Hussain21 November 2017 at 14:57

    thanks a lot man :)

    ReplyDelete
  23. Sam BI29 October 2019 at 23:06

    Thanks a lot

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Subscribe to: Post Comments (Atom)
Related Posts Plugin for WordPress, Blogger...