Backing up and Restoring single SSIS package

I was responding to a question on MSDN forums and part of the question was about how to backup the SSIS packages after they are deployed onto SQL Server. This is not related to SQL 2012 where SSIS packages can be deployed into it’s own SSISDB database as project model. This question was related to SQL Server 2008R2.

When SSIS packages are deployed, they are stored in MSDB database. So, MSDB database has to be restored if you need to recover the ssis packages. Of course, you should also have to put the ssis packages in a source control and have backups of it.

But an interesting point was raised – “is it possible to restore a single SSIS package”. The concerns mentioned were having to do full restore of msdb database.

I thought this is a real concern because having to restore msdb to recover a ssis package could lead loss of other important information such as backup\restore history etc. So, the question was – Is it possible to recover a single SSIS package without having to restore complete MSDB database?

So, to test this, I created a simple ssis package and deployed to sql server.
The below query returns the ssis packages deployed on the server.
Select * from msdb.dbo.sysssispackages

Now, I copied the data from msdb.dbo.sysssispackages into another table
Select * into newssispackages from msdb.dbo.sysssispackages

Now, I made another change to the SSIS package and redeployed. So, to recover the prior version of ssis package, first,I deleted the SSIS package from table

Delete from msdb.dbo.sysssispackages where name ='TestJob'
and then inserted the corresponding row from another table into dbo.sysssispackages.

Insert into dbo.sysssispackages
Select * from newssispackages where name ='TestJob'

This restored the prior version of SSIS into msdb. So, we can take table backup(Select * into) and revert back to previous versions as explained above.

I am not sure whether this works in every scenario and would be curious to know on scenarios where this won’t work.

Advertisement

SSIS Packages in SQL 2012/14

From SQL 2012 onwards, ssis packages can be deployed in project model. This requires creating a catalog and enabling ‘CLR’ integration. Once the packages are deployed in project model, they can be browsed in the ‘Integration Services Catalog’ in the management studio. Below code retrieves the packages along with the folder and packages present in the integration services catalog.

select A.name as [FolderName],B.name as[ProjectName],C.name as [PackageName]
from [catalog].[folders] A
INNER JOIN [catalog].[projects] B ON A.folder_id=B.folder_id
INNER JOIN [catalog].[packages] C on C.project_id=B.project_id