[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Execute Bat file from SSIS

Posted on 2011-10-06
13
Medium Priority
?
3,573 Views
Last Modified: 2013-06-21
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?
0
Comment
Question by:OHIS
  • 6
  • 6
13 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 36926520
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
 
LVL 40

Expert Comment

by:lcohan
ID: 36926578
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
 

Author Comment

by:OHIS
ID: 36927053
I checked the xp_cmdshell setting...it was wrong.  I changed it but the problem persists.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 36931758
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
 

Author Comment

by:OHIS
ID: 36933202
I doesn't appear that the SQL Agent has domain privilidges across to the other machine.  We are investigating this now.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36950272
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
 

Author Comment

by:OHIS
ID: 36950355
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
 

Author Comment

by:OHIS
ID: 36955806
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
 
LVL 40

Expert Comment

by:lcohan
ID: 36957488
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
 

Author Comment

by:OHIS
ID: 36958293

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
 
LVL 40

Expert Comment

by:lcohan
ID: 36958400
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
 

Author Closing Comment

by:OHIS
ID: 37001951
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
 

Expert Comment

by:elmbaek
ID: 39266107
What did you change ? I'm having the exact same problem and can't find the solution
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question