SSIS DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER错误代码。在投标和cmd上运行良好，但在API调用上失败

I know this has been asked a lot of times before, and I guess I’ve read almost every related post but could not resolve this error.

我知道这个问题已经被问过很多次了，我想我已经读过几乎所有相关的文章，但是我不能解决这个问题。

I have an SSIS package which reads an Excel File (Data Flow Source) on my local system and transfer the data to SQL Server local database using OLEDB Destination Data Flow Item, with a derived column and data conversion step involved in between. I’ve set:

我有一个SSIS包，它读取本地系统上的Excel文件(数据源)，并使用OLEDB目标数据流项将数据传输到SQL Server本地数据库，其中包含派生列和数据转换步骤。我设置:

Run64BitRuntime as False

Run64BitRuntime是错误的

TransactionOption as Supported

TransactionOption作为支持

DelayValidation as True (Data Flow Task and connections)

DelayValidation为真(数据流任务和连接)

ProtectionLevel as DontSaveSensitive (since I’m using Windows authentication)

保护级别为dontsavesencurious(因为我正在使用Windows认证)

When not running BIDS as an administrator, I get a warning “Could not open global shared memory” (even when I’ve added my account user, which is an admin too to the Windows Performance Group) but the package executes fine and all the 20K+ rows from the excel file are imported to the table. It also runs fine when called from cmd.

投标运行时才作为管理员,我得到一个警告“无法打开全局共享内存”(即使我添加我的账号用户,这是一个管理Windows性能组)但包执行好,所有20 k +行从excel文件被导入到表。当从cmd调用时，它也可以正常运行。

Now, my requirement is to call this package from the API. Below is the basic code I’m using to call this package:

现在，我的需求是从API调用这个包。下面是我用来调用这个包的基本代码:

Package pkg;
Application app = new Application();
DTSExecResult pkgResults;
Variables vars;
string FileName = @"D:\Users\rohit\Documents\Visual Studio 2008\Projects\OutboundPerformance\SalesPerf\ExcelDataInput.dtsx";
pkg = app.LoadPackage(FileName, null);
vars = pkg.Variables;
vars["var_fk_CampaignId"].Value = ddlCampaignName.SelectedValue;
pkgResults = pkg.Execute(null, vars, null, null , null);
if (pkgResults == DTSExecResult.Success)
lblerror.Text = "Package executed";
else
lblerror.Text = "Package failed";

And the package execution fails with the error (from SSIS logging to text file):

并且包执行失败(从SSIS日志到文本文件):

Diagnostic,M02697,IIS APPPOOL\ASP.NET v4.0,Excel Connection Manager,{8EECAC3E-7A77-43E8-AD03-B734D273E489},{2AC52183-188B-4B82-BB67-D5112FE08710},20/06/2014 17:16:40,20/06/2014 17:16:40,0,0x,ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.
OnError,M02697,IIS APPPOOL\ASP.NET v4.0,Data Flow Task,{F2934EFE-11E9-4A70-80D6-EB7143A07745},{2AC52183-188B-4B82-BB67-D5112FE08710},20/06/2014 17:16:40,20/06/2014 17:16:40,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

OnError,M02697,IIS APPPOOL\ASP.NET v4.0,ExcelDataInput,{FCEC3902-379C-4D69-9135-0246FC60AEAE},{2AC52183-188B-4B82-BB67-D5112FE08710},20/06/2014 17:16:40,20/06/2014 17:16:40,-1071611876,0x,SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

OnError,M02697,IIS APPPOOL\ASP.NET v4.0,Data Flow Task,{F2934EFE-11E9-4A70-80D6-EB7143A07745},{2AC52183-188B-4B82-BB67-D5112FE08710},20/06/2014 17:16:40,20/06/2014 17:16:40,-1073450985,0x,component "Excel Source" (307) failed validation and returned error code 0xC020801C.

I’m new to SSIS and did research on the internet on error codes 0xC0202009 and 0xC020801C. I’ve tried giving access permission to IIS_USERS, SQL dts and Agent User for the excel file, SQL temp directory and files (read and write) and the excel file isn’t locked by any other process. I’m running windows 7 32-bit and Run64BitRuntime is set as False.

我是SSIS的新手，在互联网上对错误代码0xC0202009和0xC020801C进行了研究。我尝试过为IIS_USERS、SQL dts和代理用户提供excel文件、SQL temp目录和文件(读和写)的访问权限，并且excel文件没有被任何其他进程锁定。我运行的是32位的windows 7运行时，Run64BitRuntime设置为False。

Now, I’ve tried both, Windows Authentication as well as SQL authentication (checkbox checked to save password and protection level set to EncryptSensitiveWithUserKey). BIDS runs the package fine but it fails with the same error on call from my web application. Deadline is approaching and I need to modify this package to do a lot more stuff once this error is fixed.

现在，我尝试了Windows身份验证和SQL身份验证(选中复选框以保存密码和设置为encryptsensivewithuserkey的保护级别)。投标可以很好地运行包，但它失败了，与我的web应用程序调用时的错误相同。最后期限即将到来，一旦这个错误被修正，我需要修改这个包来做更多的事情。

What else could I be missing here? Any help/suggestion will be highly appreciated.

我还能错过什么呢?如有任何帮助或建议，我们将不胜感激。

Thanks,

谢谢,

Rohit

罗希特

1 个解决方案

#1

Thank you billinkc for pointing out the missing link. I changed my App Pool's identity to Local System and it worked like a charm.

谢谢billinkc指出缺失的环节。我把我的应用程序池的标识改成了本地系统，它就像一种魔力。

http://i.imgur.com/yUq6nen.jpg

Thanks Vikramsinh Shinde for your time and suggestion.

谢谢你的时间和建议。

#1