Connecting Tech Pros Worldwide Forums | Help | Site Map

Calling external apps from SQL Agent

Newbie
 
Join Date: Aug 2008
Posts: 2
#1: Aug 16 '08
Hi everyone, I have a recurring problem with integrating DTS packages that call up non-SQL Server applications. For example, I want to kick off a script in Filemaker that will export data for use in SQL Server. My DTS includes a step to launch and run that application, then return to SQL Server and take care of the resulting data.

My packages work fine if I run the DTS package directly from the Enterprise Manager logged in as myself, but fail when the SQL Agent calls the same package. The error at the SQL Agent level is pretty meaningless (a timeout), but I am pretty sure it has to do with the permissions. Is the only solution to change the ID the SQL Agent Service runs under to mine? Or is there another way to make the agent run an external app with appropriate permissions, etc.?

Thanks in advance.

Newbie
 
Join Date: Aug 2008
Posts: 2
#2: Aug 16 '08

re: Calling external apps from SQL Agent


I should add that running it from a batch file containing the DTSRUN command has the same problem: it works fine if I execute the batch directly, but fails if the SQL Agent tries to run it. It starts but then reaches this error:

Expand|Select|Wrap|Line Numbers
  1. Executed as user: MYDOMAIN\SQL_User. ...accountname /Ppassword /NMyExternalDTSPackage DTSRun:  Loading...   DTSRun:  Executing...   DTSRun OnStart:  DTSStep_DTSCreateProcessTask_1   DTSRun OnError:  DTSStep_DTSCreateProcessTask_1, Error = -2147220330 (80040496)      Error string:  CreateProcessTask 'DTSTask_DTSCreateProcessTask_1':  Process returned code -1, which does not match the specified SuccessReturnCode of 0.      Error source:  Microsoft Data Transformation Services (DTS) Package      Help file:  sqldts80.hlp      Help context:  4900      Error Detail Records:      Error:  -2147220330 (80040496); Provider Error:  0 (0)      Error string:  CreateProcessTask 'DTSTask_DTSCreateProcessTask_1':  Process returned code -1, which does not match the specified SuccessReturnCode of 0.      Error source:  Microsoft Data Transformation Services (DTS) Package      Help file:  sqldts80.hlp      Help context:  4900      DTSRun OnFinish:  DTSStep_DTSCreateProcessTask_1   DTSRun: ...  Process Exit Code 1.  The step failed.
  2.  
Ideas for a different way to structure this? It's something I've run into several times for different tasks.
Reply