SSIS Script Task to capture EXIT codes from .bat file - How?

  • Evil Kraig F

    SSC Guru

    Points: 100851

    *Headthump*

    Alright, Mr. VB I'm not, and I'm okay with that, but I'm about to throttle this thing. Also, sorry for a longish absence from here, work's been nuts.

    Here's the scenario: I'm working against a third party app that I have to call a .bat file from. It's actually a bit deeper than that but let's start there. It returns multiple exit codes depending on what happens, and some of them the SSIS needs to consider a success, and some a failure.

    Due to this, the Execute Process Task Editor isn't working for me. I need to basically store the exit code and make decisions from there. With that as the overall scenario, allow me to simplify the case.

    First, two Batch files:

    TestBatch.bat

    EXIT /B 22

    TestBatch2.bat

    @ECHO OFF

    CALL Testbatch.bat

    ECHO "Return exit code from Testbatch.bat"

    ECHO "Errorlevel=%errorlevel%"

    ECHO %errorlevel%

    EXIT /B %errorlevel%

    Stuff this in your local directory of choice. I use C:\scratchpad.

    Fire it off via cmd.exe and you'll get some stuff on screen. No problem.

    My problem stems from trying to capture this stuff. Really, I want the 22 that's being returned by the EXIT /B %errorlevel%. I'll take whatever I can get. My google-fu is fail, but it's gotten me somewhat close.

    Public Sub Main()

    Dim StInfo As New ProcessStartInfo("C:\scratchpad\TestBatch2.bat")

    Dim proc As New Process

    Dim str As String

    StInfo.UseShellExecute = False

    StInfo.RedirectStandardOutput = True

    proc.StartInfo = StInfo

    proc.Start()

    Using oStreamReader As System.IO.StreamReader = proc.StandardOutput

    str = oStreamReader.ReadToEnd()

    End Using

    MsgBox(str)

    Dts.TaskResult = ScriptResults.Success

    End Sub

    The Msgbox comes up BLANK. I can't figure out how to trap the blinking results.

    HEEEEEELLLLP!... please?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • stevefromOZ

    SSC-Forever

    Points: 43646

    Slight mod required (for me anyway) to the testbatch2 file --> I had to specify the path to the inner batch file, I guess my exeucting location from within SSIS isn't "D:\temp"

    CALL D:\temp\Testbatch.bat rather than CALL Testbatch.bat

    Then you should need to add only one line to your code -->

    Using oStreamReader As System.IO.StreamReader = proc.StandardOutput

    str = oStreamReader.ReadToEnd()

    proc.WaitForExit()

    End Using

    HTH, enjoy!

    Steve.

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Obliged, Steve, thanks.

    Now I've got a somewhat different issue, but I can see the echoes (and grab the .exitcode!).

    However, when I run this .bat via cmd.exe, it returns what I expect... 22.

    When I run it from SSIS, it returns a 1 as the errorlevel, all the way through.

    So, that's rather confusing to me. Anyone got any ideas while I troubleshoot this?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • stevefromOZ

    SSC-Forever

    Points: 43646

    Did you change your path reference in Testbatch2 (for testbatch) to be the full path?

    Steve.

  • stevefromOZ

    SSC-Forever

    Points: 43646

    If you don't want to hard code the path into the outer batch file, set the WorkingDirectory property on the ProcessStartInfo object

    StInfo.UseShellExecute = False

    StInfo.RedirectStandardOutput = True

    Stinfo.WorkingDirectory = "D:\temp"

    Steve.

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Steve,

    Thank you, that's got me straightened out. Much appreciated.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply