In earlier days we have the options to save the DTS packages to SQL Server or to File System. Many of you trying to understand the trade offs between them. This post list out the advantages of both options.
Advantages of Saving and Deploying SSIS packages to File System
- We can use Source control system manage the versions.
- More secure when using the encrypt with user key encryption.
- Packages are available when you save locally. not subject network downtime problems.
- We can easily load the file system packages quickly in to the designer.
- Generally better user experience while developing the packages in BIDS.
- There is no difference in the execution speed whether SQL Server or File system package
Advantages of Saving and Deploying SSIS packages to SQL Server
- Packages can be easily accessed by multiple individuals when you save it to SQL Server.
- It is more secure when you save the packages to SQL server compare to File system. You will get the benefits of database security, DTS roles.
- You can include the Packages in normal Database Backup process.
- You can filter the packages using SQL server queries.
You can read the below URL to know more about on this topic