Load DTS packages -- VBS

'To create DTS packages from saved DTS files
' Author : Biju George
' Date   : 30-Jan-2013

Option Explicit

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

Dim srcFolder, dtsFolder, dtsPath ' 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 ' Count flag
Dim objRegExpr,objRegExpr2 ' As regexp
Dim newPackageName, replaceString ' Ppackage names
Dim newDBInstance ' New DB instance in the connection property
Dim newDBInstancePswd ' New DB instance password in the connection property
Dim newSchm1Pswd,newSchm4Pswd,newSchm2Pswd,newSchm3Pswd ' New DB instance schema passwords 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

set logFSO = CreateObject("Scripting.FileSystemObject")

sqlServerName = "SQL_Server_Name"
pck_count = 0

Set objRegExpr = New regexp
objRegExpr.Pattern = "STORED_BKUP"
objRegExpr.Global = True
objRegExpr.IgnoreCase = True

Set objRegExpr2 = New regexp
objRegExpr2.Pattern = "TMPFLDR"
objRegExpr2.Global = True
objRegExpr2.IgnoreCase = True

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

logFileName = ""
logFileName = dtsFolder & "\" & "DTS_creation_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 Creation logging on "  & Date & "---------")
logFile.write (vbCrLf)
logFile.write ("Folder where DTS files reside : " & dtsFolder)
logFile.write (vbCrLf)
logFile.write ("Complete path of DTS files : " & dtsPath)

newDBInstance = inputbox("Provide the new database instance SID (like MYSID1 or MYSID2...)","New Database Instance SID")
'newDBInstancePswd = inputbox("Provide the new database instance password","New Database Instance Password")

newSchm1Pswd = inputbox("Provide the new DB instance My_Schema1 schema password","My_Schema1 schema Password")
newSchm4Pswd = inputbox("Provide the new DB instance My_Schema4 schema password","My_Schema4 schema Password")
newSchm2Pswd = inputbox("Provide the new DB instance My_Schema2 schema password","My_Schema2 schema Password")
newSchm3Pswd = inputbox("Provide the new DB instance My_Schema3 schema password","My_Schema3 schema Password")

'replaceString = "CRTD_FRM_BKUP"
replaceString = inputbox("Provide the new package name pattern (like My_new_DTS_package...)","New Package Name Pattern")
'replaceString2 = "TEMPFOLDER"
replaceString2 = inputbox("Provide the new folder for batch files (like my_folder3 or my_folder4...)" _
                          & chr(13) & "viz. D:\My folder1\My folder2\My folder3\my_batch_file.bat" _
                          & chr(13) & "to D:\Your folder1\Your folder2\Your folder3\your_batch_file.bat","New Folder/Path")

logFile.write (vbCrLf)
logFile.write ("New DB Instance               : " & newDBInstance)
logFile.write (vbCrLf)
logFile.write ("My_Schema1 Password : " & newSchm1Pswd)
logFile.write (vbCrLf)
logFile.write ("My_Schema4 Password : " & newSchm4Pswd)
logFile.write (vbCrLf)
logFile.write ("My_Schema2 Password : " & newSchm2Pswd)
logFile.write (vbCrLf)
logFile.write ("My_Schema3 Password : " & newSchm3Pswd)
logFile.write (vbCrLf)
logFile.write ("New DTS package name pattern : " & replaceString)
logFile.write (vbCrLf)
logFile.write ("New folder name for batch files : " & replaceString2)

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

'loop through the folder and get the file names
For Each FIL in FLD.Files
' MsgBox FIL.Name
' MsgBox FIL.Type
if UCase(right(FIL.Name, 4)) = ".DTS" then
Set dtsPackage = CreateObject("DTS.Package2")
dtsPackage.LoadFromStorageFile FIL.path,""
logFile.write (vbCrLf)
logFile.write ("DTS file name : " & FIL.Name)
logFile.write (vbCrLf)
logFile.write ("File type : " & FIL.Type)
logFile.write (vbCrLf)
logFile.write ("DTS file path : " & FIL.path)
logFile.write (vbCrLf)
logFile.write ("DTS package name in file : " & dtsPackage.Name)
Set objConnection = dtsPackage.Connections
For Each objConnection In dtsPackage.Connections
      if objConnection.DataSource = "TEMPDB" then
      objConnection.DataSource = newDBInstance
if objConnection.userid = "My_Schema1" then
objConnection.Password = newSchm1Pswd
elseif objConnection.userid = "My_Schema4" then
objConnection.Password = newSchm4Pswd
elseif objConnection.userid = "My_Schema2" then
objConnection.Password = newSchm2Pswd
elseif objConnection.userid = "My_Schema3" then
objConnection.Password = newSchm3Pswd
end if
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

' dtsPackage.SaveToSQLServer sqlServerName, , , 256, "", "", ""

newPackageName = ""

if objRegExpr.Test(cstr(dtsPackage.Name)) then
                newPackageName  = objRegExpr.replace(cstr(dtsPackage.Name),replaceString)
logFile.write (vbCrLf)
logFile.write ("New DTS pacakage name : " & newPackageName)
dtsPackage.SaveToSQLServerAs newPackageName, sqlServerName, , , 256, "", "", , nothing, 0
end if
Set dtsPackage = Nothing
Set objConnection = Nothing
Set oTask = Nothing
Set oProperty = Nothing
     
        pck_count = pck_count + 1
end if
next

'msgbox "Total packages created : " & pck_count
logFile.write (vbCrLf)
logFile.write ("Total packages created : " & pck_count)
logFile.write (vbCrLf)

Set FSO = Nothing

' Main = DTSTaskExecResult_Success

msgbox "All Packages loaded. 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