Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 6, 2004

Restore multiple DTS packages - Page 2

By Muthusamy Anantha Kumar aka The MAK



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]





[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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM