question

As you have mentioned the [Package.Execute Method ()][1] return s [DTSExecResult Enumeration][2]. 1. The value is determined by the real execution result of the package as described in the enum specification under the link above. It is depended on many factors, like error handling inside the package etc. Also the `DTSExecResult` can be forced inside the package by setting the **`ForceExecutionResult`** property of the Package. 2. As mentioned above, the value can be forced by setting the `ForceExecutionResult` property of the package, but cannot be set to custom value as it is a fixed enum. If you want to get details of the package execution lie the reasons of the failure etc., you can configure custom logging in the package and the use the logs to determine the reasons for failure etc. 3. As per above, it is not possible to return the value directly. But if you execute a master package which executes child package, it is possible to set Master's package variable value inside the child package. In this way you can for example determine details in the master package about execution in the child package for example. But again this this is not how to return the value out of the package. The above applies for the package when execution from command line. But if you execute the package from withing `C#` or ` VB.NET` code, take a look on the [overloaded DtsContainer.Execute Method][3], it takes additional parameters and pass Variables or objects implementing IDTSEvents or IDTSLogging interfaces. Using this you can implement objects which will collect data from inside the package being executed and you can pass the data back to the code which originally executed the package. [1]: http://technet.microsoft.com/cs-cz/library/ms190901(v=sql.100).aspx [2]: http://technet.microsoft.com/cs-cz/library/microsoft.sqlserver.dts.runtime.dtsexecresult(v=sql.100).aspx [3]: http://technet.microsoft.com/cs-cz/library/microsoft.sqlserver.dts.runtime.dtscontainer.execute(v=sql.100).aspx

Pavel, thanks for your detailed response. Since I made my post, i did find out about the ForceExecutionResult, which as you point out doesn't help as far as sending custom value back to the vb.net code. And I was trying to avoid custom logging. I think everyone in my shop is a newbie to SSIS, and wouldn't know what they were looking at in a custom log anyhow (including me, the log creator!). Your idea in # 3 looks like worth trying sometime. And I will definitely examine the overloaded DtsContainer.Execute method. But you know, I finally did discover (all on my own - nothing from Google or MSDN documentation or nayhwre else, I might add), that there IS a way for the Windows app/ vb.net code to retrieve custom values back that can give more specific info on package execution. The Package object has a Variables method that can get or set variable values from the ssis package. You can use the value of a user variable created specifically to hodl a value indicating some status of task processing. For example, my updated vb.net function in the Windows app: Module ExecuteSSISPkg Function ExecuteSSIS(ByVal strPackageLocation) 'this funtion executes an SSIS package, 'and returns the results of the execution 'create variables & assign values Dim pkgLocation As String Dim pkg As New Package Dim app As New Application Dim pkgResults As New DTSExecResult pkgLocation = strPackageLocation pkg = app.LoadPackage(pkgLocation, Nothing) 'execute the package pkgResults = pkg.Execute 'return the result of package execution 'Return code of 0 means success If pkgResults = 0 Then Return pkgResults Else 'anything besides a 0 is a problem/task failure of some kind. 'The package user variable "TaskResult" holds an integer 'customized in the package to reflect more specifically the 'nature of the problem. It must be evaluated in the calling module pkgResults = **pkg.Variables.Item("TaskResult").Value** MsgBox("pkgResults = " & pkgResults) Return pkgResults End If End Function Note the "pkg.Variables.Item("TaskResult").Value". TaskResult is a user variable I created in the ssis package, with an int32 type. When I ran the package, if no file was found to process, I assigned taskResult the arbitrary value of 18. If the Script task that was supposed to check for the file threw a general exception, I assgned value 21. If the data flow failed to transfer the data to a flat file, I assigned TaskResult value of 20. If the File System Task failed to delete the file, TaskResult = 19. In all cases I failed the task & the package. The pkg.Variables in the vb.net calling function actually gets the value of TaskResult, and I am able to actuualy finish processing by displaying an adequate msg to our computer operators. For instance, in my Start button on my app that executed the function, here's how the program wraps after it's run: Private Sub cmdStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdStart.Click 'This program executes an SSIS package named GLODAR 'that transfers an ODAR Excel report to a text file, 'which will subsequently be transfered to the mainframe 'for processing Dim strSSISPackagePath As String strSSISPackagePath = "C:\GLODAR\GLODAR\GLODAR.DTSX" 'declare a variable to contain the results of 'the SSIS package execution Dim intResult As Integer 'Execute the package and store the result intResult = ExecuteSSIS(strSSISPackagePath) MsgBox("GLODAR pkg finished executing, and intResult is: " & intResult.ToString) 'Use intResult to display a message about the 'results of processing. A value of 0 indicates 'the SSIS package completed successfully. 'Anything else returned indicates a failure 'somewhere in the package that must be 'investigated and resolved. If intResult = 0 Then MsgBox("GLODAR_VB has completed successfully." & vbCrLf _ & "Click EXIT to terminate this program" & vbCrLf & vbCrLf _ & "Run mainframe program GLODAR.") ElseIf intResult = 18 Then MsgBox("The SSIS package GLODAR in the ODAR folder" & vbCrLf _ & "failed to find an Excel report named GLODAR" & vbCrLf _ & "in the ODAR folder to process and therefore" & vbCrLf _ & "terminated. Make sure an Excel file named" & vbCrLf _ & "GLODAR is in the ODAR folder, and run GLODAR_VB again." & vbCrLf & vbCrLf _ & "Click OK and then click EXIT to terminate this program") ElseIf intResult = 21 Then MsgBox("The SSIS package GLODAR in the ODAR folder" & vbCrLf _ & "failed. The task that failed was VERIFY ODAR" & vbCrLf _ & "EXCEL FILE EXISTS. Error information is in the" & vbCrLf _ & "ErrorLog file in the ODAR folder. Notify a" & vbCrLf _ & "programmer, and have them review the ErrorLog file" & vbCrLf _ & "to fix the error. After the problem has been corrected" & vbCrLf _ & "rerun GLODAR_VB." & vbCrLf & vbCrLf _ & "Click OK and then click EXIT to terminate this program") ElseIf intResult = 20 Then MsgBox("The SSIS package GLODAR in the ODAR folder" & vbCrLf _ & "failed. The task that failed was MOVE ODAR EXCEL" & vbCrLf _ & "REPORT TO TXT FILE Error information is in the" & vbCrLf _ & "ErrorLog file in the ODAR folder. Notify a" & vbCrLf _ & "programmer, and have them review the ErrorLog file" & vbCrLf _ & "to fix the error." & vbCrLf & vbCrLf _ & "Click OK and then click EXIT to terminate this program") ElseIf intResult = 19 Then MsgBox("The SSIS package GLODAR in the ODAR folder" & vbCrLf _ & "failed. The task that failed was DELETE EXCEL REPORT" & vbCrLf _ & "SOURCE FILE. Error information is in the" & vbCrLf _ & "ErrorLog file in the ODAR folder. Notify a" & vbCrLf _ & "programmer, and have them review the ErrorLog file" & vbCrLf _ & "to fix the error." & vbCrLf & vbCrLf _ & "Click OK and then click EXIT to terminate this program") End If End Sub --Again,I'm going to check out some of your recommendations, but you and others may like to know that YES, there IS a way to directly access the results of an ssis package directly from a calling Windows program. (I don't think this is generally known). Thanks again.