Save DTS packages -- VBS

'To save DTS packages as per a certain search pattern for package names
' Author : Biju George
' Date   : 30-Jan-2013

Option Explicit

'On Error Resume Next
msgbox "Starting to save DTS packages..."

Dim srcFolder, dtsFolder ' Folder paths
Dim FSO, FLD, FIL ' File handles
dim sqlServerName ' SQL server
Dim dtsPackage ' As DTS.Package2
Dim objConnection ' As DTS.Connection2
Dim pck_count,pck_count2 ' Count flag
Dim objRegExpr,objRegExpr2 ' As regexp
Dim newPackageName, replaceString ' Ppackage names
dim dtsFileName ' DTS file name
Dim dtsFile ' DTS file
Dim oApplication   ' As DTS.Application
Dim oPackageSQLServer   ' As DTS.PackageSQLServer
Dim oPackageInfos   ' As DTS.PackageInfos
Dim oPackageInfo     ' As DTS.PackageInfo
Dim existingDBInstance ' Existing DB instance in the connection property
Dim oTask, oProperty ' DTS tasks and properties
Dim newPathName, replaceString2 ' New batch file path
Dim logFileName, logFile, logFSO ' Log file handles
Dim pckgPattern,btchFilePath ' Regular expression search strings

set logFSO = CreateObject("Scripting.FileSystemObject")

sqlServerName = "SQL_Server_Name"
pck_count = 0
pck_count2 = 0

pckgPattern = inputbox("Provide the source package name pattern (like My_DTS_package...)","Source Package Name Pattern")
btchFilePath = inputbox("Provide the source package batch file folder name (like D:\my_folder1\my_folder2\...)","Source Batch File Folder Name")

Set objRegExpr = New regexp
'objRegExpr.Pattern = "ABIJU"
objRegExpr.Pattern = pckgPattern
objRegExpr.Global = True
objRegExpr.IgnoreCase = True

Set objRegExpr2 = New regexp
'objRegExpr2.Pattern = "my_folder1"
objRegExpr2.Pattern = btchFilePath
objRegExpr2.Global = True
objRegExpr2.IgnoreCase = True

existingDBInstance = inputbox("Provide the existing database instance SID (like MYSID1 or MYSID2...)","Existing Database Instance SID")

Set FSO = CreateObject("Scripting.FileSystemObject")
Set srcFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Select the location folder for DTS file storage...", 0)
If (Not srcFolder Is Nothing) Then
dtsFolder = srcFolder.Items.Item.Path
Else
WScript.Quit
End If
Set srcFolder = Nothing
'MsgBox " Folder --> " & dtsFolder

logFileName = ""
logFileName = dtsFolder & "\" & "DTS_storage_log.log"

If  logFSO.FileExists(logFileName) Then 'FileSys.DeleteFile(DocFileName)
' logFSO.GetFile (logFileName)
set logFile = logFSO.OpenTextFile (logFileName,8) 'OpenTextFile(strFile [,IOMode (8=append, 1=Read, 2=Write) [,Create (True/False)]])
else
logFSO.CreateTextFile (logFileName)
set logFile = logFSO.OpenTextFile (logFileName,2)
end if
logFile.write (vbCrLf)
logFile.write ("---------DTS Storage logging on "  & Date & "---------")
logFile.write (vbCrLf)
logFile.write ("Folder where DTS files are stored : " & dtsFolder)
logFile.write (vbCrLf)
logFile.write ("Name pattern of DTS Packages to be saved : " & pckgPattern)
logFile.write (vbCrLf)
logFile.write ("Name of folder where batch files reside : " & btchFilePath)

'Get a reference to the folder you want to search
set FLD = FSO.GetFolder(dtsFolder)

Set oApplication = CreateObject("DTS.Application")
Set oPackageSQLServer = oApplication.GetPackageSQLServer(sqlServerName,"" ,"" , 256) 'DTSSQLStgFlag_UseTrustedConnection = 256

