I'm having an issue executing an SSIS package programmatically in a web application during development on my machine. The package imports the contents of a flat file into SQL Server. The file path (data source) of the flat file connection is a local path on my machine, which is passed to the SSIS package as a variable. (All resources are local to the machine except for the SQL Server database.)
Through package logging and a monitoring application, I was able to verify that the package is being executed as NTAUTHORITY\NETWORK SERVICE (which is defined in the AppPool). I've given permissions to both the SSIS dtsx package file, as well as the flat file to be imported. (I've even in desperation given access to "Everyone" and still get the same error.)
When the package attempts to make a connection to the flat file, it generates an error: 'The file name "[full file path]" specified in the connection was not valid.'
Error numbers, generic description as follows.
Hex Error Code: 0xC001401E
Decimal Error Code: -1073659874
Error Name: DTS_E_INVALIDFILENAMEINCONNECTION
Description: The file name "__" specified in the connection was not valid.
I've verified that the file path is being passed into the SSIS package correctly by copying it and pasting into windows' run dialog, which opens the file. I can also run the SSIS package in the package designer without incident.
I discovered (thankfully) that another team member was able to run the package on his dev machine. When running the web application using the visual studio development server, the package executes as the logged on windows user account, and runs successfully. I was able to verify this by switching the web project settings.
The theory then was that there are some weird internal permissions being applied to the network service account to prevent web applications from accessing certain resources. So I made an account on my machine especially for the purpose of running web applications, set the AppPool to use it, and gave it permissions to the necessary resources (SSIS packages, import files). Same error.
My final theory is that somehow IIS in Vista or the AppPool or something is applying it's own permissions as top priority, preventing SSIS from accessing the flat file correctly.
So, does anyone have any more knowledge about what is going on here? I'm OK for development because I now know to run the application using the VS development server if I want to use the SSIS import feature. We're OK for staging and production, because all those servers are running a non-Vista OS. But I'd still like an answer as to what is going on, and ideally how to be able to run the web application directly through IIS on my dev machine without having to use the VS internal web server.
Any ideas are welcome.