THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Running 32-bit SSIS in a 64-bit Environment

After my recent post on where to find the 32-bit ODBC Administrator on a 64-bit SQL Server, a new question was asked about how to get SSIS to run with the 32-bit ODBC instead of the 64-bit ODBC. You need to make a simple configuration change to the properties of your BIDS solution. Here I have a solution called 32bitODBC and it needs to run in 32-bit mode, not 64-bit mode. Since I have a 64-bit SQL Server, BIDS defaults to using the 64-bit runtime. To override this setting, go to the property pages for the solution. Select the Debugging node. Select Run64BitRuntime and set it to False. Save your changes.

image

What about when you finish your work in BIDS and you want to use the 32-bit runtime outside of BIDS? It depends on where you execute your package from.

If you double-click a dtsx file from Windows Explorer, it is executed by the SQL Server 2008 Integration Services Package Execution Utility. The default fully qualified path for that tool is C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe. In other words, it is a 32-bit product, so it doesn’t have any trouble running things in 32-bit mode by definition.

Here’s part of the error message generated from executing the package from a SQL Server Agent job using the default settings:

Description: System.Data.Odbc.OdbcException: ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

The clue is the part about the architecture mismatch. Using the 64-bit runtime is the default behavior. Fortunately this is easy to change within the job definition by checking the Use 32 bit runtime checkbox.

image

Published Wednesday, March 24, 2010 10:54 PM by John Paul Cook

Comments

 

Todd McDermid said:

Valid tip - but it only applies to forcing 32-bit execution when the package is run inside BIDS.  That property applies to the project - and when packages are run via DTExec, project level properties don't apply.  See http://toddmcdermid.blogspot.com/2009/10/quick-reference-ssis-in-32-and-64-bits.html for more info...

March 25, 2010 1:58 AM
 

John Paul Cook said:

Todd, thank you so much for calling this to my attention. I thought I had this covered in my previous post and even told a coworker that I had done so. But I went back and realized that although I added a mention about using the checkbox, I had not actually posted the change back to the server. Oops. As you can see, I decided to post the additional explanation here and added a screen capture.

March 25, 2010 2:25 PM
 

Todd McDermid said:

Excellent!  I need to use more screenshots in my posts - they illustrate the points much better.

March 30, 2010 6:36 PM
 

Peter Florenzano said:

Hello John,

We have a situation where we have a SQL Server 2008 SSIS package x64 that needs to be executed in 32bit runtime because the package is using MS Excel.  With that said, the Run64BitRuntime value was set to false, and the 'Use 32 bit runtime' was set and we are still having issues executing this package.  Below is the error we are receiving:

Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8007007E.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x8007007E  Description: "The specified module could not be found."

I've done extensive research on this, and I'm failing to come up with a resolution.  Any suggestions would be much appreciated.

Thank you!

Peter

May 4, 2010 11:44 AM
 

John Paul Cook said:

The best place for you to post your question is in the SSIS Forum found here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/threads

May 4, 2010 12:09 PM
 

karam said:

I have a 32bit-SSIS Package. I want to deploy this Package on 64-Bit System. How can I do this?

August 25, 2010 6:03 AM
 

karam said:

Respectively can I update the package in BIDS so that the package run on the 64-Bit Machine?

August 25, 2010 6:19 AM
 

John Paul Cook said:

As previously posted, getting help for specific technical questions is best handled in the SSIS forum: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/threads

It is more suited for back and forth discussions.

August 26, 2010 10:23 PM
 

clg003 said:

This checkbox is not available in 2005. Is there a way to get the package to run thru a job under 2005. I can get the package to run in the designer but while in the design it trys to run it as 64 bit and therefore cannot find the odbc config.

August 30, 2010 12:49 PM
 

John Paul Cook said:

Using a search engine, I found this post http://sqlblogcasts.com/blogs/lawrenso/archive/2009/06/18/64-bit-sql-server-and-scheduling-ssis-packages.aspx describing how to force a scheduled job to use the 32-bit runtime.

As previously posted, the best place to post SSIS questions is in the SSIS Forum found here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/threads

I've disabled comments on this post so that technical support questions are posted in the Microsoft SSIS Forums where they will have a larger audience, both of readers and answerers.

August 30, 2010 4:20 PM
New Comments to this post are disabled

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement