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:
我正在SSIS 2012中处理Data Mart加载包。当尝试在Visual Studio中执行包时，我收到此错误：
"The AcquireConnection method call to the connection manager Data Warehouse.ssusr failed with error code 0xC0014009".
“AcquireConnection方法调用连接管理器Data Warehouse.ssusr失败，错误代码为0xC0014009”。
When I test the connectivity of the Connection Manager Data Warehouse.ssusr I see that it passes.
当我测试Connection Manager Data Warehouse.ssusr的连接时，我看到它通过了。
When I execute the package outside of Visual Studio using the Execute Package Utility, the package runs.
当我使用Execute Package Utility在Visual Studio外部执行包时，包运行。
I don't understand what's going on.
我不明白发生了什么。
The package also refuses to run using the SQL Server Job Schedule, if that has anything to do with anything.
该程序包还拒绝使用SQL Server作业计划运行，如果这与任何事情有关。
1 个解决方案
#1
38
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)
在这里做一些假设，但我将假设这是一个32对64位的问题。要进行验证，请从命令提示符处尝试这两个命令（Windows Key，R，cmd.exe或Start，Run，cmd.exe）
"C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx
"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx
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位模式运行程序包，而第二个以64位模式运行程序包。这很重要，因为您的驱动程序和您创建的任何DSN仅在32/64位世界中可见。
Fixing SSDT
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.
一旦确定了所需的版本（可能是32位版本），就需要确保项目使用适当的运行时。右键单击项目并选择Properties，然后导航到Configuration Properties下的Debugging选项卡。
After inverting the Run64BitRuntime value, I assume your package will work from within SSDT.
反转Run64BitRuntime值后，我假设您的包将在SSDT内工作。
Fixing SQL Agent
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.
您需要编辑现有的SQL代理作业以更改作业步骤的严重性。这将位于“配置”选项卡下，然后位于“高级”选项卡下。选中/取消选中32位运行时。
Lies and deception
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.
细心的人可能会看到dtexec提供了/ X86选项。不要相信。获得正确位的唯一方法是显式调用正确的dtexec.exe文档甚至说尽可能多但没有人阅读文档。
This option is only used by SQL Server Agent. This option is ignored if you run the dtexec utility at the command prompt.
此选项仅供SQL Server代理使用。如果在命令提示符下运行dtexec实用程序，则忽略此选项。
#1
38
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)
在这里做一些假设，但我将假设这是一个32对64位的问题。要进行验证，请从命令提示符处尝试这两个命令（Windows Key，R，cmd.exe或Start，Run，cmd.exe）
"C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx
"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /file C:\myPackage.dtsx
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位模式运行程序包，而第二个以64位模式运行程序包。这很重要，因为您的驱动程序和您创建的任何DSN仅在32/64位世界中可见。
Fixing SSDT
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.
一旦确定了所需的版本（可能是32位版本），就需要确保项目使用适当的运行时。右键单击项目并选择Properties，然后导航到Configuration Properties下的Debugging选项卡。
After inverting the Run64BitRuntime value, I assume your package will work from within SSDT.
反转Run64BitRuntime值后，我假设您的包将在SSDT内工作。
Fixing SQL Agent
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.
您需要编辑现有的SQL代理作业以更改作业步骤的严重性。这将位于“配置”选项卡下，然后位于“高级”选项卡下。选中/取消选中32位运行时。
Lies and deception
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.
细心的人可能会看到dtexec提供了/ X86选项。不要相信。获得正确位的唯一方法是显式调用正确的dtexec.exe文档甚至说尽可能多但没有人阅读文档。
This option is only used by SQL Server Agent. This option is ignored if you run the dtexec utility at the command prompt.
此选项仅供SQL Server代理使用。如果在命令提示符下运行dtexec实用程序，则忽略此选项。