Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup SSIS packages Expand / Collapse
Author
Message
Posted Sunday, December 20, 2009 8:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 10, 2011 6:32 PM
Points: 57, Visits: 249
Hi there

How to back up all the SSIS packages from MSDB? like for e.g if i need to backup database
i use this script

BACKUP DATABASE abc TO DISK = 'C:\abc.bak' WITH INIT, NAME = 'abc'
BACKUP DATABASE xyz TO DISK = 'C:\xyz.bak' WITH INIT, NAME = 'xyz'

i need similars scripts like this to backup all my SSIS packages

I tried backing up the MSDB but i could not able to find any SSIS package
after restoring it with different name on same instance

Cheers
Post #836951
Posted Monday, December 21, 2009 5:17 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 4:36 AM
Points: 496, Visits: 393
Hi,

the packages are stored in the [msdb].[dbo].[sysdtspackages90] table. The source xml of the package is stored in the [packagedata] column as image.

If you want to see the source XML of the package, you can simply convert it by simple select.

SELECT 
[name]
,CONVERT(xml, CONVERT(varbinary(max), [packagedata])) as XMLPackageData
,[id]
,[description]
,[createdate]
,[folderid]
,[ownersid]
,[packagedata]
,[packageformat]
,[packagetype]
,[vermajor]
,[verminor]
,[verbuild]
,[vercomments]
,[verid]
,[isencrypted]
,[readrolesid]
,[writerolesid]
FROM [msdb].[dbo].[sysdtspackages90]

If you store the content of the XMLPackageData into .dtsx file, you have the original package.

If you make a backup of your msdb database and restore it under other name, you will not see the packages on the server as the packages are being red from msdb. You can access them by the above mentioned query with appropriate database name
Post #837083
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse