I've created a Windows app in VS 2008 using vb.net, that executes an ssis package. It runs OK, here is my code:
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:
Thanks in advance if there are any takers on this!!
(P.S. - using SQL Server 2008 if it matters).
The above applies for the package when execution from command line. But if you execute the package from withing
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
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
--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).
answered Jan 18, 2013 at 03:42 PM