How can you backup your SSIS packages? I've been asked several times, and the answer is it depends.
Where do you store your packages?
SSIS Package Store
The SSIS package store is just a folder on disk, so regular file system backups should suffice, or you can backup that folder specifically. By default it is %ProgramFiles%\Microsoft SQL Server\90\DTS\Packages. It is possible that multiple folders can be used, or the default is changed. This can be explored further by reviewing the SSIS service configuration file%ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml. For more information see Configuring the Integration Services Service. Restoration will depend on the capabilities of your file backup method and software.
SQL Server (MSDB)
The SQL Server store uses a table in the msdb database. For SQL Server 2005 this is dbo.sysdtspackages90, and for SQL Server 2008 this is dbo.sysssispackages. No extra work is required as msdb should already be included as part of your regular database backup routine. Regular backup and restore options apply but bear in mind msdb is a system database. Knowing the tables means we can manipulate the rows of data directly which offers some useful options such as moving rows via T-SQL, or other data access technologies including SSIS itself.
File System
This is perhaps the most common storage location used, and with good reason. It is easy to use and fits well with the code project style development paradigm we have with SSIS compared to traditional SQL Server object deployment and management. SSIS certainly fits better into this model, and we can use regular file system backups again.
SSIS Backup Tool
For those of you that remember DTS I wrote a rather handy tool DTSBackup. Whilst it was widely used by myself and many others, you could get by quite happily without it, but it gave people comfort, and more importantly it gave people more control and certainly faster and more granular recovery options. With SSIS there was a major development process change, lead by the new tools, to a code project style of development. This means that the primary backup should be your source code repository along with your release management process. The change in development paradigm between DTS and SSIS meant I didn't see a strong need for a SSIS port of the tool. Whilst I haven't written anything as polished or accomplished for SSIS, I have put together a sample application. Whilst I don't see a need for backups this code is still a useful example of transferring packages as part of a deployment or maintenance process.
Here is one of the core methods implemented in the application. The application is a simple Windows Forms application and is aimed more at getting you started rather than be a polished application.
/// <summary> /// Import all packages from a file system folder into a SQL Server (MSDB) store. /// </summary> /// <param name="folder">The source file system folder path.</param> /// <param name="server">The target SQL Server name.</param> /// <returns>The number of packages transferred.</returns> public static int ImportToSqlServer(string folder, string server) { // Validate parameters if (string.IsNullOrEmpty(folder)) { throw new ArgumentNullException(folder); } if (string.IsNullOrEmpty(server)) { throw new ArgumentNullException(server); } int counter = 0; Application application = new Application(); // Get package files string[] files = Directory.GetFiles(folder, "*.dtsx"); foreach(string file in files) { // Load and save package using (Package package = application.LoadPackage(file, null)) { application.SaveToSqlServer(package, null, server, null, null); counter++; } } return counter; }
The simple form allows you to import and export packages form SQL Server:
