We help IT Professionals succeed at work.
Get Started

SSIS 2008 vbscript task

2,499 Views
Last Modified: 2013-11-18
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
BI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE