locked
Script Task - Remotely Zipping Files & Catching Errors RRS feed

  • Question

  • As part of  SSIS Soultion i'm working on we're wanting to ZIP (using winzip) some files once they've extracted.  However the zipping application is not necessarily going to be on the SSIS box so i've implemented a Remote Procedure call using WMI within a script task as part of the Control Flow. 

    I've got it working for the most part but the problem i'm having is trying to catch an error if either the source or destination file paths are incorrect/invalid.

    If i put an incorrect file path for the zipping application then the Task Fails as it should, however if the file paths are not valid the task is still comign back as successfull where as i really want it to fail.

    If i run the command in command prompt on my machine then winzip is throwing an error, but i'm usure how i can catch this in the code and propogate it up to fail the Task

    Here's the code i'm using which i've utilised from various other threads i've found

    Any help very much appreciated

    Public Sub Main()
    
            Dim retValue As String
    
            Dim sComputer As String = Dts.Variables("_ZipTargetMachine").Value.ToString
            Dim bTrusted As Boolean = Dts.Variables("_ZipTargetMachineTrustedConnection").Value
            Dim sUser As String = Dts.Variables("_ZipTargetMachineUser").Value.ToString
            Dim sPassword As String = Dts.Variables("_ZipTargetMachinePassword").Value.ToString
    
            Dim sCommand As String = Dts.Variables("_ZIP_Command").Value.ToString
            
            'Use a try catch block to cater for any exceptions 
            Try
                retValue = RunCommand(sCommand, sComputer, bTrusted, sUser, sPassword)
    
                'Determine based on the value returned if the script task should fail or succeed 
                Select Case retValue
                    'If 0 is returned the process was successfully executed 
                    Case "0"
                        Dts.TaskResult = ScriptResults.Success
    
                        'If 2 is returned access to the remote machine was denied 
                    Case "2"
                        Dts.Events.FireError(2, "Zipping Files", "Access(denied)", Nothing, Nothing)
                        Dts.TaskResult = ScriptResults.Failure
    
                        'If 3 is returned the account specified has insufficient privileges to perform the action 
                    Case "3"
                        Dts.Events.FireError(2, "Zipping Files", "Insufficient(privilege)", Nothing, Nothing)
                        Dts.TaskResult = ScriptResults.Failure
    
                        'If 8 is eturned an unknown failure occured 
                    Case "8"
                        Dts.Events.FireError(2, "Zipping Files", "Unknown(failure)", Nothing, Nothing)
                        Dts.TaskResult = ScriptResults.Failure
    
                        'If 9 is returned the path for the executable was not found 
                    Case "9"
                        Dts.Events.FireError(2, "Zipping Files", "Path(Not found)", Nothing, Nothing)
                        Dts.TaskResult = ScriptResults.Failure
    
                        'if 21 is returned an invalid parameter was specified 
                    Case "21"
                        Dts.Events.FireError(2, "Zipping Files", "Invalid(parameter)", Nothing, Nothing)
                        Dts.TaskResult = ScriptResults.Failure
                End Select
    
            Catch ex As Exception
                'Fire an error event if an exception occurs 
                Dts.Events.FireError(1, "Zipping Files", ex.ToString, Nothing, Nothing)
                Dts.TaskResult = ScriptResults.Failure
            End Try
    
    
    
        End Sub
    
        Public Function RunCommand(ByVal strCommand As String, ByVal strMachineName As String, ByVal boolTrusted As Boolean, ByVal strUserName As String, ByVal strPassword As String) As String
    
            Try
                Dim options As New System.Management.ConnectionOptions
    
                If boolTrusted = True Then
                    options.Impersonation = ImpersonationLevel.Impersonate
                    options.EnablePrivileges = False
                Else
                    options.Username = strUserName
                    options.Password = strPassword
                End If
    
                Dim path As New System.Management.ManagementPath("\\" & strMachineName & "\root\cimv2:Win32_Process")
                Dim scope As New System.Management.ManagementScope(path, options)
    
                scope.Connect()
    
                Dim opt As New System.Management.ObjectGetOptions()
                Dim classInstance As New System.Management.ManagementClass(scope, path, opt)
    
                Dim inParams As System.Management.ManagementBaseObject = classInstance.GetMethodParameters("Create")
                inParams("CommandLine") = strCommand
    
                ' Execute the method and obtain the return values. 
                Dim outParams As System.Management.ManagementBaseObject = classInstance.InvokeMethod("Create", inParams, Nothing)
                Return CStr(outParams("returnValue"))
    
            Catch ex As ManagementException
                Dts.Events.FireError(1, "Zipping Files", ex.ToString, Nothing, Nothing)
                Return "1"
            End Try
    
        End Function
    

     

    Tuesday, November 1, 2011 10:14 AM

