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

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