How to restore multiple packages from Structured Storage File format

Question

Post reply

How to restore multiple packages from Structured Storage File format

Carlos Urbina

SSCommitted

Points: 1850
More actions
December 11, 2006 at 2:05 pm

#116520

Based on Andy Warren's article at http://www.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp and Minh Vu's suggestion in http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=274907, we have successfully backed up all DTS packages to a remote backup server using the code below. Note that within the cursor (my apologies to the purists) each command generated by the select that populates the temp table #SQL is executed to perform the actual backup.
The question now is, how do we retrieve the Structured Storage Files from the remote files server and populate the SQL Server from which we originally backed them up?
--///////////////////////////////////////////////////////////////////////
--                           BACKUP CODE
--///////////////////////////////////////////////////////////////////////
DECLARE @TARGETDIR varchar(1000), @SQL varchar(200)
CREATE TABLE #SQL (SQLStatement varchar(200))
SET @TARGETDIR = '\\PLATEDIPROD01\FTWVDSGSQL03\BackupDTS\'
-----------------------------------------------------------------------------------
-- Create commands
-----------------------------------------------------------------------------------
INSERT INTO #SQL
SELECT distinct
'exec master.dbo.xp_cmdshell ' + '''DTSRUN.EXE /S '
+ CONVERT(varchar(200), SERVERPROPERTY('servername'))
+ ' /E '
+ ' /N '
+ '"' + name + '"'
+ ' /F '
+ '"' + @TARGETDIR + name + '.dts"'
+ ' /!X' + ''''
FROM msdb.dbo.sysdtspackages P
-----------------------------------------------------------------------------------
-- Initialize Cursor
-----------------------------------------------------------------------------------
DECLARE Command CURSOR FOR
SELECT SQLStatement
   FROM #SQL
   OPEN Command
FETCH NEXT FROM Command INTO @SQL
-----------------------------------------------------------------------------------
-- Cursor Loop Start
-----------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
--print @SQL
execute (@SQL)
FETCH NEXT FROM Command INTO @SQL
END
-----------------------------------------------------------------------------------
-- Cursor Loop End
-----------------------------------------------------------------------------------
close Command
deallocate Command
-----------------------------------------------------------------------------------
-- Finalize
-----------------------------------------------------------------------------------
drop table #SQL

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Block Old Hand Points: 334 More actions · Answer 1

You need to use the COM object either via the OA extended stored procs, a script, .NET, etc.

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 2

Thanks for responding, Jeff. Is there any chance that you have an example? When you get a moment, could you post it?

Jeff Block Old Hand Points: 334 More actions · Answer 3

the vbscript code would look something like this:

SQlServer=WScript.Arguments(0)

Filename=WScript.Arguments(1)

const TrustedConnection = 256

dim version1

Set oPackage1 = CreateObject("DTS.Package2")

oPackage1.LoadFromStorageFile Filename,"","","",""

oPackage1.SaveToSQLServer SQlServer, , , TrustedConnection

Set oPackage1 = Nothing

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 4

Thanks, Jeff. There's a lot of stuff in that snippet, some of which I don't know, but in a little while I will. Thanks for pointing us in the right direction.