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.
