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.