Often, I see people reporting "zzz fails if I'm not admin". Do I have to be machine admin to use SSIS?
Well, absolutely no. Most SSIS tasks can be performed without being admin at all. Actually, I develop on Vista machine and I very rarely need to elevate to admin status.
Then why would you get errors if you are not admins? Most often you don't have access to whatever data source or package location you use. E.g. if you load a package from SQL Server, you need to have access to this SQL Server. Often people automatically get access because they are local admins, but for other users, access should be configured explicitly.
Same is true for database and file connections at package execution time: if package tries to access something, the user who runs the package has to have an access to this resource (assuming package is using integrated authentication). So if your package fails when run as non-admin, the first step to do is turn on SSIS or console logging and see what error you get - this will point to the resource for which you need permissions.
Do I ever have to be machine administrator when working with SSIS? Yes, there are several situations when admin is needed.
- First, when you look at the running packages in SSMS, you'll only see your own packages if you are non-admin. If you need to see all packages, you need to be machine admin (unfortunately, it is controlled by admin membership - would be better to have specialized role for this).
- Second, if you are developing custom task or transform in managed code, you'll need to install it to GAC - this requires machine admin as well.
- When developing in native code, this is usually simpler - you register the task once as admin; later you can compile it as regular user.