为什么我的 ODBC 连接在 Visual Studio 中运行 SSIS 加载时失败，但在使用执行包实用程序运行相同的包时却没有 [英] Why does my ODBC connection fail when running an SSIS load in Visual Studio but not when running the same package using Execute Package Utility

问题描述

我正在 SSIS 2012 中处理数据集市加载包.尝试在 Visual Studio 中执行包时出现此错误:

I'm working on a Data Mart loading package in SSIS 2012. When attempting to execute the package in Visual Studio I get this error:

"AcquireConnection 方法调用连接管理器数据Warehouse.ssusr 失败，错误代码为 0xC0014009".

"The AcquireConnection method call to the connection manager Data Warehouse.ssusr failed with error code 0xC0014009".

当我测试 Connection Manager Data Warehouse.ssusr 的连通性时，我发现它通过了.

When I test the connectivity of the Connection Manager Data Warehouse.ssusr I see that it passes.

当我使用执行包实用工具在 Visual Studio 之外执行包时，包会运行.

When I execute the package outside of Visual Studio using the Execute Package Utility, the package runs.

我不明白发生了什么.

该包也拒绝使用 SQL Server 作业计划运行，如果这与任何事情有关.

The package also refuses to run using the SQL Server Job Schedule, if that has anything to do with anything.

推荐答案

在此做出一些假设，但我将假设这是一个 32 位与 64 位问题.要进行验证，请从命令提示符(Windows 键、R、cmd.exe 或开始、运行、cmd.exe)尝试这两个命令

Making some assumptions here, but I'm going to assume that this is a 32 vs 64 bit issue. To verify, try these two commands from a command prompt (Windows Key, R, cmd.exe or Start, Run, cmd.exe)

"C:Program Files (x86)Microsoft SQL Server110DTSBinndtexec.exe" /file C:myPackage.dtsx
"C:Program FilesMicrosoft SQL Server110DTSBinndtexec.exe" /file C:myPackage.dtsx

第一个将在 32 位模式下运行您的包，而第二个将在 64 位模式下运行它.这很重要，因为您的驱动程序和您创建的任何 DSN 将仅在 32/64 位世界中可见.

The first will run your package in 32 bit mode whilst the second runs it in 64 bit mode. This is going to matter as your drivers and any DSNs you've created are going to only be visible in the 32/64 bit world.

一旦确定了您需要的版本，可能是 32 位版本，您需要确保您的项目使用适当的运行时.右键单击您的项目并选择属性"，然后导航到配置属性"下的调试"选项卡.

Once you've identified which one you need, probably 32 bit version, you'd need to ensure your project is using the appropriate run-time. Right click on your project and select Properties and then navigate to the Debugging tab under the Configuration Properties.

在反转 Run64BitRuntime 值后，我假设您的包将在 SSDT 中运行.

After inverting the Run64BitRuntime value, I assume your package will work from within SSDT.

您将需要编辑现有的 SQL 代理作业以更改作业步骤的位.这将在配置选项卡下，然后在高级选项卡下.选中/取消选中 32 位运行时.

You will need to edit the existing SQL Agent job to change the bittedness of the job step. This will be under the Configuration tab and then under the Advanced tab. Check/Uncheck the 32-bit runtime.

细心的人可能会看到 dtexec 提供了一个 /X86 选项.不要相信.获得正确位的唯一方法是显式调用正确的 dtexec.exe 文档甚至说了这么多，但没有人阅读文档.

Observant folks may see that the dtexec offers a /X86 option. Don't believe it. The only way to get the correct bit-ness is to explicitly call the correct dtexec.exe The documentation even says as much but nobody reads documentation.

此选项仅由 SQL Server 代理使用.此选项被忽略如果您在命令提示符下运行 dtexec 实用程序.

This option is only used by SQL Server Agent. This option is ignored if you run the dtexec utility at the command prompt.

这篇关于为什么我的 ODBC 连接在 Visual Studio 中运行 SSIS 加载时失败，但在使用执行包实用程序运行相同的包时却没有的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！