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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


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\ > 

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" 
Sun 09/19/2004 
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_
C:\RestoreDTS>Cscript /nologo RestoreDTS.vbs SQL 
C:\RestoreDTS>Cscript /nologo RestoreDTS.vbs SQL 
C:\RestoreDTS>Cscript /nologo RestoreDTS.vbs SQL 
C:\RestoreDTS>Cscript /nologo RestoreDTS.vbs SQL 
C:\RestoreDTS>REM echo d:\DTS\20040828\%i 
C:\RestoreDTS>Echo "Load Completed"  
"Load Completed" 
Sun 09/19/2004 

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]


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