I have a file that gets placed on our ftp site once a week. The file is named achconsxx.dat
The xx is the fiscal week meaning the file one week looks like achcons18.dat and the next week it will look like achcons19.dat
I am trying use an ActiveX script in my DTS package to unzip that file and rename to achcons.dat so I can import.
My code looks like this so far:
Function Main()
Dim objWshshell, objFso
Dim strCmd, strSubject, strMessage, strFileattachment, rc
DTSGlobalVariables("gv_strWklydir").value = "\\E$\Update\Data\" ' Weekly directory
DTSGlobalVariables("gv_strACHFile").value = "achcons" & DTSGlobalVariables("gv_strFW").value & ".zip" ' ACH Group weekly file
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objWshshell = CreateObject("WScript.Shell")
rc = 0
' Build the Winzip command
If (objFso.FileExists(DTSGlobalVariables("gv_strWklydir").value & DTSGlobalVariables("gv_strACHFile").value)) Then
strCmd = "winzip32.exe -e -o " & DTSGlobalVariables("gv_strWklydir").value &_
DTSGlobalVariables("gv_strACHFile") & " " & DTSGlobalVariables("gv_strWklydir").value
rc = objWshshell.Run(strCmd, 1, true)
If (objFso.FileExists(DTSGlobalVariables("gv_strWklydir").value & objFso.GetBaseName(DTSGlobalVariables("gv_strACHFile")) & ".dat")) Then
objFso.GetFile(DTSGlobalVariables("gv_strWklydir").value & objFso.GetBaseName(DTSGlobalVariables("gv_strACHFile")) & ".dat").Copy (DTSGlobalVariables("gv_strWklydir").value & "achcons.dat")
End If
Set objWshshell = Nothing
Set objFso = Nothing
End If
I also have an e-mail routine that sends me an e-mail if the file doesn't exist. The e-mail works so my code can't see the file it needs to unzip and rename.
Any help is appreciated.May refer to http://www.sqldts.com for any reference on activex using in DTS.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment