DTS package created to download FTP files. All task are performed
successfully, files are pulled view FTP task on to the destination folder.
I can see the file populated under the dest folder.
DTS package message displayed successfully loaded but file gets deleted from the destination folder.
Tasks performed-ActiveXscript,dynamic properties and FTP task.
1. vbscript
Function Main()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
txtstrWeek = inputbox("Enter the weekly date PRV414 you wish to import on (yyyymmdd format)")
i_Day = Cint(Mid(txtstrWeek ,7, 2 ))
i_Month = Cint(Mid(txtstrWeek ,5, 2 ))
i_Year = Cint(Left(txtstrWeek,4))
strWeek = DateSerial( i_Year , i_Month ,i_Day )
MyWeekDay = weekday(strWeek)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
DTSGlobalVariables("strDestinationDir").Value = "\\newpafs1\data reporting$\Reference_Files_State\PRV414"
DTSGlobalVariables("strSourceFTPDir").Value = "'PRV414W.BU.zip';'PROMISe';''"
DTSGlobalVariables("strLogFile").Value = "\\newpafs1\data reporting$\Reference_Files_State\PRV414\LOG\objPRV414Log.txt"
DTSGlobalVariables("strZipArchiveDir").Value = "\\newpafs1\data reporting$\Reference_Files_State\PRV414\Archive"
DTSGlobalVariables("FileName").Value = "PRV414W.BU.zip"
DTSGlobalVariables("glbFTPSource_wd").value = MyWeekDay
DTSGlobalVariables("RptWeek").Value = txtstrWeek
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Check to see if a log file exists if it does open it. If not create it.
'
Set objFSO = CreateObject("Scripting.FileSystemObject")
If not objFSO.FileExists(DTSGlobalVariables("strLogFile").Value ) then
Set master = objfso.CreateTextFile(DTSGlobalVariables("strLogFile").Value )
master.WriteLine "File CREATED @ " & now
master.WriteLine " In PRV414 Check Routine @ " & now
Else
Set master = objFSO.OpenTextFile((DTSGlobalVariables("strLogFile").Value ),8)
master.WriteLine
master.WriteLine "File OPENED @ " & now
master.WriteLine " In PRV414 Check Routine @ " & now
End if
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' check to see if DAT file exists
'
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists((DTSGlobalVariables("strDestinationDir").Value ) & "\PRV414MW.BU.dat") then
master.WriteLine "DTSTaskExecResult_FAILURE - prv414W.bu.dat EXISTS it should not exist at this point @ " & now
Main = DTSTaskExecResult_Failure
Else
master.WriteLine "DTSTaskExecResult_SUCCESS - prv414W.bu.dat DOES NOT EXISTS at this point @ " & now
Main = DTSTaskExecResult_Success
End if
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' check to see if ZIP file exists
'
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists((DTSGlobalVariables("strDestinationDir").Value ) & "\" & (DTSGlobalVariables("FileName").Value ) ) then
master.WriteLine "DTSTaskExecResult_FAILURE - prv414W.bu.zip EXISTS it should not exist at this point @ " & now
Main = DTSTaskExecResult_Failure
Else
master.WriteLine "DTSTaskExecResult_SUCCESS - prv414W.bu.zip DOES NOT EXISTS at this point @ " & now
Main = DTSTaskExecResult_Success
End if
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
master.WriteLine "FILE CLOSED @" & now
master.close
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
set objFSO = nothing
set FSO = nothing
set oFSO = nothing
set master = nothing
set objFSOFolder = nothing
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Function
2. DYNAMIC PROPERITIES CREATED AS MENTIONED IN VBSCRIPT
3. FTP task has connection to the FTP server and destination folder mapped.
File is being downloaded to dest folder but upon completion is deleted.
Please let me know if script is coded incorrectly.
Thanks in advance
spag
successfully, files are pulled view FTP task on to the destination folder.
I can see the file populated under the dest folder.
DTS package message displayed successfully loaded but file gets deleted from the destination folder.
Tasks performed-ActiveXscript,dynamic properties and FTP task.
1. vbscript
Function Main()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
txtstrWeek = inputbox("Enter the weekly date PRV414 you wish to import on (yyyymmdd format)")
i_Day = Cint(Mid(txtstrWeek ,7, 2 ))
i_Month = Cint(Mid(txtstrWeek ,5, 2 ))
i_Year = Cint(Left(txtstrWeek,4))
strWeek = DateSerial( i_Year , i_Month ,i_Day )
MyWeekDay = weekday(strWeek)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
DTSGlobalVariables("strDestinationDir").Value = "\\newpafs1\data reporting$\Reference_Files_State\PRV414"
DTSGlobalVariables("strSourceFTPDir").Value = "'PRV414W.BU.zip';'PROMISe';''"
DTSGlobalVariables("strLogFile").Value = "\\newpafs1\data reporting$\Reference_Files_State\PRV414\LOG\objPRV414Log.txt"
DTSGlobalVariables("strZipArchiveDir").Value = "\\newpafs1\data reporting$\Reference_Files_State\PRV414\Archive"
DTSGlobalVariables("FileName").Value = "PRV414W.BU.zip"
DTSGlobalVariables("glbFTPSource_wd").value = MyWeekDay
DTSGlobalVariables("RptWeek").Value = txtstrWeek
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Check to see if a log file exists if it does open it. If not create it.
'
Set objFSO = CreateObject("Scripting.FileSystemObject")
If not objFSO.FileExists(DTSGlobalVariables("strLogFile").Value ) then
Set master = objfso.CreateTextFile(DTSGlobalVariables("strLogFile").Value )
master.WriteLine "File CREATED @ " & now
master.WriteLine " In PRV414 Check Routine @ " & now
Else
Set master = objFSO.OpenTextFile((DTSGlobalVariables("strLogFile").Value ),8)
master.WriteLine
master.WriteLine "File OPENED @ " & now
master.WriteLine " In PRV414 Check Routine @ " & now
End if
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' check to see if DAT file exists
'
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists((DTSGlobalVariables("strDestinationDir").Value ) & "\PRV414MW.BU.dat") then
master.WriteLine "DTSTaskExecResult_FAILURE - prv414W.bu.dat EXISTS it should not exist at this point @ " & now
Main = DTSTaskExecResult_Failure
Else
master.WriteLine "DTSTaskExecResult_SUCCESS - prv414W.bu.dat DOES NOT EXISTS at this point @ " & now
Main = DTSTaskExecResult_Success
End if
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' check to see if ZIP file exists
'
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists((DTSGlobalVariables("strDestinationDir").Value ) & "\" & (DTSGlobalVariables("FileName").Value ) ) then
master.WriteLine "DTSTaskExecResult_FAILURE - prv414W.bu.zip EXISTS it should not exist at this point @ " & now
Main = DTSTaskExecResult_Failure
Else
master.WriteLine "DTSTaskExecResult_SUCCESS - prv414W.bu.zip DOES NOT EXISTS at this point @ " & now
Main = DTSTaskExecResult_Success
End if
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
master.WriteLine "FILE CLOSED @" & now
master.close
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
set objFSO = nothing
set FSO = nothing
set oFSO = nothing
set master = nothing
set objFSOFolder = nothing
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Function
2. DYNAMIC PROPERITIES CREATED AS MENTIONED IN VBSCRIPT
3. FTP task has connection to the FTP server and destination folder mapped.
File is being downloaded to dest folder but upon completion is deleted.
Please let me know if script is coded incorrectly.
Thanks in advance
spag