Systems Engineering and RDBMS

Importing DTS packages in SQL Server 2000

Posted by decipherinfosys on February 10, 2007

In SQL Server 2000, if you have a requirement to copy/transfer multiple DTS packages from one instance to the other, doing it manually becomes cumbersome. There are utilities in the market that do that for you but what if you have to make this import a part of your install? If you have such a requirement, then you can save your DTS packages as files and then write up a simple VB Script and call it through cscript to load up the packages. You can put this process into your install shield code to give a better end user experience.

‘ Usage: cscript ImportPackages.vbs <sql server name> <sql server userid> <sql server password>

‘ Example: cscript ImportPackages.vbs microndt2978-2 sa password

And here is the code for the ImportPackages.vbs (we use this in the case of slot_info in order to load the DTS packages):

option explicit

‘ ImportPackages.vbs — import DTS packages from .dts files.

‘ Usage: cscript ImportPackages.vbs <sql server name> <sql server userid> <sql server password>

‘ Example: cscript ImportPackages.vbs microndt2978-2 sa password

‘ This script imports all packages from the folder specified below to the
‘ specified SQL Server.


const PACKAGE_FOLDER = “DTS Packages”

dim oPackage
dim oFile
dim sPath
dim iCount
dim sPackageName
dim sServer
dim sUserid
dim sPassword
dim oArgs

‘ Validate arguments.
set oArgs = wscript.Arguments

if oArgs.count < 3 then
wscript.echo “Usage: cscript ImportPackages.vbs <sql server name> <sql server userid> <sql server password>”
wscript.quit 1
end if

sServer = oArgs(0)
sUserid = oArgs(1)
sPassword = oArgs(2)

‘ Obtain the path that the script is run in. The DTS package folder
‘ is relative to that path.
sPath = left(wscript.scriptfullname, len(wscript.scriptfullname) – len(wscript.scriptname))

dim fso
set fso = CreateObject(“Scripting.FileSystemObject”)

dim oFolder

‘ Make sure the package folder exists.
if not fso.FolderExists(sPath & PACKAGE_FOLDER) then
wscript.echo “Can’t find package folder ” & sPath & PACKAGE_FOLDER
wscript.quit 1
end if

set oFolder = fso.GetFolder(sPath & PACKAGE_FOLDER)

iCount = 0

‘ Iterate through the package files.
for each oFile in oFolder.Files
wscript.echo “Importing ” & oFile.Path
set oPackage = CreateObject(“DTS.Package2”)

‘ Get the package name, which is the name of the .dts file
‘ without the .dts extension.
sPackageName = left(oFile.Name, len(oFile.Name) – 4)

‘ Delete the package, but don’t error out if it already doesn’t exist.
on error resume next
oPackage.RemoveFromSQLServer sServer, sUserid, sPassword, , , , sPackageName
on error goto 0

‘ Load the package from the .dts file.
oPackage.LoadFromStorageFile oFile.Path, “”

‘ Save the package to SQL Server.
oPackage.SaveToSQLServer sServer, sUserid, sPassword

iCount = iCount + 1

‘ Release the package object. It can’t be reused.
set oPackage = Nothing

wscript.echo “Successfully imported ” & iCount & ” packages.”
wscript.quit 0
An excellent article on transferring the DTS packages in SQL 2000 is available at Darren Green’s site:

Sorry, the comment form is closed at this time.

%d bloggers like this: