Export all msdb SSIS packages to files

This is going to be a post with more pictures than text, but it is something that is useful.  :)  I did not set this package up, it was done before I started working here.  But, it has been asked about, so I am blogging about it.

When I release changes to the production, I always want a rollback plan (I would assume most people do).  In order to do that for SSIS, I use a package to export all of the SSIS packages to files. Then, save those in a zip folder.  That way, I can get to them quickly as needed.  Here is the package info for those who can also use it. I will say that this only looks at 1 of the folders within the msdb structure - the main BI ETL folder (which has sub-folders and the SSIS packages in those sub-folders). The way this package works, I would have to have it in each of the SSIS solutions I use. I want to re-do this to make it more generic and re-useable for multiple solutions.

Here is what the full package looks like - it uses a SQL statement to get the folder names and puts that into a variable (System.Object).

The query in the first "Execute SQL" step is:

SELECT foldername
FROM dbo.sysssispackagefolders folders
WHERE EXISTS (SELECT NULL 
              FROM dbo.sysssispackages pkg 
              WHERE pkg.folderid = folders.folderid)

The next step is the "For Each Loop Container", which loops through each of the records in the System.Object variable filled with data from the first step.  It looks like this:

Lastly, the "Data Flow Task" step is what writes the SSIS package to the files, and it looks like this:

The query used in the "OLE DB Source" uses our specific folder names, as shown below:

select folder.foldername, pkg.name, pkg.packagedata
from dbo.sysssispackages pkg
join dbo.sysssispackagefolders folder on pkg.folderid = folder.folderid
where folder.foldername in ('MasterControlPackages'
                            , 'ODS_ETL_Staging'
                            , 'Staging_ETL_DW'
                            , 'StagingTransformations'
                            , 'Source_ETL_ODS')

The "Derive Column" step is making a column with the full path in addition to the package name.