We help IT Professionals succeed at work.

SSIS 2008 vbscript task

cheryl9063
cheryl9063 asked
on
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....
        DeleteZipFile()

        zipName = "Catalog Cover JPGs"
        ZipFiles()
        zipName = "Detail Images"
        ZipFiles()
        zipName = "Thumbnail Images"
        ZipFiles()


    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:
        FindFilesTest(zipName)

        '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)
        Try

            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
                'fi.Delete()

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

                AddToArchive(zip, filesToAdd)

            Next
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            '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)
        Try


            '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("/", _
                       IO.Path.GetFileName(uriFileName))

            Dim partUri As New Uri(zipUri, UriKind.Relative)
            Dim contentType As String = _
                       Net.Mime.MediaTypeNames.Application.Zip

            '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
            Console.WriteLine(ex.Message)
            '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
            fi.Delete()

        Next


    End Sub

End Module

Open in new window

Comment
Watch Question

View Solution Only

ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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/
AlexPace

Commented:
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.
cheryl9063

Author

Commented:
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..
AlexPace

Commented:
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.
cheryl9063

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
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!