At work recently, we had a little confusion about where SQL Server Integration Services (SSIS) packages should be stored on our database server, and we ended up having multiple copies of packages littered around the server. To me, the dialogs for dealing with SSIS packages seem a little confusing (as does SSIS in general, but that's another topic).

We use SQL Server 2008, so I'm not sure if there are slight differences between this and 2005 / 2008 R2.

Basics

After SSIS packages are developed (using Visual Studio Business Intelligence Development Studio - BIDS), they are deployed to a production environment in SQL Server. Within SQL Server, packages can be stored in two places: "File System" and "MSDB". Shown below is a screen shot showing this in SQL Server Management Studio (SSMS) connected to Integration Services.

When you connect to Integration Services with SSMS then right-click and select Import Package on the MSDB tree node under Stored Packages, it stores the package in the MSDB system database (of course).

When you right-click and select Import Package on the File System node under Stored Packages, you're able to select a .dtsx file in a dialog. It seems when you do this (or at least it did to us), that SQL Server will reference the file in the location that you specified. Actually, it copies it to a special directory that SQL Server manages ("C:\Program Files\Microsoft SQL Server\100\DTS\Packages" in our case).

We had our own directory on the database server that we agreed to store SSIS packages in. When we stored the package files there, then went through the import process for the File System option, we ended up having two copies (one in our directory, one in SQL Server's).

Another confusing point was the dialog itself when right-clicking and selecting Import Package on either the MSDB or File System nodes. It gives a choice of three locations to import from: SQL Server, File System, and SSIS Package Store. When you select SQL Server, you're given a choice to import items just under the MSDB node. When you select File System, you're can choose a .dtsx file that's on your hard drive (not in the File System node of the same name under Stored Packages). When you select SSIS Package Store, you're given a choice of anything under the Stored Packages node (including items under the MSDB node which you see when you select the SQL Server source - giving you two ways to access the same package).

Although confusing, it seems this dialog is designed to be flexible enough for you to import packages stored not only locally on disk, but also stored on a different SQL Server either in that server's MSDB or File System store.

Where Should Packages be Stored?

Bear in mind that packages can just be stored on disk outside of SSMS and the file referenced directly from a SQL Agent job (where the package will commonly be triggered). This probably doesn't give you the flexibility or management that is intended with SSIS, though, as you can't see the list of all SSIS packages from within SSMS, you can fire a package directly from within SSMS if it's stored there, etc.

Packages can be imported using the File System target and SQL Server can manage the files in its special directory, but this puts the package physically outside of database and it won't be backed up along with the database.

What we ended up settling on is storing our packages in MSDB. Several Google results recommended this for ease of management and backups (if you backup MSDB, then all the packages are backed up too).