Restore multiple DTS packages

Wednesday Oct 6th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

MAK examines how to take advantage of VB Script and MS-DOS batch files, to restore multiple DTS packages stored in the form of structured storage files from one folder to a SQL Server box.



This article examines how to take advantage of VB Script and MS-DOS batch files to restore multiple DTS packages stored in the form of structured storage files from one folder to a SQL Server box. When many DTS packages reside as structured storage files, it can be tiresome to restore those DTS packages one by one using Enterprise Manager. [Fig 1.1] As described in my previous article, Automating "Save DTS package," there may be cases where you have multiple DTS packages stored in a folder.



Click for larger image

[Fig 1.1]



Let's simulate the whole process of restoring multiple DTS packages stored as a structured storage file.

Pre-Requisite

  1. SQL Server client installed on the machine
  2. All the DTS packages have only one version stored in the structured storage file
  3. The Windows Login used to run the VB Script should have administrative access to the SQL Server box where you are going restore the packages, because we are going to use Trusted Connection.

Let us assume that we have all the DTS packages stored in a structured storage format in the folder D:\DTS\20040828 [Fig 1.2]


[Fig 1.2]

Step 1

Create folder C:\restoreDTS. [Fig 1.3]


[Fig 1.3]

Step 2

Create C:\RestoreDTS\RestoreDTS.vbs. Copy and paste the code below into RestoreDTS.vbs and save the file.

'Objective: Load DTS package and save it to SQL Server
'Created by:MAK
'Date: Sep 17, 2004

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

Download Code here.

Step 3

Create C:\RestoreDTS\Restore.bat. Copy and paste the code below into the file and save it.

REM Objective: Load DTS package and save it to SQL Server
REM Type: Batch File to get all DTS package name
REM Created by: MAK
REM Contact: mak_999@yahoo.com
REM Date: Sep 17, 2004
REM Parameters %1 = SQl Server box name %2 = directory name
@Echo on
 
REM Export DIR listing to C:\MyImport\Dirlist.txt

Echo "Load Started" 
Date/t 
time/t 
dir %2 /b > C:\RestoreDTS\Dirlist.txt

REM Restore all the DTS package listed in C:\RestoreDTS\Dirlist.txt

for /f "tokens=1" %%i in (C:\RestoreDTS\Dirlist.txt) do Cscript /nologo RestoreDTS.vbs %1 %2%%i 

REM echo %2%%i
Echo "Load Completed" 
Date/t 
time/t

Download Restore.Bat here.



Step 4



Execute the batch file [Fig 1.4]


restore SQL d:\DTS\20040828\ > 
  c:\RestoreDTS\RestoreDTS.log

Click for larger image

[Fig 1.4]


Where "SQL" is the SQL Server box name and "d:\DTS\20040828\" is the path where all the DTS packages are stored in structured storage file format.


When the batch file is executed, it creates Dirlist.txt [Fig 1.5] and executes the vbscript, RestoreDTS.vbs for every file listed in C:\RestoreDTS\Dirlist.txt. It also creates a log file C:\RestoreDTS\RestoreDTS.log [Fig 1.6]


Click for larger image

[Fig 1.5]




[Fig 1.6]

The log file captures all of the processing information as shown below.

C:\RestoreDTS>REM Objective: Load DTS package and save it to SQL Server 
C:\RestoreDTS>REM Type: Batch File to get all DTS package name 
C:\RestoreDTS>REM Created by: MAK 
C:\RestoreDTS>REM Contact: mak_999@yahoo.com 
C:\RestoreDTS>REM Date: Sep 17, 2004 
C:\RestoreDTS>REM Parameters SQL = SQl Server box name d:\DTS\20040828\ = directory name 
C:\RestoreDTS>REM Export DIR listing to C:\MyImport\Dirlist.txt 
C:\RestoreDTS>Echo "Load Started"  
"Load Started" 
C:\RestoreDTS>Date/t  
Sun 09/19/2004 
C:\RestoreDTS>time/t  
12:07p
C:\RestoreDTS>dir d:\DTS\20040828\ /b  1>C:\RestoreDTS\Dirlist.txt 
C:\RestoreDTS>REM Restore all the DTS package listed in C:\RestoreDTS\Dirlist.txt 
C:\RestoreDTS>for /F "tokens=1" %i in (C:\RestoreDTS\Dirlist.txt) do Cscript /nologo 
RestoreDTS.vbs SQL d:\DTS\20040828\%i  
C:\RestoreDTS>Cscript /nologo RestoreDTS.vbs SQL d:\DTS\20040828\dts1_Aug_23_2004_
_8_46_58_513P.dts  
C:\RestoreDTS>Cscript /nologo RestoreDTS.vbs SQL 
  d:\DTS\20040828\dts1_Aug_28_2004__8_28_54_967A.dts  
C:\RestoreDTS>Cscript /nologo RestoreDTS.vbs SQL 
  d:\DTS\20040828\MyImportDTS1_Aug_28_2004__8_29_37_000A.dts  
C:\RestoreDTS>Cscript /nologo RestoreDTS.vbs SQL 
  d:\DTS\20040828\MyImportDTS_Aug_28_2004__8_29_10_403A.dts  
C:\RestoreDTS>Cscript /nologo RestoreDTS.vbs SQL 
  d:\DTS\20040828\MyImportDTS_Aug_28_2004__8_29_33_263A.dts  
C:\RestoreDTS>REM echo d:\DTS\20040828\%i 
C:\RestoreDTS>Echo "Load Completed"  
"Load Completed" 
C:\RestoreDTS>Date/t  
Sun 09/19/2004 
C:\RestoreDTS>time/t  
12:07p

Now we can open Enterprise Manager and see whether all the DTS packages were restored. [Fig 1.7] Please refresh the "Local Package" folder [Fig 1.8]


[Fig 1.7]


[Fig 1.8]

In addition, you can see that if there are multiple files for the same package, those packages are stored as different versions. [Fig 1.9]


[Fig 1.9]

Conclusion

This article has examined how to take advantage of VB Script and MS-DOS batch files in order to restore multiple DTS packages stored in the form of structured storage files from one folder to a SQL Server box.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved