Restore multiple DTS packages - Page 2

Step 4

Execute the batch file [Fig 1.4]

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

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]

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]

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]

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