SSIS Package Not Running as 32bit in SQL Server 2012

Question

I have a package that I developed in VS2012 (using the SQL Data Tools component) which collects data from a DBF file using the VFPOLEDB provider, and puts it into a database on a SQL Server 2012 X64 server. The project containing the package has the DebugOption of Run64BitRuntime set to false. I have imported this package to the SSIS Package Store of a test and live server (identical setups). The VFPOLEDB provider is installed on both, and I can see in the registry of both machines that it's there for 32bit runtimes.

The package runs fine on the test machine, but fails on the live machine. The live instance of SQL does not appear to recognize the 32bit VFPOLEDB provider that is installed.

The only difference in the instances of SQL is that the live environment has an Integration Service Catalog set up, where as the test does not. Looking at the logs of the servers, when the live starts, it runs sp_ssis_startup, and then logs messages about unsafe assemblies being loaded in. This SP is not run on the test environment because there is no catalog.

The jobs I create have the flag set to use the 32bit runtime, but I can't help but feel that the SSIS Catalog is having an issue with the VFPOLEDB I am using, and not loading it.

I don't really know anything about the SSIS catalog, so is anyone able to suggest any direction I could move in?

Update: Here is my job step configuration. The 32bit runtime flag is set.

Update #2:

1. OLEDB provider is installed appropriately.
2. The same version of the provider is installed on both machines.
3. The OBDCAD32.exe shows the same versions of the VFPOLEDB provider. No DSNs are defined on either machine. My local machine does have DSNs defined, so I will try adding one in for dBASE files and see if that helps.
4. In the process of trying this step now. I am looking for a way to use the dtexec tool without having it create the SSISDB catalog. Although I did remove the existing SSIS catalog and stopped sp_ssis_startup being executed on service startup. I saw no log entries about an unsafe assembly, but the job still failed with the same error as it always has. I will report back on 4 and possibly further ask for further guidance.

Update #3:
I have just checked and the test and live environments are not as identical as I had originally stated. The live server does not have the 32bit version of dtexec.exe (although I didn't think this would matter as TechNet says that jobs run with SQL Server Agent will always use the 64bit version. I think I used an x86 and i64 ISO to set up the test environment, but a 64bit only version for live. Changing this would require, I'd imagine, uninstalling the Integration Services shared component from the live box and reinstalling it with the dual iso.

I guess setting the "Use 32bit runtime" option only works if there is a 32bit version to use? That might explain things.

Clerihew

Post your job step definition. If you've indicated that the job step should use the 32 bit version, that's the dtexec that will be called – Timon

Ahhhhh, I saw the SQL Server 2012 tag and assumed you were using the Project Deployment Model and therefore, your package was deployed to the SSISDB. Had I looked harder at your SQL Agent picture, that should have been apparent. Or reading for comprehension "SSIS Package Store" Mea culpa – Timon

Answer 1

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.

Timon

I appreciate the thorough explanation. I did state in my original question that the 32bit runtime flag was being used to ensure that a 32bit assembly could be used though. – Clerihew

@AdamH Perfect, then unless you're doing something like starting another package from within the same package using an external process call, there's no opportunity for the "bittedness" to change from 32 back to 64 bit. Added more things to check to identify and hopefully resolve your issue – Timon

I have added Update #2 in response to your 4 steps. – Clerihew

I have added Update #3 with something step 4 helped me identify. It may be something, it may be nothing. Do you have an opinion on it? I am still working on running it from the command line as users of varying permission levels. – Clerihew

I installed the SQL Data Tools component so there was a 32bit version of dtexec.exe to run (which I assume is what it was trying to do with the execution option flag set) and it ran fine. I am guessing that in absence of the 32bit, it was ultimately running it with the only version it had, the 64bit. Thanks for the guidance and help. – Clerihew

Answer 2

If you want to run a package from a 64-bit SQL Server Agent job in 32-bit mode, select a job step type o-->Operating system, and enter a command line or use a batch file that invokes the 32-bit version of dtexec.exe

Command: CD "C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\" DTExec.exe /f "C:\Download\Root\SQL Package.dtsx"

or Command: "C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTExec.exe" /FILE "C:\Download\Root\SQL Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT

Note: I was using SQL 2014 64 bit version.

Mccallum