We get this question a lot actually. “Is it better to save to SQL Server or to files”. The seemingly obligatory answer is “It depends”. This is not a new question either. The options to save packages to SQL Server and to files were available in DTS as well as the option to save to Metadata Services, which is no longer available in SQL Server 2005. It's a good question. It means people are trying to understand the tradeoffs. So here are some of them:
Advantages of Saving to Files
- Easier to do shared source control
- Ultra secure when using the Encrypt with User Key encryption option
- Not subject to network downtime problems (saved locally)
- May escrow deployment bundles including miscellaneous files
- Less steps to load into the designer
- Easier direct access for viewing
- May store packages hierarchically in file system
- Projects in Visual Studio are disk based and require the package to be in the file system
- Generally, a better experience during development
Advantages of Saving to SQL Server
- Easier access by multiple individuals
- Benefits of database security, DTS roles and Agent interaction
- Packages get backed up with normal DB backup processes
- Able to filter packages via queries
- May store packages hierarchically via new package folders
- Generally, a better in-production experience
Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden