Execute Bat file from SSIS

I need to execute a bat file inside of a scheudle SSIS package.  The bat file calls a PSExec command to start a SAS job on a dedicated SAS server.  This bat file is called in a Process Task with the executeable defined by a variable built by expression.  I confirmed the variable is loaded correctly.

Running from BIDS it works as designed; from SQL as a schediled SSIS job, it hangs on the call to the Bat file.

With PSexec I am passing a login and password for the SAS machine so permissions should not be a problem.  PSExec is installed on both machines.

Suggestions?
OHISAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
What about the SQL Agent Service startup account??
Does it have sufficient rights to execute the BAT which executes other commands??


" I am passing a login and password for the SAS machine so permissions should not be a problem.  "
I understand that is inside the BAT but before you can exec that you need to ensure that the SQL Server agent can reach and run the BAT file
0
 
lcohanDatabase AnalystCommented:
All you need is access/rights/permissions and you should be able to do that by using xp_cmdshell:
http://msdn.microsoft.com/en-us/library/ms175046.aspx


if not enabled you can use the code below to enable it:

exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
exec sp_configure
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
0
 
lcohanDatabase AnalystCommented:
Does the NT user that starts SQL Server Agent service has sufficient rights to the folder where bat file is located and down the tree from there? When you run it in BIDS it runs under the current NT login and if that has sufficient rights to do it all...you should get the idea.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
OHISAuthor Commented:
I checked the xp_cmdshell setting...it was wrong.  I changed it but the problem persists.
0
 
OHISAuthor Commented:
I doesn't appear that the SQL Agent has domain privilidges across to the other machine.  We are investigating this now.
0
 
lcohanDatabase AnalystCommented:
Please do NOT grant domain priviledges to the SQL Agent account!! That could be dangerous and please see below Microsoft recommendations. Besides that you are better if grated only speciffic limmited folder and executable rights just sufficient to perform the task(s) you want.

http://msdn.microsoft.com/en-us/library/ms190926(v=SQL.90).aspx

and please download "SQL Server 2005 Security Best Practices" document from Microsoft.
0
 
OHISAuthor Commented:
After thinking about this over the weekend.  i came to the same conclusion about the SQL Agent account.  Folder rights are set correctly in all other areas, or so I am told by admin.

Unfortuneately I am battling offshore DBAs.  Communication is challenging.  They are doing things without my knowledge and have now broken the entire process.

I will advise further once I have restored the process.
0
 
OHISAuthor Commented:
I have reseolved the DBA crisis from yesterday.  The ProtectionLevel property was wrong..corrected.
Now running the package it fails with an Exit Code 5 on the Bat call.  Denied oeration I believe.

I changed the location of the BAT file to the same machine as the SQL job...no change in outcome.  It apepars that the SQLAgent cannot call a Command prompt type executeable.

I am told all permissions are set correctly...any ideas?
0
 
lcohanDatabase AnalystCommented:
Can you try to execute the batch file from a SQL query on that server?
I suggest you create a test bat file with only dir *.* in it something like

dir c:\*.* >c:\dir_list.txt

but put it in the same location then run it using cmdshell like:

exec xp_cmdshell 'c:\test.bat'

Obviously please change C:\ with your actual full path including drive leter in sample code above and if you can run this then is most likely something inside the bat or what the bat is trying to execute that fails

0
 
OHISAuthor Commented:

I was on the same track as you...

I have tried the following:
EXEC xp_cmdshell 'dir \\{remote SAS server}\datafile\sas\eds_ecore\*.bat'; --location of current Bat files
GO

this resulted in an Access Denied.

I moved the Bat files to the SQL machine:
EXEC xp_cmdshell 'dir D:\oxmoorhouse\batch';
GO

I got a correct listing of the bat files.  I tried the SSIS job pointing to these files...same error message as I received when pointint to the remote Bat files..

Message
Executed as user: TIME-INC-CORP\spc-sqlqa. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  1:10:32 PM  Error: 2011-10-12 13:12:32.06     Code: 0xC0029151     Source: Execute Datamart Load Execute Process Task     Description: In Executing "D:\oxmoorhouse\batch\hal.bat" "" at "", The process exit code was "5" while the expected was "0".  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:10:32 PM  Finished: 1:12:32 PM  Elapsed:  119.844 seconds.  The package execution failed.  The step failed.

The offshore DBAs have taken over the test SQL machine to review all logins, permissions and security settings.

0
 
lcohanDatabase AnalystCommented:
Exactly - this means what's inside the BAT file is causing the error(s).

Besides that as far as I'm aware you can not execute any shell command against a remote server and all (bat,exe,etc) files should be local to the SQL server (not even on a share)

You can run SSIS packages against different servers as SSIS will have inside a connection (manager) for each server and you can set diferent steps of the package to run against different connections.
0
 
OHISAuthor Commented:
It appears that we have resolved the problem...confusing at it was.
There were 2 real problems ...startup account permissions were improperly granted and very poor deployment procedures by the DBAs.

The PSExec call within the BAT file was correct.  It was just SQL Agent permissions.

Thanks.
0
 
elmbaekCommented:
What did you change ? I'm having the exact same problem and can't find the solution
0
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.

All Courses

From novice to tech pro — start learning today.