Running batch file in ssis SQL Agent job

Question

Running batch file in ssis SQL Agent job

upendar.dilytics

Mr or Mrs. 500

Points: 529
More actions
May 2, 2017 at 4:10 am

#336708

I have an SSIS Package, which simply runs a batch file, the code for the batch file is
@if (@CodeSection == @Batch) @then @echo off
start "" runas /user:TEX\TEX_B D:\purgecache12c1.bat
CScript //nologo //E:JScript "%~F0"
goto :EOF
@end
WScript.CreateObject("WScript.Shell").SendKeys("BI$tup{ENTER}");
If I run the SSIS package manually it runs fine and it showcases the output, however, if I run the package
through a SQL Agent Job it runs successfully but it does not showcase the output

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

When you run the package manually, it runs in the context of whatever user you're logged in as. When you run it from a job, it runs in the context of the SQL Server Agent service account (unless you've configured a proxy). You need to make sure that the service account has the necessary access to run the batch file and to access all resources used by the batch file.

John

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

If all the SSIS package is doing is calling the batch file, then why couldn't you just use a job step of type "Operating system (CmdExec)"? You can setup credentials and a proxy to explicitly determine who the batch file will run as in the OS.
http://sqlmag.com/blog/sql-server-dba-tip-credentials-and-proxies

upendar.dilytics Mr or Mrs. 500 Points: 529 More actions · Answer 3

Chris Harshman - Tuesday, May 2, 2017 11:25 AM
If all the SSIS package is doing is calling the batch file, then why couldn't you just use a job step of type "Operating system (CmdExec)"? You can setup credentials and a proxy to explicitly determine who the batch file will run as in the OS.
http://sqlmag.com/blog/sql-server-dba-tip-credentials-and-proxies

I have tried in this way also using step type as "Operating system (CmdExec)" job is running successfully but the cache is not cleared.

upendar.dilytics Mr or Mrs. 500 Points: 529 More actions · Answer 4

I'm trying to get a batch file to automatically enter the password into the "runas" program, this code is working fine but it is not supported by SQL server agent. Here's my batch file so far:

@if (@CodeSection == @Batch) @then
@echo off
start "" runas /user:testuser c:/path/to/my/program.exe
CScript //nologo //E:JScript "%~F0"
goto :EOF
@end
WScript.CreateObject("WScript.Shell").SendKeys("password{ENTER}");

Please suggest any method to enter a password automatically and it should support the SQL server agent job.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

instead of trying to make the batch file change users with RUNAS, you could use credentials and proxies as mentioned above to more easily store the OS login and password within SQL Server and execute the batch file as that user once you setup a proxy for the credentials and the Operating System (cmdexec) job step.

upendar.dilytics Mr or Mrs. 500 Points: 529 More actions · Answer 6

Chris Harshman - Monday, May 15, 2017 7:12 AM
instead of trying to make the batch file change users with RUNAS, you could use credentials and proxies as mentioned above to more easily store the OS login and password within SQL Server and execute the batch file as that user once you setup a proxy for the credentials and the Operating System (cmdexec) job step.

I have already tried this by creating credentials and proxy for the job. But when we run the job it showing success status but it is not clearing the cache.