Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    32

    Unanswered: 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_strWklyd ir").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_strWklyd ir").value & objFso.GetBaseName(DTSGlobalVariables("gv_strACHFi le")) & ".dat")) Then
    objFso.GetFile(DTSGlobalVariables("gv_strWklydir") .value & objFso.GetBaseName(DTSGlobalVariables("gv_strACHFi le")) & ".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.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    May refer to http://www.sqldts.com for any reference on activex using in DTS.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •