SSIS 包未在 SQL Server 2012 中以 32 位运行 [英] SSIS Package Not Running as 32bit in SQL Server 2012
问题描述
我有一个在 VS2012 中开发的包(使用 SQL Data Tools 组件)，它使用 VFPOLEDB 提供程序从 DBF 文件中收集数据，并将其放入 SQL Server 2012 X64 服务器上的数据库中.包含包的项目将 Run64BitRuntime 的 DebugOption 设置为 false.我已将此包导入到测试和实时服务器(相同设置)的 SSIS 包存储中.VFPOLEDB 提供程序安装在两台机器上，我可以在两台机器的注册表中看到它用于 32 位运行时.
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.
该包在测试机器上运行良好，但在现场机器上运行失败.SQL 的实时实例似乎无法识别已安装的 32 位 VFPOLEDB 提供程序.
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.
SQL 实例的唯一区别是实时环境设置了集成服务目录，而测试没有.查看服务器的日志，live开始时运行sp_ssis_startup，然后记录加载不安全程序集的消息.这个SP没有运行在测试环境中，因为没有目录.
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.
我创建的作业已将标志设置为使用 32 位运行时，但我不禁感到 SSIS 目录在我使用的 VFPOLEDB 上存在问题，并且没有加载它.
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.
我对 SSIS 目录一无所知，所以有人能够建议我可以进入的任何方向吗?
I don't really know anything about the SSIS catalog, so is anyone able to suggest any direction I could move in?
更新:这是我的工作步骤配置.设置了 32 位运行时标志.
Update: Here is my job step configuration. The 32bit runtime flag is set.
更新#2:
- OLEDB 提供程序已正确安装.
- 两台机器上都安装了相同版本的提供程序.
- OBDCAD32.exe 显示了相同版本的 VFPOLEDB 提供程序.两台机器上都没有定义 DSN.我的本地机器确实定义了 DSN，所以我会尝试为 dBASE 文件添加一个，看看是否有帮助.
- 现在正在尝试这一步.我正在寻找一种方法来使用 dtexec 工具而无需创建 SSISDB 目录.尽管我确实删除了现有的 SSIS 目录并停止了在服务启动时执行的 sp_ssis_startup.我没有看到有关不安全程序集的日志条目，但该作业仍然失败，并出现与往常一样的错误.我将在 4 日汇报，并可能进一步寻求进一步指导.
更新 #3:
我刚刚检查过，测试环境和现场环境并不像我最初所说的那样完全相同.实时服务器没有 32 位版本的 dtexec.exe(虽然我认为这并不重要，因为 TechNet 说使用 SQL Server Agent 运行的作业将始终使用 64 位版本.我想我使用了 x86 和 i64 ISO 来设置测试环境，但是只有 64 位的实时版本.我想改变这需要从实时框中卸载集成服务共享组件并使用双 ISO 重新安装它.
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.
我想设置使用 32 位运行时"选项仅在有 32 位版本可用时才有效?这或许可以解释一些事情.
I guess setting the "Use 32bit runtime" option only works if there is a 32bit version to use? That might explain things.
推荐答案
默认情况下，一切都将在服务器上以 64 位运行.要更改此行为，您需要指明 dtexec 的 32 位版本应该使用.对于 2012 SSISDB，我们有两种调用包的简单方法:SQL 代理和 catalog.start_execution 方法.
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.
对于单服务包运行，您可以在 SSISDB 目录中找到包并右键单击它们以执行...
For single serving package runs, you can find the package in the SSISDB catalog and right click on them to Execute...
在出现的弹出对话框中，您需要转到高级选项卡并选中32-bit runtime 框.这将在每次运行包时完成.
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.
在幕后，向导生成的 SQL 看起来像
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
如您所见，@use32bitruntime 参数传递了一个 True 值，以指示它应该在 32 位空间中运行.
As you can see, the @use32bitruntime parameter is passed a value of True to indicate it should run in 32 space.
对于重复运行的包，我们通常使用调度工具.要在代理中获得包的 32 位设置，它基本上是相同的单击路径，除了您首先需要单击配置"选项卡，然后然后单击高级"选项卡以选择 32-bit运行时
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
作业步骤定义类似于
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 ""SSISDBPOCSSISConfigMixAndMatchPackage.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
您将看到，在@command 调用中，向导生成了 /X86 调用，这是为 SQL Agent 保留的特殊参数(检查开头的 BOL 链接)以指示是否应该使用 32 或 64 位版本的 dtexec.命令行调用需要我们明确使用正确的 dtexec.默认情况下，64 位 dtexec 将首先列在您的 PATH 环境中
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
- C:Program FilesMicrosoft SQL Server90DTSBinnDTExec.exe
- C:Program FilesMicrosoft SQL Server100DTSBinnDTExec.exe
- C:Program FilesMicrosoft SQL Server110DTSBinnDTExec.exe
- C:Program FilesMicrosoft SQL Server120DTSBinnDTExec.exe
- C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe
- C:Program Files (x86)Microsoft SQL Server100DTSBinnDTExec.exe
- C:Program Files (x86)Microsoft SQL Server110DTSBinnDTExec.exe
- C:Program Files (x86)Microsoft SQL Server120DTSBinnDTExec.exe
它在一台服务器上运行，不在另一台服务器上运行.
It runs on one server, doesn't on another.
第 1 步 - 确认您已安装驱动程序.愚蠢，显而易见，但有很多问题，人们错误地认为部署 SSIS 包/.ispac 也会部署所有引用的程序集.它不是 nuget 所以不，所有先决条件都需要安装并正确安装(看到有人尝试将程序集复制到 GAC 而不是使用该工具)
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)
第 2 步 - 验证跨服务器的驱动程序安装是否匹配.同样，似乎很明显，但我经历过痛苦，通常是 VS_NEEDSNEWMETADATA，驱动程序版本 4.0.2.013 的点差异产生了与 4.0.2.014 不同的结果
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
第 3 步 - 确保您定义的任何 DSN 都定义在正确的空间中.这个咬人的原因有很多.我认为直到 Server 2012，您才能通过在文件系统上找到它才能获得 odbcad32.exe 的 32 位版本(与管理工具相关的可执行文件 -> 数据源 (ODBC)).更令人困惑的是，无论是在 System32 还是 SysWOW64 中，可执行文件都被命名为 odbcad32.exe，这两个文件夹分别用于 64 位驱动程序和 32 位驱动程序.是的，未来的读者，这不是错别字.64 位版本的应用程序在 System32 中，32 位版本在 SysWOW64 中.这是一个旨在最小化影响的设计决策.
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.
在测试和实时服务器上，运行 C:WindowsSysWOW64odbcad32.exe 找到您的 FoxPro 驱动程序和相关的 DSN，它们是否符合预期?
On the test and live server, run C:WindowsSysWOW64odbcad32.exe Find your FoxPro drivers and the related DSNs, are they as expected?
第 4 步 - 奇怪的权限检查.以普通"帐户登录到两台服务器并从命令行运行程序包.重复此步骤，但使用代理执行它，使用您可能定义或可能未定义的任何代理.如果第一个工作但后者失败，这通常表明权限问题.可能是 SQL Server 或代理帐户无法访问驱动程序安装到的任何文件夹.可能是该帐户需要 InteractWithDesktop 权限或其他一些被拒绝或未明确授予的权限.
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.
这篇关于SSIS 包未在 SQL Server 2012 中以 32 位运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
