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
        Unzip()
    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
        Next
   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$()
        Loop

        GetAllFilenames = SRet
    End Function

End Class

Thanks for your help.
slrine2000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ErnariashCommented:
Hello
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
 
	Next
	Set objFile = Nothing
	Set FSO = Nothing
	Set objFolder =   Nothing
   
	Main = DTSTaskExecResult_Success
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slrine2000Author Commented:
Thanks for the help in pointing me in the right direction. I got it working had to change the Shell to use "Wscript.shell".
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.