Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1034
  • Last Modified:

SQL 2005 SSIS Execute Process Task

I have a SQL 2005 SSIS package that fails on an Execute Process task.  The package runs successfully in BIDS, but fails when running it on the server.  I have a SQL job built to run the SSIS package which uses the SQL Agent account.

The .exe file which the Execute Process is supposed to run is located on the SQL server.  I can successfully run the .exe directly, or as I mentioned, in the package within BIDS.  I have BIDS running on a separate machine.  The package was imported as a File System, and I've tried both ProtectionLevel of sensitive by user and by password.  I've changed the SQL agent service to run with an account that has permissions to both the .exe file and SQL, and I've lowered the folder and file permissions where the .exe file is located to everyone.

I have my package config file with just the connection strings added - one connection for the .exe file, and another for my exchange server for a send mail task.

Here is the error from the SQL job logs:

Message
Executed as user: MY_SERVER\myusername. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  10:29:42 AM  Error: 2009-04-20 10:29:43.78     Code: 0xC0029151     Source: Execute RRDFulfillment Mapping Execute Process Task     Description: In Executing "E:\MD\SSIS\RRDFulfillment\Debug\Mapping.exe" "" at "", The process exit code was "1" while the expected was "0".  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  10:29:42 AM  Finished: 10:29:43 AM  Elapsed:  0.828 seconds.  The package execution failed.  The step failed.

Any suggestions are welcome.

Thanks.

0
LP_Tech
Asked:
LP_Tech
  • 4
  • 4
  • 3
  • +1
1 Solution
 
reb73Commented:
What does the exe actually do?
0
 
LP_TechAuthor Commented:
the .exe file was built using Mapforce by Altova.  It extracts data from an .xlsx file and updates a table in SQL basically.  The .exe file runs fine by runing it directly on the server, or even by executing the task in the package within BIDS.
0
 
Anthony PerkinsCommented:
If "E:\MD\SSIS\RRDFulfillment\Debug\Mapping.exe" is on a different server than make sure this is a UNC path and not a mapped drive.  Also, make sure the account for the SQL Server Agent service has access to all the resources required by MapForce.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
nmcdermaidCommented:
You've answered your own question - the job runs under the SQL Agent account.
Temporarily set the SQL Agent account to your account and see if that fixes it. If it does we can start troubleshooting down that avenue.
0
 
nmcdermaidCommented:
sorry posted a bit late.
0
 
LP_TechAuthor Commented:
The "E:\MD\SSIS\RRDFulfillment\Debug\Mapping.exe" path is located on the same server, and the SQL agent account has permissions to the resources that Mapforce requires to run.

Also, I did change the SQL agent service to run with my account, but i still receive the same error.  My account can successfully run the .exe file outside of the SSIS package.  I restarted the SQL agent service after changing the account as well.
0
 
Anthony PerkinsCommented:
Than if you are not using any mapped drives to other servers, then I am afraid I have no idea and your best bet is to talk to Altova.

I know it can be done, because we have the same setup at my shop.
0
 
nmcdermaidCommented:
Have you tried logging on to the Server as yourself and running the EXE there? (Its unclear in your original post whether you have done this)
0
 
LP_TechAuthor Commented:
Ok - I think I'm on the right path now.  I logged into the server with my account and tried to run the .exe file as nmcdermaid suggested.  Doing so generated the following error:

Mapping Application
ERROR: Request for the permission of type 'System.Security.Permissions.FileIOPer
mission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934
e089' failed.

Stack Trace:
   at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMa
rk& stackMark, Boolean isPermSet)
   at System.Security.CodeAccessPermission.Demand()
   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, I
nt32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions o
ptions, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access,
FileShare share)
   at System.IO.File.OpenRead(String path)
   at Altova.IO.FileInput..ctor(String f)
   at Mapping.MappingConsole.Main(String[] args)

So,  it appears I need to find exactly where permissions still need to be granted to allow the file to be run.  Also, I got a pop up just before I ran the file stating a security error - publisher: unknown publisher that I had to click OK to continue.  I'm not sure if the two are related, of if I'll have to address both of these items separately.
0
 
Anthony PerkinsCommented:
Which is exactly what I asked:
"make sure the account for the SQL Server Agent service has access to all the resources required by MapForce"
And you responded:
"the SQL agent account has permissions to the resources that Mapforce requires to run."

Which obviously it did not.

>>Also, I got a pop up just before I ran the file stating a security error - publisher: unknown publisher that I had to click OK to continue. <<
Correct.  That user does not have permissions to access those resources on that remote server.
0
 
nmcdermaidCommented:
The first error is as acperkins states - probably a security error. You can use Procmon to verify/find this, I guess its something in the .Net runtime. Whether its directly related to file permissions or something else I'm not sure. There might be private a key that user does not have access to or something (OK now I'm just making this up).
The second error - it may be related to the first one, or you might need to put some kind of exception somewhere in windows to say 'this EXE is good to run'
0
 
Anthony PerkinsCommented:
>>you might need to put some kind of exception somewhere in windows to say 'this EXE is good to run'<<
Correct.  You can set that in IE.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now