I’ve tended to use filesystem which has the advantages of:-
1) Not bloating MSDB if you have thousands of packages.
2) Ease of deployment of packages – DTSX files can simply be copied into the filesystem using Windows Explorer.
3) Multi-user security – using NTFS permissions within the filesystem it’s possible to grant rights to over-write packages to more than one person. Useful if you have teams of developers all wanting to deploy packages to specific folders but not others.
MSDB I think has the advantages of:-
1) A database backup of MSDB will automatically safeguard all SSIS packages. No further messing about with file-system backups needed.
2) Ability to use the roles within MSDB specifically designed to allocate granular rights to SSIS packages – although I believe that these are more geared around a designated package owner as opposed to multiple people accessingÂ
I’m surprised no one has mentioned the one drawback of storing packages in the database: you cannot edit them there. You have to export the package to the file system, edit, and re-import the package. If you’re going to do that anyway, it’s just as easy to store them on the file system.
Personally, I like to store the package as files because I can simply ZIP up the whole thing to make a snapshot for version archiving and/or for easy FTP transfer between the development and production systems. I use 7.Zip on the whole directory; it’s free and you get extremely good compression on SSIS package files.
Files are also good if you’re trying to do a global edit or search to change a table or variable name, and you need to be sure you’ve caught all the places where you used the old name. I use Notepad++ which can search/edit a whole file tree. (Yes, global edits can be dangerous, but I can always revert to the previous version simply by unzipping my snapshot.)
Comments