By default, everything will run in 64 bits on the servers. To change this behavior, you need to specify that you use the 32-bit version of dtexec . For 2012 SSISDB, we have two easy ways to call our packages: the SQL agent and the catalog.start_execution method.
catalog.start_execution
For single package packages, you can find the package in the SSISDB directory and right-click on them until Execute...
In the pop-up dialog that appears, you need to go to the "Advanced" tab and select the 32-bit runtime check box. This will be done each time the package is launched.
Behind the scenes, the SQL created by the wizard will 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, @use32bitruntime is set to True to indicate that it should work in 32 spaces.
SQL Agent
For repeated runs of packages, we usually use a planning tool. To go to the 32-bit configuration of the package in the agent, this is basically the same path to the click, except that you first need to click the "Configuration" tab, and then go to the "Advanced" tab to select 32-bit runtime
The definition of the job step will look something like this:
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 will see that in the @command call, the wizard generates a /X86 call, which is a special argument reserved for the SQL agent (check the BOL link at the beginning) to indicate whether the 32 or 64 bit version should be used dtexec. Invoking the command line will require explicit use of the correct dtexec. By default, 64-bit dtexec will be listed first in your PATH environment
Dtexec 64-bit 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
Dtexec 32-bit 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
Additional troubleshooting drivers
It runs on one server and not on another.
Step 1 - Make sure you install the drivers. Stupid, obviously, but there were many questions on which people mistakenly thought that deploying the SSIS / .ispac package would also deploy all referenced assemblies. This is not nuget, so no, all prerequisites must be installed and installed correctly (people try to copy assemblies in the GAC instead of using this tool)
Step 2 - check that the driver installation matches all servers. Again, it seems obvious, but I experienced pain, as a rule, VS_NEEDSNEWMETADATA, with a difference in points in the driver version version 4.0.2.013, different results were obtained than 4.0.2.014
Step 3 - Verify that all the DSNs you have assigned are defined in the right place. This person bites people for a number of reasons. I think that only until Server 2012 you could get only the 32-bit version of odbcad32.exe (executable file related to administrative tools → Data Sources (ODBC)) by searching for it in the file system. Even more confusing is the executable file called odbcad32.exe, regardless of whether it is located in System32 or SysWOW64, and these two folders are for 64-bit drivers and 32-bit drivers, respectively. Yes, future readers, this is not a typo. 64 versions of applications are in System32, 32-bit versions are in SysWOW64. It was a constructive solution designed to minimize impact.
On a test and live server, run C:\Windows\SysWOW64\odbcad32.exe Find the FoxPro drivers and their associated DSNs, are they as expected?
Step 4 - Verify the true permission. Log in to both servers as a “regular” account and run the package from the command line. Repeat this step, but do it using the agent, with any proxy server that you might or could not determine. If the former works, but the latter does not work, this usually indicates a resolution problem. Perhaps the SQL Server or Agent account cannot access any folder in which the driver was installed. This account might require InteractWithDesktop permission or some other permission that was denied or not explicitly granted.