In SQL Server 2005 and later, you can create a SQL Server Integration Services (SSIS) package in Microsoft Visual Studio and store that package as a file (with a .dtsx extension) or in SQL Server's msdb database. Storing SSIS packages under a well-defined folder structure in msdb offers a couple of advantages. Because the SSIS packages are stored in a centralized location, you can easily back up all of them by backing up the msdb database. Plus, when you store SSIS packages in the msdb database, they're protected by another layer of security. When the SSIS packages are stored as files, they can be accessed and modified by anyone who has read/write permission for the folders in which the packages are stored (unless they are password protected).

Despite the benefits of storing SSIS packages in msdb, getting them into the database can be a hassle, especially when your packages are scattered throughout hundreds of subfolders in the SSIS folder structure. In such cases, you need to manually import all the packages, one at a time. Although you can use the dtutil command-line utility to copy, move, and delete SSIS packages, it can be tricky and time-consuming to use. And if there's an existing SSIS folder structure on msdb, it must be removed prior to importing a new one.

To avoid these hassles, I developed the SSIS Package Management solution. As Figure 1 shows, you can use it to import a SSIS folder structure (and all its underlying SSIS packages) to msdb, export an SSIS folder structure from msdb, and delete an existing SSIS folder structure from msdb.

Figure 1: The SSIS Package Management solution's functionality (click to enlarge)

The SSIS Package Management solution consists of three packages: a main package named Package_Management.dtsx and two subpackages named Extract_Packages_From_MSDB_To_Files.dtsx and Import_SSIS_Packages_To_MSDB.dtsx (see Figure 2).

Figure 2: The SSIS Package Management solution's three packages