You are viewing limited content. For full access, please sign in.

Question

Question

Workflow Script to run command or batch file

asked on January 5, 2016

I require a simple script for workflow that will run a command (.exe or .bat) file in Windows.

This must have been done before!

 

1 0

Answer

SELECTED ANSWER
replied on January 7, 2016

Peter - Interesting question!  I recalled seeing some sample code provided with the Workflow SDK that allowed you to call an external program so I modified that example code for a Workflow Script activity.  It seems to work as anticipated.

The concept is that you set the appropriate tokens in an 'Assign Token Values' activity that are then retrieved by the 'Script Activity' for execution.  The tokens allow you to set the external program to call, the command line parameters (if any), and whether or not to wait for program termination before continuing the workflow.

Here is a screen shot of the bare bones workflow;

Here is the Script activity code;

            Dim processID As String = ""
            Dim stdOut As String = ""
            Dim exitCode As String = ""

            'Create a process (which will not start running until later) which will eventually start the program we wish to call.
            Using process As System.Diagnostics.Process = New System.Diagnostics.Process

                Try

                    'Retrieve the token values...
                    Dim cmdLineParameters As String = Me.GetTokenValue("CmdLineParameters")
                    Dim externalProgram As String = Me.GetTokenValue("ExternalProgram")
                    Dim waitForExit As Boolean = Me.GetTokenValue("WaitForExit")

                    ' Empty externalProgram token is ignored...
                    If (Not String.IsNullOrWhiteSpace(externalProgram)) Then

                        'These settings let us specify the file name and location, and specify to the process that we wish to handle stdout and stderr ourselves. UseShellExecute = false is
                        'a requirement for this to work.
                        process.StartInfo.UseShellExecute = False
                        process.StartInfo.RedirectStandardOutput = True
                        process.StartInfo.FileName = externalProgram

                        'If there are command line arguments then set them...
                        If (Not String.IsNullOrWhiteSpace(cmdLineParameters)) Then
                            ' The arguments are specified in the input property box
                            process.StartInfo.Arguments = cmdLineParameters
                        End If

                        'Starts the process with all the settings we chose via Process.StartInfo...
                        process.Start()

                        'Set the processId variable for return as a token...
                        processID = process.Id

                        'If True then wait for the process to terminate before continuing the workflow...
                        If (waitForExit) Then
                            'As recommended in http://msdn.microsoft.com/en-us/library/system.diagnostics.process.standardoutput%28v=vs.71%29.aspx, stdout should be read before
                            'WaitForExit. Here the stdout is read and stored, and then we wait for the process to complete, if applicable.
                            Dim output As String = process.StandardOutput.ReadToEnd
                            process.WaitForExit()

                            'Set the return values to return as tokens...
                            stdOut = output
                            exitCode = process.ExitCode

                        End If

                    End If

                Finally
                    'Once we are done, make sure to free the process' handle if the process exists.
                    If (Not process Is Nothing) Then
                        process.Dispose()
                    End If
                End Try

            End Using

            'Set the token values to be evaluated later in the workflow...
            Me.SetTokenValue("ProcessID", processID)
            Me.SetTokenValue("StdOut", stdOut)
            Me.SetTokenValue("ExitCode", exitCode)

 

In this example I called a bat file to rename another file in the root of the C: drive.

4 0

Replies

replied on January 7, 2016

Thanks Cliff.  I have installed your script and tokens exactly as you have described.  The script does not return any errors when built and the workflow publishes OK.  However when I run the workflow the command does not run.  The workflow completes OK.  There are no errors.

I have added a Pause to the C:\renamefile.bat but there is no indication that it has been run.

If I use SDKScript instead of Script I can return the ProcessID as a token.

I basically don't know why the command won't run, but I am also only a beginner when it comes to script.

Could you please have a quick look at the following and let me know if it matches what worked for you?

This is my complete code using the Workflow Script activity:

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text

Namespace WorkflowActivity.Scripting.Script
    '''<summary>
    '''Provides one or more methods that can be run when the workflow scripting activity is performed.
    '''</summary>
    Public Class Script1
        Inherits ScriptClass90
        '''<summary>
        '''This method is run when the activity is performed.
        '''</summary>
        Protected Overrides Sub Execute()
            'Write your code here.
            Dim processID As String = ""
            Dim stdOut As String = ""
            Dim exitCode As String = ""

            'Create a process (which will not start running until later) which will eventually start the program we wish to call.
            Using process As System.Diagnostics.Process = New System.Diagnostics.Process

                Try

                    'Retrieve the token values...
                    Dim cmdLineParameters As String = Me.GetTokenValue("CmdLineParameters")
                    Dim externalProgram As String = Me.GetTokenValue("ExternalProgram")
                    Dim waitForExit As Boolean = Me.GetTokenValue("WaitForExit")

                    ' Empty externalProgram token is ignored...
                    If (Not String.IsNullOrWhiteSpace(externalProgram)) Then

                        'These settings let us specify the file name and location, and specify to the process that we wish to handle stdout and stderr ourselves. UseShellExecute = false is
                        'a requirement for this to work.
                        process.StartInfo.UseShellExecute = False
                        process.StartInfo.RedirectStandardOutput = True
                        process.StartInfo.FileName = externalProgram

                        'If there are command line arguments then set them...
                        If (Not String.IsNullOrWhiteSpace(cmdLineParameters)) Then
                            ' The arguments are specified in the input property box
                            process.StartInfo.Arguments = cmdLineParameters
                        End If

                        'Starts the process with all the settings we chose via Process.StartInfo...
                        process.Start()

                        'Set the processId variable for return as a token...
                        processID = process.Id

                        'If True then wait for the process to terminate before continuing the workflow...
                        If (waitForExit) Then
                            'As recommended in http://msdn.microsoft.com/en-us/library/system.diagnostics.process.standardoutput%28v=vs.71%29.aspx, stdout should be read before
                            'WaitForExit. Here the stdout is read and stored, and then we wait for the process to complete, if applicable.
                            Dim output As String = process.StandardOutput.ReadToEnd
                            process.WaitForExit()

                            'Set the return values to return as tokens...
                            stdOut = output
                            exitCode = process.ExitCode

                        End If

                    End If

                Finally
                    'Once we are done, make sure to free the process' handle if the process exists.
                    If (Not process Is Nothing) Then
                        process.Dispose()
                    End If
                End Try

            End Using

            'Set the token values to be evaluated later in the workflow...
            Me.SetTokenValue("ProcessID", processID)
            Me.SetTokenValue("StdOut", stdOut)
            Me.SetTokenValue("ExitCode", exitCode)



        End Sub
    End Class
End Namespace

 My Tokens are the same as yours:

My batch file C:\renamefile.bat works when run from Windows:

rename MyFile.csv NewName.csv
Pause

Have I installed the script correctly?

Thanks in advance for you help. Any feedback would be appreciated.

2 0
replied on July 10, 2018 Show version history

in your bat file, you have references to 3 different files and do not supply a path to any of them.  The SQLCMD is probably fine since it should be in the path variable (but I would still provide full path), but the other 2 most definitely need full paths supplied.

change to something like

Also, any files referenced inside of the nrc.sql script also need to have their full path defined.

1 0
replied on January 7, 2016

Update:

All OK.  This script works.

The error was in the batch file.

It is not running C:\renamfile.bat in C:\.  It is looking for the files in C:\Windows\System32\.

 

Thanks again for your help Cliff.

0 0
replied on January 7, 2016

Good, glad you got it working!  If you are run Windows Task Manager and sort the running processes by PID you can then locate the ProcessID and see it appear and then disappear in the list as the externally called program runs....

1 0
replied on July 8, 2018

Hi Cliff

I hope all is well.

I have the same problem here. I am using your script to run a .exe and it works perfectly.

Now I have a .bat file that runs a sql query to export a sql table to csv.

The script does not execute the .bat file even though the Workflow returns no errors.

What could be wrong?

0 0
replied on July 9, 2018 Show version history

What is the value of your "ExternalProgram" token?  Is it the full path to the bat file?

Are you providing the full path to any/all files that your .bat file and sql script accesses?

0 0
replied on July 9, 2018

I have tried every way around. Script C#, script visual basic.

SDK script tool C#, SDK script tool visual basic.

Note that this method works perfectly fine when attempting to use to it on a .exe.

Actually, I am using a copy and pasted version of this code in a already scheduled Workflow to execute a third party program.

Thoughts?

0 0
replied on July 11, 2018 Show version history

RE:

I am not referencing anything specific in my SQL Script:
Hi Bert

I hope you are doing good today!

I know this must be a pain for you already but I feel like we are making some progress.

First of all thank you for the help.

It looks like my Workflow is finally reacting to what I'm telling it.

I changed the paths of everything as you instructed:

Workflow still looks like:

When running the .bat by double clicking it, I still get my expect result:

I am a flat out of ideas over here.

I am about 105% sure everything is pointing in the correct direction, the bat file is where it needs to be, etc.

