Easily Import and Export SSIS Packages

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

Package_Management.dtsx has a few control switches (i.e., package variables) that determine which subpackage should run and the actions to be taken. In all, there are five variables that you need to set to use the SSIS Package Management solution:

• Root_Folder: This variable is used to specify the folder where the SSIS folder structure will be extracted to (when exporting from msdb) or imported from (when importing to msdb). The folder path must be a Universal Naming Convention (UNC) path. The account that's running the solution must have read/write permission for the folder specified in Root_Folder.
• SQL_Server_Instance_Name: This variable is used to specify the name of the SQL Server machine that SSIS folder structure will be extracted from or imported to.
• Import_To_MSDB: Setting this variable to 1 puts the solution into import mode, which means the SSIS folder structure will be imported to msdb. When you don't want to perform an import operation, you set this variable to 0.
• Export_From_MSDB: Setting this variable to 1 puts the solution into export mode, which means the SSIS folder structure will be exported from msdb. When you don't want to perform an export operation, you set this variable to 0.
• Just_Cleanup: Setting this variable to 1 puts the solution into cleanup mode, which means the existing SSIS folder structure will be deleted from msdb. (No other action is taken.) When you don't want to perform a cleanup operation, you set this variable to 0.

The machine where the solution will be running, the root folder where the SSIS folder structure will be extracted to or imported from, and SQL Server machine where the SSIS packages will imported to or extracted from can all be on separate machines, which provides complete flexibility.

To use the import mode, set Import_To_MSDB to 1, Export_From_MSDB to 0, and Just_Cleanup to 0. The solution will then move the SSIS folder structure from the location specified in Root_Folder to the msdb database on the server specified in SQL_Server_Instance_Name. Figure 3 shows an example of the SQL_Server_Instance_Name and Root_Folder variables set.

Figure 3: The Package_Management.dtsx variables (click to enlarge)

To use the export mode, set Export_From_MSDB to 1, Import_To_MSDB to 0, and Just_Cleanup to 0. The solution will then move the SSIS folder structure in the msdb database on the server specified in SQL_Server_Instance_Name to the folder specified in Root_Folder. (The specified folder must already exist.)

To use the cleanup mode, you set Just_Cleanup to 1, Import_To_MSDB to 0, and Export_From_MSDB to 0. The solution will then remove the SSIS folder structure from msdb on the server specified in SQL_Server_Instance_Name.

As you might have noticed, only one of the Import_To_MSDB, Export_From_MSDB, and Just_Cleanup variables can be set to 1 at any given time. If you set more than one of these variables to 1, you'll receive an error message like that in Figure 4.

Figure 4: Error message received when some of the variables are set incorrectly (click to enlarge)

You can download the SSIS Package Management solution by clicking the 101918.zip hotlink at the top of the page. The solution works on SQL Server 2005 and later.