Post reply

How to restore multiple packages from Structured Storage File format

  • December 11, 2006 at 2:05 pm

    #116520

    Based on Andy Warren's article at http://qa.sqlservercentral.com/columnists/awarren/copyingdtspackagestoadifferentserver.asp and Minh Vu's suggestion in http://qa.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

  • December 11, 2006 at 5:52 pm

    #677389

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

  • December 12, 2006 at 8:28 am

    #677494

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

  • December 12, 2006 at 10:26 am

    #677545

    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

  • December 12, 2006 at 11:06 am

    #677553

    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.

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

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