SSIS Package Needs Proxy Account - How do I debug / run locally?
I have set up an SSIS package in BIDS 2008 to import an XML file from a network file share, then process it. For speed, both the importing and processing is handled in T-SQL. The SQL Server / Agent account (one account) does not have access to the network file share, so the Job runs as a Proxy account, and the SSIS package runs fine as a job. The problem comes when I open the SSIS package in BIDS and try to run it. It fails with the rather uninformative message:
The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Running the T-SQL (stored procedure) manually gives the following error:
Msg 4861, Level 16, State 1, Line 2 Cannot bulk load because the file "\\SERVER\SHARE\TEST123.xml" could not be opened. Operating system error code 1326(failed to retrieve text for this error. Reason: 15105). This is a permissions issue, as the SQL Server / Agent account (one account) does not have access to the network file share (as stated above), which is why the Proxy account was created. So how do I get the SSIS package to use the Proxy account when trying to debug / run the package locally?