Problems running a bat file within a SSIS package RRS feed

  • Question

  • I have a ssis package which has 2 tasks, first it builds an excel sheet with data on the server from where I am running the ssis pkg and the second task (a sql process task), runs a bat. the bat file that basically copies the  excel sheet created from the first step to another server. Problem is that the ssis pkg runs succesful when i run it from BIDS, bust when I run is as ajon, it gets hung. It completes the first step, it is the sescond step that is get hung. I noticed that when i run from BIDs, it does come up with a window open file - security warning and says the publisher could not be verified, are you sure you want to run this software. why does it do that ??? the file I am asking to run is a .bat file. it should automatically use the cmd.exe app to run the bat file. Please help !!!


    Thanks in advance.

    Friday, July 13, 2007 7:44 PM


All replies

  • just wanted to make sure this got posted....:Smile)
    Friday, July 13, 2007 7:50 PM
  • The "executable" should be "c:\windows\system32\cmd.exe" and the argument should be "/C full-path-to-batch-file"
    Friday, July 13, 2007 8:04 PM
  • I tried your suggestion. In the executable, i've got c:\winnt\system32\cmd.exe

    and in the arguments i've got the bat file and the 2 parameters. when I execute the task, it comes up with the command prompt screen and just suts there. so it is not executing the batch file. where should the 2 parameters that the batch file needs go, the source file and the destination file. I had them earlier in the arguments and the bat file in the executable, where should i put the parameter for the bat file now. 



    Saturday, July 14, 2007 2:04 AM
  • Hi Jain,


    You should place the arguments to your batch file in the "Arguments" parameter of the Execute Process Task, right after the name of the batch file.  So, for example, I have a batch file called c:\foo.bat which contains the following:


    echo %1 %2



    To run it from my package, I create an execute process task, and in the editor, I set the following


    Executable            c:\windows\system32\cmd.exe

    Arguments            /c c:\foo.bat abc def


    When I run this package, my batch file is executed and passed "abc" and "def" as its arguments, such that a command prompt appears and I see


    C:\Windows\System32>echo abc def
    abc def

    Press any key to continue . . .


    printed to the console.


    Saturday, July 28, 2007 12:05 AM
  • Just add .bat file as exceutable. and add the arguments if the .bat file will accept any arguments.

    Friday, June 19, 2009 7:52 PM
  • hi,

    You can copy your excel file from one location to another with the use of tool - FileSystemTask.

    The File System task performs operations on files and directories in the file system. For example, by using the File System task, a package can create, move, or delete directories and files. You can also use the File System task to set attributes on files and directories. Goto here  for more details. Video

    For practical examples goto below links:

    Create batch file with multiple parameters and execute this batchfile using ExecuteProcessTask.
    You can create batch file which copy your excel file and that batch file you can create any multiple way -

    1. One way is use Robocopy. By using this tools you can copy single file, muliple files, whole folder any operation you can perform.
        Download Robocopy.exe and goto here for more details.

    2. Make batchfile with parameters execute this batchfile using execute processtask and pass arguments.
        create batch file - copy %1 %2
        and set two argumets in ExecuteProcessTask. See in following examples:

    You can also execute your package using batchfile with command line parameters.

    dtexec /FILE "C:\SSIS\SSIS Project\bin\Test.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI /SET "\Package.Variables[User::FileName].Properties[Value]";Test.xls

    Kapil Khalas
    • Marked as answer by Bob Bojanic Thursday, June 25, 2009 5:07 PM
    Saturday, June 20, 2009 5:36 PM
  • Hi did you fix this issue? Im having the same issue.

    Friday, May 21, 2010 9:48 PM
  • David,

    I like this answer. It certainly addresses the problem I'm having.

    Unfortunately though it introduces a new variable since the target system might have it's windows installation on something other than drive C:. So in my case this is another path variable I'd need to add to the dtsConfig file.



    Andy Abel
    Tuesday, August 31, 2010 6:38 PM
  • HI UmangPatel,Unfortunately this only works if you have one argument, because SSIS passes the argument(s) to cmd with double quotes, so if you have two arguments it passes the following:-

    "C:\MyProgram.bat" "arg1 arg2"

    ... which doesn't work



    Andy Abel
    Tuesday, August 31, 2010 6:41 PM
  • what is the problem if the CMD window doesn't appear when i run the package? I used the same echo example and the cmd window just flashes and the package reports success without doing what the batch file is meant to do? I'm using SQL Server 2005.
    Tuesday, May 3, 2011 5:10 PM
  • this thread has spanned 2 years...still not sure what the answer should is.


    I am calling a remote bat file that executes SAS by way of PSExec.  I just call the BAT in the Proces Task step.

    From BIDS it works, opens a dos cmd window, runs to completion.  From SSIS, scheduled or run manually, it hangs on the bat call.





    Hal B

    • Edited by hboyles Thursday, October 6, 2011 2:59 PM
    Thursday, October 6, 2011 2:46 PM
  • How do you run it manually? And how scheduled?

    I suggest you do not pop the DOS window, on a server there is no GUI, a script needs to run with windows style = hidden.

    Arthur My Blog
    By: TwitterButtons.com
    Thursday, October 6, 2011 3:12 PM
  • the Process Task just calls the bat file.

    The bat file contains the script to call PSExec and the SAS job.

    The dos windows just happens, it is not explicitly called.


    Hal B
    Thursday, October 6, 2011 3:34 PM
  • Please answer my questions above in full.

    It is not called, it just works BY DEFAULT with the window popping. You can & should inhibit it, it does not good.

    This thread shows how: http://mcaf.ee/r9vyf

    Arthur My Blog
    By: TwitterButtons.com
    Thursday, October 6, 2011 5:42 PM
  • Open Internet Explorer -> Internet options -> Security -> Internet -> Custom Level -> Launching applications and unsafe files: Set it to ENABLE.

    You can also enable it by changing the corresponding registry entry. In registry key, we can set the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Internet Settings\Zones\3\1806
    Value: 0

    You can read more about IE security settings and registry keys here:

    “Internet Explorer security zones registry entries for advanced users” - http://support.microsoft.com/kb/182569


    Monday, December 10, 2012 6:01 PM