File system:
1. If the system is secure (no access to every one) then File system is the best.
2. We can set dynamic source (connection manager strings) without using config file. 3. Good to have if we are having all environments are similar (same setup)
4. Easy to deploy and maintain.
5. If we have any assembly used in the package then file system is better.
6. After modification just need to keep the package on the specific location.
7. Good to opt this if we have lot of packages to deploy.
SQL Server:
1. We can deploy two packages with the same name with different ID which creates problem to execute the package
2. Best from security point of view (until access no one can even execute the package)
3. Little tricky to set dynamic source path (connection manager settings) so need config file for that.
4. Only security is the one option which is good here other than this no more benefit.
5. Need to build/deploy every time before adding into SQL agent.
6. If the job is on different server and packages are on different server then need to create proxy then it runs the job and execute the packages.
7. Good if we have less no of packages (less than 10)
Conclusion: Overall I feel File system is the good option if we have secure environment.
