Problems running a bat file within a SSIS package
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 thatbasically copiesthe excel sheet created from the first step toanother 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.
July 13th, 2007 10:44pm
just wanted to make sure this got posted....:)
July 13th, 2007 10:50pm
The "executable" should be "c:\windows\system32\cmd.exe" and the argument should be "/C full-path-to-batch-file"
July 13th, 2007 11:04pm
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. Thanks.
July 14th, 2007 5:04am
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 havea batch file called c:\foo.bat which contains the following: echo %1 %2 pause 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 defabc def C:\Windows\System32>pausePress any key to continue . . . printed to the console.
July 28th, 2007 3:05am
Just add .bat file as exceutable. and add the arguments if the .bat file will accept any arguments. Cheers!!!!
June 19th, 2009 10:52pm
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. VideoFor practical examples goto below links: http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.htmlhttp://sqlserversolutions.blogspot.com/2009/04/copyrename-file-using-file-system-task.htmlCreate 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: http://denster.spaces.live.com/blog/cns!125D53A08EC75357!449.entry http://www.developmentnow.com/g/103_2005_12_0_0_676200/SSIS-Execute-Process-Task-Variables.htm 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.xlshttp://www.simple-talk.com/sql/sql-server-2005/executing-ssis-packages-/Kapil Khalas
June 20th, 2009 8:36pm
Hi did you fix this issue? Im having the same issue.
May 22nd, 2010 12:48am
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
August 31st, 2010 9:38pm
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
August 31st, 2010 9:41pm
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.
May 3rd, 2011 1:13pm