Help please!

 

0 0
replied on July 11, 2018

Perhaps a permissions issue with Workflow accessing the folder where you have the batch file?  I would move the bat file to a more generic folder, perhaps creating a test folder off of the root of C: and making sure the folder allows access to all users.

2 0
replied on July 11, 2018 Show version history

Did you verify that the "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.exe" is present on your system?  It may have a different path based on the version(s) SQL you have installed.  If you do not know where to find sqlcmd on your workflow server, open Regedit and search/find "SQLCMD.EXE".  Then check that the file is where the registry is reporting it.

As Cliff states, the user folders are a bit restrictive, so create a folder on the root of C (or on a data drive) and make sure that the user that Workflow "Logs On As" has permissions to it.  Place your .bat, .sql, and .csv files in that folder.

Finally, make sure that the user that Workflow "Logs On As" has permissions to SQL because that is the user that will run the script.

2 0
replied on July 14, 2018

This solved it. I moved everything to C:\mypath\mypath2\ and it seems that WF is executing my .bat! Thank you Cliff.

And yes, Bert, thank you kind sir. My path is ironically the exact same as the one you had specified to me.

My Workflow user logs on as "Local System" same as yours. 

Is it expected behavior to not execute the bat simply because of the dir the bat is in?

Thanks guys.

0 0
replied on July 14, 2018

Good!  Glad we could help!  As Bert shares the User folders typically have restricted rights and it is not uncommon to have restricted execute rights in other folders as well.  That is why I usually create a new folder in the root of the C: (or data drive as Bert shares) and grant it specific execute rights to make sure that permissions aren't an issue.

0 0
replied on July 15, 2018

I see. I don't get why it wouldn't have access deeper in to the hierarchy but at the root of C: it at least executes it you know! Even though my Log on As and everything seems right corresponding to what you guys have told me.

My victory was short lived guys

It executes and runs the bat from Workflow now, but now it specifies that Workflow doesn't have any permission to connect to SQL.

Which is for sure not true since I am accumulating data from one table to another whilst executing this .bat file.

I'll figure out my permissions!

Thank you guys for the help.

0 0
replied on July 16, 2018

It's not that it won't go past the root of the C:\ drive, but as Cliff and Bert stated, it's permissions for the service user on the folder your script was residing in.

"Workgroup\Laserfiche-ISN$" is the Local System account for this machine. If your SQL is on a different machine, this account won't be able to connect because it's restricted to just the local machine. If your SQL is on the same machine, then this use needs to be given rights to SQL.

0 0
replied on July 16, 2018

I ended up using a custom query for this as I'm not winning the whole user rights battle with my server.

I have a feeling the initial installation was done improperly as I'm getting all sorts of errors and instability lately.

But, nevertheless, my problem is solved.

Thank you guys for all this helpful and very valid input.

0 0
replied on March 21, 2019

Hello,

I found this very helpful in allowing starting a batch file.  It would even be better if it started a Powershell file.  Do you know what would need to be modified in order to start a Powershell file instead?

0 0
replied on November 21, 2023

Hey all-

Commenting on a 7 year old thread. I am trying to do something similar (AWS CLI), and it works fine when running the script in debug, but the workflow never completes the desired action. No errors or warnings, but no file in my local drive that's supposed to be pulled from AWS.

Curious if there are any security settings that might be getting in the way. I'm trying with Workflow 10.4 and 11 with the same (lack of) results.

Tagging in @████████

0 0
replied on November 21, 2023 Show version history

I'd start here: Workflow Admin Console > Security Node.

By default, Workflows aren't allowed to browse the local file system, which I believe causes operations to write to a directory Workflow can't "see" to fail. You have to explicitly enable that.

For testing purposes, you can also wrap your whole Workflow Script in a try/catch block (in the script code, not with the Workflow activity) and in the catch section write the exception to a Workflow token so you can see where it's failing.

As you likely know, scripts run through Workflow Designer run under your own user identity rather than the Workflow Server service.

0 0
replied on November 21, 2023

Thanks, Sam. Turns out I missed the first rule of troubleshooting...when in doubt, reboot. Something about the CLI extension (or whatever it's called) wasn't working right with our call to the command prompt until rebooting. Though odd that it worked fine when debugging (and yes, was using the same account as WF itself).

On the plus side, I now (sort of) know how to pull files from AWS CLI via Workflow.

1 0
replied on November 21, 2023

Glad to hear it's working =)

I personally usually use the AWS SDK for .NET in Workflow Scripts directly. Haven't tried invoking their CLI from there.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.