Problems running a bat file within a SSIS package

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.

Answer 1

just wanted to make sure this got posted....:)

Answer 2

The "executable" should be "c:\windows\system32\cmd.exe" and the argument should be "/C full-path-to-batch-file"

Answer 3

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.

Answer 4

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

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 def
abc def

C:\Windows\System32>pause
Press any key to continue . . .

 

printed to the console.

 

Answer 5

Just add .bat file as exceutable. and add the arguments if the .bat file will accept any arguments.

Cheers!!!!

Answer 6

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:
http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html
http://sqlserversolutions.blogspot.com/2009/04/copyrename-file-using-file-system-task.html

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:
    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.xls

http://www.simple-talk.com/sql/sql-server-2005/executing-ssis-packages-/

---

Kapil Khalas

Answer 7

Hi did you fix this issue? Im having the same issue.

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

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.

 

Anyone?

 

 

---

Hal B

Answer 12

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

Answer 13

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

Answer 14

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

Answer 15

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

HTH!
DebS