[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Winform codebehind to vbscript in DTSPackage

Posted on 2008-11-06
Medium Priority
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

Accepted Solution

Ernariash earned 1000 total points
ID: 22896849
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

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 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