x

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).

more ▼

asked Jan 11, 2013 at 11:52 PM in Default

avatar image

Badaveg
50 4 4 7

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jan 17, 2013 at 06:44 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 18, 2013 at 03:42 PM

avatar image

Badaveg
50 4 4 7

Hi, as I have mentioned in my answer.. There is no way to directly read the values if the package is being executed from command line.

Of course, in the .Net code you have much more possibilities and one of them is the overloaded Execute method.

As you have mentioned. The package has Variables collection inside it, which can be accessed by the .Net code and this is also related to the overloaded Execute method, where you can pass the variables for example too or pass the mentioned events handlers etc, where you can detect problems in real-time and much more.

The SSIS object model is very powerful as you can build complete packages during runtime.

And of course, it's very good, that you have found a way by yourself.

Jan 18, 2013 at 06:31 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1244
x449

asked: Jan 11, 2013 at 11:52 PM

Seen: 2223 times

Last Updated: Jan 18, 2013 at 06:32 PM

Copyright 2018 Redgate Software. Privacy Policy