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.
Showing posts with label placed. Show all posts
Showing posts with label placed. Show all posts
Tuesday, March 20, 2012
Subscribe to:
Posts (Atom)