Set oPackageInfos = oPackageSQLServer.EnumPackageInfos("", True, "")  'EnumPackageInfos (PackageName,ReturnLatest,PackageID )

Set oPackageInfo = oPackageInfos.Next

pck_count = 0

'msgbox "match found numer#0 : " & pck_count

Do Until oPackageInfos.EOF

Set dtsPackage = CreateObject("DTS.Package2")

dtsPackage.LoadFromSQLServer sqlServerName, , ,256, , , , oPackageInfo.Name

'msgbox "match found numer#1 : " & pck_count

'msgbox "pkc count : " & pck_count & ". Package : " & cstr(oPackageInfo.Name)

replaceString = "STORED_BKUP"
replaceString2 = "TMPFLDR"
newPackageName = ""

        if objRegExpr.Test(cstr(oPackageInfo.Name)) then

logFile.write (vbCrLf)
logFile.write ("DTS package getting saved : " & oPackageInfo.Name)

                newPackageName  = objRegExpr.replace(cstr(oPackageInfo.Name),replaceString)

'msgbox "match found. pck count : " & pck_count & ". Package : " & cstr(oPackageInfo.Name) & ". New : " & newPackageName      

                pck_count = pck_count + 1
dtsFileName = ""
dtsFileName = dtsFolder & "\"& newPackageName & ".dts"

logFile.write (vbCrLf)
logFile.write ("New Package Name in the file : " & newPackageName)
logFile.write (vbCrLf)
logFile.write ("DTS File Name in storage location : " & dtsFileName)

Set objConnection = dtsPackage.Connections
For Each objConnection In dtsPackage.Connections
'       if objConnection.DataSource = existingDBInstance then
logFile.write (vbCrLf)
logFile.write ("Existing DB instance SID in the DTS packages : " & objConnection.DataSource)
      objConnection.DataSource = "TEMPDB"
objConnection.Password = ""
' end if
Next

newPathName = ""

'changes in task properties
Set oTask = dtsPackage.Tasks
For each oTask in dtsPackage.Tasks
Set oProperty = oTask.Properties
For Each oProperty In oTask.Properties
if oProperty.Name = "ProcessCommandLine" then
'MsgBox "PropertyName: " &  oProperty.Name & " Value="  & oProperty.Value
if objRegExpr2.Test(cstr(oProperty.Value)) then
                newPathName = objRegExpr2.replace(cstr(oProperty.Value),replaceString2)
logFile.write (vbCrLf)
logFile.write ("Existing path for batch file : " & oProperty.Value)
logFile.write (vbCrLf)
logFile.write ("New path for batch file : " & newPathName)
oProperty.Value = newPathName
end if
end if
Next
Next

Set objConnection = Nothing


dtsPackage.SaveToStorageFileAs newPackageName,dtsFileName

Set dtsPackage = Nothing
Set objConnection = Nothing
Set oTask = Nothing
Set oProperty = Nothing
else
'logFile.write (vbCrLf)
'logFile.write ("DTS pacakge match not found : " + dtsPackage.Name)
pck_count2 = pck_count2 + 1
end if
Set oPackageInfo = oPackageInfos.Next

'if pck_count = 0 then
'msgbox "just quit without looping"
'exit do
'end if
Loop

logFile.write (vbCrLf)
logFile.write ("Total packages saved : " & pck_count)
logFile.write (vbCrLf)
logFile.write ("Total packages that did not match the criteria : " & pck_count2)
logFile.write (vbCrLf)

'Clean up and free resources
Set oApplication = Nothing
Set oPackageSQLServer = Nothing
Set oPackageInfos = Nothing
Set oPackageInfo = Nothing
Set dtsPackage = Nothing
Set FSO = Nothing

' Main = DTSTaskExecResult_Success

msgbox "All Packages saved. Check log file " & logFileName & " for details..."

logFile.close
Set logFile = Nothing
Set logFSO = Nothing

If Err.Number <> 0 Then
logFile.write (vbCrLf)
logFile.write ("Error : " & Err.Description)
Err.Clear
End If
WScript.Quit