I have an SSIS package that I want to deploy to SQL Server A. This package needs to access tables on SQL Server A, and another SQL Server, B. What is the best way to set this up? Should I create a domain account, and create a matching login on each machine and grant the appropriate permissions? If I did this, how do I run the job that runs the SSIS package completely under that domain account context? How should I be saving connection info within the SSIS package?
Do I really need to use a proxy account? It seems that if I have connection information saved in the SSIS package, it shouldn't matter what account is running the SQL Server Agent or anything like that.
Would just using a linked server using a SQL Server login make the most sense?