Tuesday, March 20, 2012

Active X script help in a DTS package

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.

No comments:

Post a Comment