Winform codebehind to vbscript in DTSPackage

Posted on 2008-11-06
Last Modified: 2013-11-30
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.
Question by:slrine2000
    LVL 9

    Accepted Solution

    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


    Author Comment

    Thanks for the help in pointing me in the right direction. I got it working had to change the Shell to use "".

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now