SSIS package works with windows account, fails with sqlserver sa account

Question

Hi,

Having some SSIS permissions issue.

I am calling the SSIS package in my .net console application with:

Package pkg = app.LoadFromSqlServer(packagePath, serverName, serverUsername, serverPassword, dtsEvents);

It is working when manually running it using the windows accout, but fails with the above call in my console application.

Shouldn't it work with the sql server sa account?

Answer 1

There are plenty of reasons for it to fail when run under different account, to avoid trying to fix it blindly, you should get the error information. It looks like you are supplying dtsEvent parameter - so your app should get the error message. Another way to get it to examine package.Error collection after execution, or enable SSIS logging and check the error log.

Once you have the error message, update the post :)

There is also a KB article dedicated to troubleshooting such errors: http://support.microsoft.com/kb/918760/.

But it just lists lots of possible error causes - to find out your issue, you need to get the error message first.

Answer 2

This generally happens when you have sql queries that use linked server to access a database on a different server.