Answers

  • I think WinZip does not raise this as error, again, use the code in the url I provided to parse its output: http://social.msdn.microsoft.com/forums/en-US/vblanguage/thread/76cc913a-1b0a-402c-9320-54e31e8fc3bb/
    Arthur My Blog
    By: TwitterButtons.com
    • Proposed as answer by Eileen Zhao Monday, November 7, 2011 8:30 AM
    • Marked as answer by Matt 205 GTi Monday, November 7, 2011 9:10 AM
    Thursday, November 3, 2011 4:24 PM

All replies

  • Hi Matt 205 GTi,

    please refer to the following articles and hope that helps:

    http://consultingblogs.emc.com/jamiethomson/archive/2005/11/03/SSIS_3A00_-Throwing-errors-from-script-task_2F00_component.aspx

    http://msdn.microsoft.com/en-us/library/ms136131.aspx

    Please feel free to let me know if I misunderstand.

    Thanks,
    Eileen

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.



    Thursday, November 3, 2011 8:41 AM
  • Hi Eilieen

    No i'm afraid its not the reporting of the error detail that's the problem, its actually catching the error in the first place that the code isn't doing. Ie it's not throwign an error at all where as it should be.

     

     

    Thursday, November 3, 2011 2:57 PM
  • Why not to simply validate the file path before attempting to zip?

    So does the below code catches any error?

    Return CStr(outParams("returnValue"))

    Arthur My Blog
    By: TwitterButtons.com
    Thursday, November 3, 2011 3:29 PM
  • yes i suppose we could validate the filepath(s) independantly it means re writing the package/script so that we build up the zipping command in the script task rather than a variable, not a biggie, but i guess i'm just interested in understanding why the error isn't trickling up to the SSIS Task.

    Return CStr(outParams("returnValue"))

    The above WILL return an error if the path to the Winzip application is INCORRECT, however if the path to the winzip application IS CORRECT but either or the file paths for the source and destination are INVALID you receive NO error. However running the same command in a dos command prompt DOES result in an error message from winzip!

    cheers

    Thursday, November 3, 2011 3:44 PM
  • So you're using WinZip, so what is in your command line?

    And what do you see in DOS when the file path is incorrect?


    Arthur My Blog
    By: TwitterButtons.com
    Thursday, November 3, 2011 4:01 PM
  • OK, I think I now know your issue. To capture this error you need to read the command line result returned by WinZip (I do not use WInZip, but it does not matter).

    So the approach is in adding some more code. I was searching for a good example. I think you can work based on this post:

    http://social.msdn.microsoft.com/forums/en-US/vblanguage/thread/76cc913a-1b0a-402c-9320-54e31e8fc3bb/


    Arthur My Blog
    By: TwitterButtons.com
    Thursday, November 3, 2011 4:07 PM
  • command line is

    C:\Progra~1\WinZip\wzzip.exe -a -s"Password" -ycAES256 "\\servername\ExportData\Test folderXXX\Destination.zip" "\\servername\ExportData\Test folder\Source.txt"

    If the destination path is invalid you get the error
    Error: Could not create output file (\\servername\ExportData\Test folderXXX\Destination.zip)

    If the source path is invalid you get the error

    Warning: no files were found for this pattern that match your selection criteria: \\servername\ExportData\Test folderXXX\Source.txt
    Error: No files were found for this operation - Nothing to do. (\\servername\ExportData\Test folder\Destination.zip)

     

    Thursday, November 3, 2011 4:10 PM
  • I think WinZip does not raise this as error, again, use the code in the url I provided to parse its output: http://social.msdn.microsoft.com/forums/en-US/vblanguage/thread/76cc913a-1b0a-402c-9320-54e31e8fc3bb/
    Arthur My Blog
    By: TwitterButtons.com
    • Proposed as answer by Eileen Zhao Monday, November 7, 2011 8:30 AM
    • Marked as answer by Matt 205 GTi Monday, November 7, 2011 9:10 AM
    Thursday, November 3, 2011 4:24 PM
  • i think you must be right and that that's the case ie Winzip is not thowing any actual error but rather just returning a message instead

    thanks for your help

    Matt

    Monday, November 7, 2011 9:10 AM