We help IT Professionals succeed at work.

SSIS 2008 vbscript task

I have an ssis package that has an execute process task compenant that runs a  exe(vb).. That executable is a VB app that zips up images in a folder and then moves the zipped  files to another folder and then the next components in SSIS FTP the contents of the new folder.. I would like to take the code out of the VB app and put it in a vbscript editor task in SSIS.. I need help with this.. Below is the entire VB module... Keep in mind I did not build this and I'm a SQL developer not VB developer.. I also have very little experience with the vb script editor in BIDS 2008..
Imports System.IO.Packaging
Imports System.IO
Imports System.Text
Imports System.Configuration

Module ZipModule

    Dim zip As Package
    'Dim zipPath As String = "K:\eDreamz\DataLoads\ZipFiles\"
    Dim zipPath As String = "D:\eDreamz\DataLoads\ZipFiles\"

    Dim zipName As String
    'Dim zipDelete As String = "K:\eDreamz\DataLoads\ZipFiles"
    Dim zipDelete As String = "D:\eDreamz\DataLoads\ZipFiles"

    Sub main()
        'Delete existing zip files....

        zipName = "Catalog Cover JPGs"
        zipName = "Detail Images"
        zipName = "Thumbnail Images"

    End Sub

    Private Sub ZipFiles()

        Dim zipFullPath As String

        zipFullPath = zipPath + zipName + ".zip"

        'Open the zip file if it exists, else create a new one 
        'Dim zip As Package
        zip = ZipPackage.Open(zipFullPath, _
             IO.FileMode.OpenOrCreate, IO.FileAccess.ReadWrite)

        'Add as many files as you like:

        'AddToArchive(zip, fileToAdd)
        'AddToArchive(zip, "C:\TEMP\Compression\Compress Me2.txt")
        'AddToArchive(zip, "C:\TEMP\Compression\Compress Me3.txt")

        zip.Close() 'Close the zip file

    End Sub

    Private Sub FindFilesTest(ByVal zipX As String)
        'Dim HP_Location = "G:\E-dreamz Images\Catalog Cover JPGs"
        'Dim HP_Location = "G:\Tim\E-dreamz Images\" + zipX
        Dim HP_Location = "C:\Tim\E-Dreamz Images\" + zipX
        Dim filesToAdd As String

        Dim di As New System.IO.DirectoryInfo(HP_Location)

            For Each fi As System.IO.FileInfo In di.GetFiles
                'If fi.Name.ToUpper.EndsWith(UserName) Then
                'copy files
                'fi.CopyTo(System.IO.Path.Combine(JobsLocation, fi.Name), True)
                'fi.CopyTo(System.IO.Path.Combine(ArchiveLocation, fi.Name), True)

                'delete file

                'collect filenames (see the count below the loop)
                'End If
                filesToAdd = HP_Location + "\" + fi.Name

                AddToArchive(zip, filesToAdd)

        Catch ex As Exception
            'MsgBox("Error creating zip: " & ex.Message)
        End Try

        'MessageBox.Show(FilesArray.Count, "# Files Processed")

    End Sub

    Private Sub AddToArchive(ByVal zip As Package, _
                         ByVal fileToAdd As String)

            'Replace spaces with an underscore (_) 
            Dim uriFileName As String
            uriFileName = fileToAdd.ToString

            'A Uri always starts with a forward slash "/" 
            Dim zipUri As String = String.Concat("/", _

            Dim partUri As New Uri(zipUri, UriKind.Relative)
            Dim contentType As String = _

            'The PackagePart contains the information: 
            ' Where to extract the file when it's extracted (partUri) 
            ' The type of content stream (MIME type):  (contentType) 
            ' The type of compression:  (CompressionOption.Normal)   
            Dim pkgPart As PackagePart = zip.CreatePart(partUri, _
                       contentType, CompressionOption.Normal)

            'Read all of the bytes from the file to add to the zip file 
            Dim bites As Byte() = File.ReadAllBytes(fileToAdd)

            'Compress and write the bytes to the zip file 
            pkgPart.GetStream().Write(bites, 0, bites.Length)

        Catch ex As Exception
            'MsgBox("Add to Zip File error: " & ex.Message)
        End Try

    End Sub

    Private Sub DeleteZipFile()

        Dim di As New System.IO.DirectoryInfo(zipDelete)
        For Each fi As System.IO.FileInfo In di.GetFiles
            'delete files


    End Sub

End Module

Open in new window

Watch Question

ValentinoVBI Consultant
Most Valuable Expert 2011

To be honest, I wouldn't port that entirely to an SSIS script task just as it is.  In the end you'll be better off if you'd created an SSIS package from scratch (or add components to the current one), whilst imitating the behaviour of the old VB app.

Here's an article that should help you get started: http://dataintegrity.wordpress.com/2009/10/13/dynamically-zipping-files-in-ssis/

Another possibility is to use the final step in the SSIS task launch a Robo-FTP script that does both the zip compression and the FTP step for you.  Then use the DBQUERY command in Robo-FTP to execute a SQL statement that completes any remaining database work.


The FTP part is not the issue.. I need a SSIS VB script task that zips files in a folder and moves the zipped files somewhere else..

I was just trying to think of an outside-the-box way to accomplish the same goal, by having the FTP automation program do the zipping and file moving and FTPing.


Thanks I appreciate that but I still dont know how to stick that thing in a VB script task.. I plan to read the article that  Valanetino sent me to and see if I can figure it out.. You have to be both a SQL Developer AND a VB developer these days I guess
BI Consultant
Most Valuable Expert 2011
Well, they are both called "developer" after all... :) (though I agree that there are probably more differences than similarities)

Don't hesitate to post follow-up question if you're not able to get it to work by reading that article, we'll try to help out!