A decision that all SSIS architects need to make at some point is whether to deploy their packages to SQL Server or the File System.
Kirk has a useful post here that discusses some of the pros and cons of both approaches: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx
I've wrestled with this one at length myself and have made the decision to always deploy to the file system. Allow me to me explain why.
The SSIS systems that I have deployed up to now have been multi-package solutions that use the Execute Package Task extensively. When you're building your packages in BIDS then they all exist as flat files so you need to use a file connection manager to point to them. However, if you deploy to SQL Server then your packages all require an OLE DB connection manager as well. So as I see it, deploying to SQL Server creates 4 problems:
- Your packages contain an OLE DB Connection Manager that doesn't get used in your development environment
- Your packages contain a File Connection Manager that doesn't get used in your Test/QA/Production environments
- You need to tell your packages which environment they are running in
- You have extra development effort to ensure your Execute Package Tasks use the correct connection manager
Deploying to the file system alleviates these issues.
For these reasons I choose to always deploy to the file system. I'm not recommending it as an approach for anyone else but I'd urge you to consider these issues when making this decision.
UPDATE 7th Jan, 2008: It would be wrong of me not to highlight the negative impacts of using File System storage as well. Sam Loud has uncovered an issue (documented here) that explains how, under certain circumstances, many connection managers may be needed. This is because when using File System storage the location and name of the package is defined in the connection manager; when using SQL Server storage the connection manager defines the instance on which the packages reside, and then the name of the package is defined in the Execute Package Task. Hence, when using SQL Server storage you have the advantage that a single connection manager can be used for many packages.
As always, evaluate the right choice for YOUR situation. The things stated here are just things to be aware of.