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
Script Task - Remotely Zipping Files & Catching Errors
