Quick
Reference: SSIS in 32- and 64-bits
There
are quite a few misconceptions flying about out there regarding SQL Server
Integration Services and 64 bitness. I've had to set more than a few
people on the right path in the forums - mostly on one particular setting
inside the SSIS packages that is getting misinterpreted. Of course, this
information only applies to 64-bit architectures - if you are running a Windows
32-bit OS, you have no choice - your packages will always run in 32-bit mode.
Why
would you want to run in 32-bit mode if you have a 64-bit system?
Drivers, mostly. I'm not referring to hardware drivers, but data
providers. As an example, Excel and some versions of Oracle don't have
64-bit providers. So for the ever-increasing base of 64-bit users, here's
the skinny on executing Integration Services packages in your choice of
64-bit or 32-bit mode.
Background
I Can Design Fine, Why Won't It Run?
A
little background to start. Business Intelligence Design Studio (BIDS) is
a 32-bit application. When you're designing your package, you're using
32-bit facilities - and have no choice in the matter. When you
execute your package using DTExec, you have the option of 32-bit or 64-bit
operation - but the default on a 64-bit installation is to use 64-bit mode
(obviously). However, some commonly used objects in SSIS don't have
64-bit counterparts, and will therefore cause your packages to fail.
Unfortunately,
it usually doesn't say anywhere in these messages that the fault lies with 32
vs 64 bits. It's usually something like:
·
0x80040154
... Class Not Registered
·
The
AcquireConnection method call to the connection manager XXX failed with
error code 0xC0202009
·
0xC00F9304
... SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED
·
The
OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered
(I
include those sample errors here in the hope that those searching the web may
find this article!)
Why Do I Want 32-bit Mode?
The
most common reason to want 32-bit mode in an executing SSIS package is the
Excel Provider. It's currently not available for 64 bits, and will
cause your package to crash. (Office 14 (2010) is reported to have
64-bit support - even though it's not supported side-by-side with
32-bit.) This applies to the other Office providers as well - Access,
specifically - and to several other third party drivers and providers (like
Oracle). They simply will not work in a 64-bit environment
(pre-2010). You
may also wish to run Execute DTS 2000 Package Tasks - and those can only run in
32-bit mode as well.
It
Depends How You're Executing Your Package
There
are many ways to execute an SSIS package - and this is the primary determiner
of whether you're running it in 64-bit or 32-bit mode. So pick your
execution environment from the list below, and read up on how to force the
bitness you desire.
Choosing
Bitness Inside Business Intelligence Development Studio (BIDS)
If
you're running your package inside BIDS, the setup is simple unless
you're using the Execute Package Task or Execute Process Task to run child
packages.
The
package you currently have open will (by default) run in 64 bit
mode. The setting that controls this is a property on
the project called Run64BitRuntime. To access this property,
right-click on the Integration Services project in your solution explorer and
select Properties. Then select the Debugging node in the editor.
The default here is "true", which means all the packages in this
project will run in 64-bit mode. If you change this to "false",
all the packages will be run in 32-bit mode.
Special Note: Execute Package Task
Any
child packages executed via the Execute Package Task will run in the same mode as
the parent, regardless of the Run64BitRuntime setting of the project that the
child package belongs to, regardless of the setting of
ExecuteOutOfProcess. This means that even if your child package has
Run64BitRuntime set to false in the project you designed it in,
it will be executed in 64-bit mode within BIDS if your parent package's
Run64BitRuntime property istrue.
Special Note: Execute Process Task
The
Execute Process Task can allow you to choose 32-bit mode independently of the
settings in the parent package, at the expense of running the child
package in another process. As with the SQL Agent methods
described later, you can specifically identify the 32-bit DTExec to run SSIS
child packages in 32-bit mode (see below).
Choosing
Bitness With SQL Agent
Instructing
SQL Agent what environment you want your packages to run in is simple in
Integration Services 2008. SSIS 2005 makes you jump through a few more
hoops.
Integration Services 2008
In
the Agent Job Step Properties, you'll be using the SQL Server Integration
Services Package type of step. If you go to the Execution Options tab,
you'll see an option to "Use 32 bit runtime" down at the bottom.
Integration Services 2005
With
SQL 2005, you can not use the Integration Services Package type of job step to
run an SSIS package in 32-bit mode. Your recourse is to use the Operating
System type of job step, and refer to the 32-bit version of DTExec specifically
in the command line that you use, and manually specify arguments to DTExec.
Hurdle #1 - Finding the 32-bit DTExec
Finding
the executable shouldn't be difficult. In a standard 64-bit installation,
the 32-bit DTExec.EXE should be located in the "\Program Files
(x86)\Microsoft SQL Server\90\DTS\Binn" folder. It's plainly called
"DTExec.EXE", and doesn't identify itself in any way as a 32-bit app
- you'll have to "know" it is by it being located in the
32-bit folder. (Or you could try to execute it and watch Task
Manager.) If you've installed SQL to a non-standard location, you may
have to hunt a little. If you can't find it at all, you may not have
installed the 32-bit components on your 64-bit machine. During the
install of SQL Server, if you only selected "Integration Services"
and didn't install "Business Intelligence Development Studio" OR
"Management Tools - Complete", then you won't have the 32-bit DTExec
installed. You'll have to run SQL Setup, and install one of those
options.
Hurdle #2 - Determining the Command Line
Arguments
Next,
you need to determine the command line parameters you need to operate
DTExec from the command line. You could read through the documentation
and attempt to determine the arguments and settings by yourself, but I would
recommend you use the power of the included GUI tools. Both the IS Job
Step in SQL Agent, and the DTExecUI tool provide a GUI to configure an SSIS
package run. On the last page of the GUI, it very helpfully places the
exact command line arguments needed to run DTExec, based on all of the
configuration options you've chosen on the ten or so other tabs of the
GUI. Leverage that! Set up your package execution using the
GUI, then copy the arguments off that last page.
Precompiled
Scripts
This
is only an issue in Integration Services 2005 - the dev team completely fixed
this issue in SSIS 2008. There is a "Precompile" option on
Script Tasks, which is set to "true" by default. If this has
somehow been set to "false", your packages may not execute in a
64-bit environment.
32-bit
ODBC Drivers
There
is also one other oddity with using 32-bit ODBC drivers in Windows - at least
in Server 2003, 2008, Vista, and Windows 7 64-bit OSes. The first step to
using those drivers is to set up a DSN to handle storing the server name and
other particulars. In my experience, the natural first place to start is
by opening the "Data Sources" applet in the Control Panel.
That's mistake #1 - because that applet only manages 64-bit drivers. You
won't see yours listed at all. The next step is to poke around and
realize that there's a "Data Sources (32-bit)" applet there in the
Control Panel as well. That's mistake #2 - but not your mistake. In
my experience, this icon leads to some odd hybrid 32/64 bit management
utility. It definitely did NOT manage my 32-bit sources. If you
fire it up, then look at the processes tab in Task Manager, you'll see a
process labeled "odbcad32.exe"... but you'll notice that it doesn't
have the "*32" after it denoting that it's a 32-bit app. Whatever
it's attempting to manage, it's not the 32-bit ODBC drivers. What you
need to do is navigate to another odbcad32.exe
that's sitting in your SYSWOW64 folder. That ODBC data source
administrator truly does manage 32-bit drivers, in
a 32-bit process.
References/Resources
Most
(if not all) of this information is also distilled in an MSDN article: 64-bit Considerations for
Integration Services. If you find other useful articles,
pointers, or mistakes in the above, please post a comment.
Some
other very useful articles:
How To: Run a Package,
MSDN
64-bit references within an
SSIS Script Component by Michael Entin, Microsoft SSIS Dev.
64-bit Considerations for SQL
Server Integration Services by Douglas Laudenschlager,
Microsoft SSIS Dev.
Where is my app.config for
SSIS? by Darren Green, SQL Server MVP.
Todd
McDermid
No comments:
Post a Comment