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

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 = "10.142.117.202\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: " & file.name
            objDTSPackage.LoadFromStorageFile DTSPath & "\" & file.name, ""
            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
Next
 
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-2019 ASPAlliance.com  |  Page Processed at 2019-11-20 7:12:52 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search