Execute a batch file on remote server with SSIS or SQL Agent

Experts,

We have SSIS/SSMS intalled on ServerA. We have a .bat file and an application and  on ServerB. The application processes an input text file and produces an output file.
I can be flexible on how to do this, but we've been trying to get this to work with SSIS. We use SSIS for processing prior to producing this file and then we have to stop, manually run the application, then start with post processing in SSIS.

Once the file is exported to a folder, the next step in our package (hopefully) is to call a .bat file that runs a cmd line script that has the application process the file (in silent mode...not sure if that is unique to this app??). We had this working from the DataStage ETL tool, but DataStage and the bat file were on the same server as the application that does the processing. We aren't as fortunate now.

I've got many hours invested in this and I just can't get this to work. I've tried Execute process task, Powershell scripts. I don't know if it's syntax problems or simply logic problems. I'm stumped.

As examples,
the name of the .bat file is: EXECUTE_GROUPER.bat which is located on E:\Batch_Files of ServerB

In the EXECUTE_GROUPER.bat file, just the one command to run the application: (Application is on C: on ServerB)
@echo off
"C:\Program Files\Medical\Medical Grouper 2014\MedicalGrouper2014.exe" -silent

Any assistance is GREATLY appreciated.

..btw 2012 versions of SQL Server

Thank you.
LVL 1
jvoconnellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
Create a new step and set its type to "Operating System (CmdExec)".  In the "Command Field" enter the batch file you want to run as per below:

cmd.exe /c "x:\path\batchfile.cmd"
0
jvoconnellAuthor Commented:
Thank you for the response.
Is there a way to do this using UNC? I am getting a hard time about a mapped drive since muliple teams access this server.
0
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
You can use a UNC just as long as the service account under which SQL agent is running has rights to the share.
0
jvoconnellAuthor Commented:
Hello,

I can't confirm either way if the suggested solution would have solved my problem. I couldn't get past security errors. We have some strict security policies and would not be able to have the Security Group work through this in the allotted time (if at all).

I was able to finally get this to work by changing the values I was using in the Execute Process task in SSIS.

In the Execute Process task, I used the following value in the 'Executable' property:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe


In the 'Arguments' property, I used the following value:

Start-Process -FilePath '\\ServerName\C$\Program Files\Medical\Medical Grouper 2014\MedicalGrouper2014.exe' -ArgumentList "/SilentMode" -Wait

Once again, I don appreciate your time in trying to assist me with this issue.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jvoconnellAuthor Commented:
I could not get past Security error messages with other proposed solutions. They may have worked if I had higher security level, but I can't confirm if they would have worked or not.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.