By default, everything will run in 64 bit on the servers. To change this behaviour, you need to indicate that the 32bit version of dtexec should be used. For the 2012 SSISDB, we have two easy ways of invoking our packages: SQL Agent and the catalog.start_execution method.

catalog.start_execution

For single serving package runs, you can find the package in the SSISDB catalog and right click on them to Execute...

In the resultant pop up dialog, you will need to go to the Advanced tab and check the 32-bit runtime box. This would be done on each run of the package.

Behind the scenes, the SQL that wizard generates would look like

DECLARE @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
    @package_name = N'Package.dtsx'
,   @execution_id = @execution_id OUTPUT
,   @folder_name = N'POC'
,   @project_name = N'SSISConfigMixAndMatch'
,   @use32bitruntime = True
,   @reference_id = NULL
SELECT
    @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id
,   @object_type = 50
,   @parameter_name = N'LOGGING_LEVEL'
,   @parameter_value = @var0
EXEC [SSISDB].[catalog].[start_execution]
    @execution_id
GO

As you can see, the @use32bitruntime parameter is passed a value of True to indicate it should run in 32 space.

SQL Agent

For recurring package runs, we generally use a scheduling tool. To get to the 32bit setting for a package in agent, it’s basically the same click path except you first need to click on the Configuration tab and then click on the Advanced tab to select 32-bit runtime

The job step definition would look something like

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Do it'
,   @step_name = N'Run in 32bit'
,   @step_id = 1
,   @cmdexec_success_code = 0
,   @on_success_action = 1
,   @on_fail_action = 2
,   @retry_attempts = 0
,   @retry_interval = 0
,   @os_run_priority = 0
,   @subsystem = N'SSIS'
,   @command = N'/ISSERVER "\"\SSISDB\POC\SSISConfigMixAndMatch\Package.dtsx\"" /SERVER "\".\dev2014\"" /X86 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'
,   @database_name = N'master'
,   @flags = 0

You’ll see that in the @command call, the wizard generates the /X86 call which is the special argument reserved for SQL Agent (check the BOL link in the beginning) to indicate whether the 32 or 64 bit version of dtexec should be used. A command line invocation would require us to explicitly use correct dtexec. By default, the 64 bit dtexec will be listed first in your PATH environment

64 bit dtexec locations

• C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe
• C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
• C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe
• C:\Program Files\Microsoft SQL Server\120\DTS\Binn\DTExec.exe

32 bit dtexec locations

• C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe
• C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
• C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe
• C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTExec.exe

Further troubleshooting drivers

It runs on one server, doesn’t on another.

Step 1 – verify you’ve installed the drivers. Silly, obvious but there have been many questions where people mistakenly thought deploying an SSIS package/.ispac would also deploy all the referenced assemblies. It’s not nuget so no, all the prerequisites would need to be installed, and installed properly (seen people try to copy assemblies into the GAC instead of using the tool)

Step 2 – verify the driver installation matches across servers. Again, seems obvious but I’ve experienced pain, generally VS_NEEDSNEWMETADATA, on a point difference in driver version version 4.0.2.013 produced different results than 4.0.2.014

Step 3 – Ensure that any DSNs you have defined were defined in the correct space. This one bites people for a number of reasons. I think it wasn’t until Server 2012 that you could only get to the 32bit version of odbcad32.exe (executable related to Administrative Tools -> Data Sources (ODBC)) was by finding it on the file system. All the more confusing is the executable is named odbcad32.exe regardless of whether it’s in System32 or SysWOW64 and those two folders are for the 64 bit drivers and 32 bit drivers respectively. Yes, future readers, that is not a typo. The 64 version of applications are in System32, the 32 bit versions are in SysWOW64. It was a design decision intended to minimize impact.

On the test and live server, run C:\Windows\SysWOW64\odbcad32.exe Find your FoxPro drivers and the related DSNs, are they as expected?

Step 4 – Weird permission check. Log on to both servers as a “normal” account and run the package from the command line. Repeat this step but execute it using Agent, with whatever proxy you may or may not have defined. If the first works but the latter fails, that usually indicates a permission issue. It could be that the SQL Server or Agent account can’t access whatever folder the driver was installed to. It might be that said account needs the InteractWithDesktop permission or some other permission that is denied or not explicitly granted.