Deploying DTS package in SQL Server 2000
page 3 of 3
by Cyril Beschi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19719/ 20

Automated Deployment


Using VBScript


The DTS.Package object of VBScript exposes two methods - LoadFromStorageFile and SaveToSQLServer - which help to deploy DTS packages from physical files to DB server.  The following script serves the purpose.  The script loops through all the files with DTS extension in the DTS subdirectory where the script is located, and install the DTS packages one by one.


Option Explicit
Dim DBServerName, DBUserName, DBPassword
DBServerName = "\ARD54, 1297"
DBUserName = "sa"
DBPassword = "welcome01*"
Dim objFSO, objFile, file, objDTSPackage, DTSPath
DTSPath = ".\DTS"
Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each file In objFSO.GetFolder(DTSPath).Files
      If LCase(objFSO.GetExtensionName(file)) = "dts" Then
            Set objDTSPackage = CreateObject("DTS.Package")
            WScript.Echo "Creating DTS: " &
            objDTSPackage.LoadFromStorageFile DTSPath & "\" &, ""
            WScript.Echo Chr(9) & "DTS loaded successfully"
            objDTSPackage.SaveToSQLServer DBServerName, DBUserName, DBPassword
            WScript.Echo Chr(9) & "DTS created in the server successfully"
            Set objDTSPackage = Nothing
      End If
WScript.Echo "END"


When there are multiple versions in the physical DTS file, then GUID of the version to be deployed must be mentioned.  When there is only one version in the physical file, then the available version will be deployed.

View Entire Article

User Comments

No comments posted yet.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-07-23 4:06:30 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search