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:
Run64BitRuntime as False
TransactionOption as Supported
DelayValidation as True (Data Flow Task and connections)
ProtectionLevel as DontSaveSensitive (since I’m using Windows authentication)
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.
Now, my requirement is to call this package from the API. Below is the basic code I’m using to call this package:
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):
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’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.
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.
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.
https://i.sstatic.net/yakxX.jpg
Thanks Vikramsinh Shinde for your time and suggestion.
