July 30, 2007 at 8:45 am
Hi
I'm wondering how people have chosen to store their SSIS packages - file system or in msdb?
From what I've found it seems that both methods work well. File system seems to be slightly easier for editing and changing packages (no need to redeploy) whereas msdb seems to be a bit neater (no extra directory structures, backed up as part of SQL backups)
If there's no great advantage either way then I'll probably go with storing them in msdb.
cheers
July 30, 2007 at 9:28 am
Use file system.
There are no advantages to using MSDB except the backup, which is outweighed by the hassle of editing etc...
Security can be put on the FS as well as config files can live alongside the package.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
July 31, 2007 at 1:05 am
beath, you've highlighted the trade offs exactly so basically if you don't edit the packages frequently then I agree, MSDB is much neater, but if you find yourself requiring easy access to the package itself, or want to run them interactively etc then the file system is probably the most flexible.
I'm working with two companies at the moment, one using the file system and the other MSDB. The MSDB even stores it's config information in a database so the whole thing never touches disk. The other continues to change and re-release the packages frequently so we've kept them in the file system with xml configuration files.
S'pose it's up to you to weigh it up then.
July 31, 2007 at 6:19 am
We have a great deal of ETL here and we are using a single, central SSIS server to simply store all of our packages in the MSDB database. The job agent on that server runs some of them, and other packages are called from other servers via the command line.
From an editing persective, we create projects and solutions in visual studio and use Visual SourceSafe to check everything in and out. In theory, the latest version of the package is pulled form VSS and after changes are made, it is deployed to the SSIS server. To date (since about 17 months ago), that has really been the case. Deployment is less than perfect since we wanted a nice folder structure on the server forcing us to import the package from the SSIS server rather than using the Visual Studio deployment packages. Because we have development and test environments, we have been using package configurations stored on the C drive (because all of our servers have that drive) making it the configuration of the server that the command line is run on determining the connections being used. This does mean we have to deploy package configuration files to multiple servers, but some good annotations in the package itself have made this easy to manage.
I guess I was reluctant to use the file system because it felt wrong to me. It would clearly work just as well to put all of the packages on a file server that gets a regular tape backup and deploy them in much the same way we do with the MSDB database.
Either way you go, I would recommend you do not fight the Visual Studio development environment. Use a compatible source control management package (again, we are using VSS) to handle your version control and take the time to come up with a good strategy for creation of new solutions to keep things organized. If you have not done any of this before, take the time to speak with your development team (if you have one) or someone that has been working with Visual Studio for a few years.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply