Winform codebehind to vbscript in DTSPackage

I've created the following windows app code that will take a file from a directory copy it to a backup folder, unzip the file to a different directory folder and then delete the original file. This works when running it using VS2005. What I want to do is to take this and have it work as an ActiveX Script Task within a DTS package in SQL 2000.  I've tried pasting it into the DTSPackage script and removing or modifying the lines where it would error at only to end up getting the "package failed" when I tried to execute the task.

Public Class Unzip
Private Sub Unzip_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    End Sub

    Sub Unzip()
         Dim UnzipExePath = Chr(34) & "C:\UnZip\unCIP.exe" & Chr(34)
        Dim UnzipfilePath = Chr(34) & "C:\Extractunzip" & Chr(34)
        Dim filenames As String
        Dim ZipfilePath = "C:\UnZip\Test\"
        filenames = GetAllFilenames(ZipfilePath, ";")
        Dim filearray()
        filearray = Split(filenames, ";")
        'ZipfilePath = ZipfilePath & sfilename
        For i As Integer = 0 To filearray.Length - 1
            Dim file As String
            file = ZipfilePath & filearray(i)
            'Copy file to backup location
            Dim myfileInfo As New System.IO.FileInfo(file)

            If myfileInfo.Exists = True Then
                Dim Destination As String
                Destination = "C:\Extractunzip\Backup\" & Date.Now.ToString("yyyyMMdd") & filearray(i)
                System.IO.File.Copy(file, Destination, True) ' copy to the target file

                'Unzip file to pickup folder
                Dim retval As Double
                retval = Shell(UnzipExePath & " " & Chr(34) & file & Chr(34) & " " & UnzipfilePath)

                'Delete file from drop-off folder
                System.IO.File.Delete(file) 'delete source file

            End If
   End Sub

    Public Function GetAllFilenames(ByVal Directory As String, Optional ByVal Delimiter As String = vbCrLf) As String
        Dim SRet As String
        Dim sFName As String
        sFName = Dir$(Directory & "*.*", vbReadOnly Or vbHidden Or vbDirectory)

        Do Until Len(sFName) = 0
            If Len(SRet) > 0 Then
                SRet = SRet & Delimiter
            End If
            SRet = SRet & sFName
            sFName = Dir$()

        GetAllFilenames = SRet
    End Function

End Class

Thanks for your help.
Who is Participating?
ErnariashConnect With a Mentor Commented:
You are using MS SQL 2000. The DTSPackage scripts are VBScripts, and your code is regular Visual Basic.NET.
Try to use Scripting FileSystemObject for the same purpose. The declarations Dim filenames As String as not allowed you need to use Dim filenames since all variables in VBScripts   are of type variant that can store different types of data.
For testing this is what I usually do: Copy your script into a text file and rename it as vbs file ( nametest.vbs). When your script works without errors then you can use the same code into the DTSPackage scripts.
I have not tested this code but will give you an idea, there are a lot of samples on the internet for the FileSystemObject.
Thanks Ernesto

Function Main()
        Dim UnzipExePath
        Dim UnzipfilePath 
        Dim filenames ' As String
        Dim ZipfilePath  
	Dim FSO 'As Scripting.FileSystemObject
	Dim File 'As File
	Dim colFiles 
	Dim objFolder 
	Dim Destination' As String
        Dim retval' As Double
   	Set FSO = CreateObject("Scripting.FileSystemObject")
	UnzipfilePath = "C:\Extractunzip"  
	Set objFolder = FSO.GetFolder(UnzipfilePath )
	/// loop all files on the folder
    	Set colFiles = objFolder.Files
	For Each objFile in colFiles
               Destination = "C:\Extractunzip\Backup\" &  UCase(MonthName(Month(Now()))) & "_" & Right("0" & Day(Now()), 2)   & objFile.Name
		objFile.Copy ("Destination")
   		'Unzip file to pickup folder
                retval = Shell(UnzipExePath & " " & Chr(34) & file & Chr(34) & " " & UnzipfilePath)
                'Delete file from drop-off folder
                objFile.Delete 'delete source file
	Set objFile = Nothing
	Set FSO = Nothing
	Set objFolder =   Nothing
	Main = DTSTaskExecResult_Success
End Function

Open in new window

slrine2000Author Commented:
Thanks for the help in pointing me in the right direction. I got it working had to change the Shell to use "".
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.