Execute ssis package from Windows app - questions about the exit code
I've created a Windows app in VS 2008 using
vb.net, that executes an ssis package. It runs OK, here is my code: Function ExecuteSSIS() 'this funtion executes an SSIS package, 'and returns the results of the execution Dim pkgLocation As String Dim pkg As New Package Dim app As New Application Dim pkgResults As New DTSExecResult pkgLocation = "C:\GLODAR\GLODAR\GLODAR.DTSX" pkg = app.LoadPackage(pkgLocation, Nothing) pkgResults = pkg.Execute Return pkgResults End Function My question has to do with the value returned and stored in pkgresults. I'm understanding that it's limited to only a handful of integers, mainly 0 for success and 1 for package failure. I would like to know: 1. Just WHERE do these "exit codes" (or maybe they're "return values", I'm not sure which) come from? Are they a system variable the package returns to the calling windows app? If it's a system variable, which one? (I'm asking mainly from curiosity here). 2. Secondly, and this is the main thing I need to find out - can the values be changed somehow, to custom values I could set to provide more specific info when my package fails? For example, my package does three things (1) run Script task to verify existence of an excel file (2) Runs data flow task to transfer the Excel data to a flat file (3) executes a file System task to delete the Excel file. If any of these actions fail I want the package to fail, but I would like to return something more specific to the windows program than a 1 for package failure. Like say a "9" for "no Excel file present", "10" for "failed to transfer Excel report to flat file" and "12" for "failed to delete the Excel File". 3. I'm anticipating people will say customizing the exit code is just not possible. If that is the case, I've seen many examples on Google of executing a package and sending in variables as part of the input. Is there a way to get the generic package result of "1" for failure and also AN OUTPUT USER VARIABLE returned? If there's a way to assign a value to a user variable when some part of the package fails AND GET THAT RETURNED to my
vb.net program? If I could get a customized anything back I could have the Windows program display a specific message about why teh package failed, and not have to rely on the generic info that logging provides. Thanks in advance if there are any takers on this!! (P.S. - using SQL Server 2008 if it matters).
As you have mentioned the [Package.Execute Method ()] return s [DTSExecResult Enumeration]. 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], 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. :
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.