none
Using CmdExec to execute dtexec in sqlagent

    Question

  • I'm a bit confused.  On the command line of the job step property I entered dtexec /SQL... and got an error saying file not found, I assumed dtexec itself couldnt be found.  So I tried /SQL ....  by itself  and got something that looked more like a security error.  If I make the step property type "ssis" job appears to run fine, I receive my pkg's on success (rather than on failure) email but I know everything isnt fine because even if no data is ETL'd, first executable is supposed to (and always has in client) insert a row into an audit table and it doesnt.  If I set job step "type" to t-sql and simply db email myself with t-sql command, everything is fine.

     

    The first question is "Wouldnt dtexec need to be specified, how else could sqlagent know what I'm trying to run?"  If answer is yes, what's wrong with my syntax or environment?

     

    Saturday, April 12, 2008 5:55 PM

Answers

  •  

    I don't think that its because of SP2.

     

    I had done this some time back with Standard Edition with SP1 (No SP2 at that time)

     

    I thought that you have missed some thing on the relevant path of the dtexec.exe.

     

    Create a sql script which uses XP_CMDSHELL to execute the dtexec.exe.

    Sunday, April 13, 2008 3:27 PM
  •  db042188 wrote:

    I'm curious, do databases stay intact when a service pack is applied?   Is the server basically unavailable for the minutes during which the service pack is being applied?  Do packages deployed to MSDB stick around during a service pack upgrade?

     

     

    Data is safe during service pack. Basically you have to stop the server and restart it after SP2 finished.  Normally a few minutes.

    Sunday, April 13, 2008 5:59 PM

All replies

  • Can you describe you hardware?

     

    Can you run this script and post result:

    Code Snippet

     

    USE master;

    GO

     

     

    SELECT

    'Microsoft SQL Server ' +

     

    convert(varchar, SERVERPROPERTY('ProductVersion') ) + ' ~~ ' +

     

    convert(varchar, SERVERPROPERTY('ProductLevel') ) + ' ~~ ' +

     

    convert(varchar, SERVERPROPERTY('Edition') ) + ' ~~ ' +

     

    convert(varchar, SERVERPROPERTY('EngineEdition') ) ;

    GO

     

    EXEC sp_dbcmptlevel AdventureWorks

    GO

     

     

     

     

    Saturday, April 12, 2008 6:42 PM
  • thanks sqlusa

     

    Microsoft SQL Server 9.00.1399.06 ~~ RTM ~~ Standard Edition ~~ 2

    The current compatibility level is 90.

     

    Saturday, April 12, 2008 6:52 PM
  • You have to install SP2 first.

     

    Test again after SP2 and see if still any issue.

     

    Saturday, April 12, 2008 8:02 PM
  •  

    I don't think that its because of SP2.

     

    I had done this some time back with Standard Edition with SP1 (No SP2 at that time)

     

    I thought that you have missed some thing on the relevant path of the dtexec.exe.

     

    Create a sql script which uses XP_CMDSHELL to execute the dtexec.exe.

    Sunday, April 13, 2008 3:27 PM
  • SP2 just to be on the safe side.

     

    You can schedule an SSIS package directly in SQL Server Agent.

     

    Why do you need DTEXEC?  If so, have you tested DTEXEC manually at command prompt?

     

    Sunday, April 13, 2008 3:36 PM
  • thanks SQLUSA and Manikandan S.

     

    I want dtexec for reasons suggested by Mathew Roche at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3124043&SiteID=1  .  It sounds like good advice after having seen the "lay of the land" when setting up sqlagent jobs and viewing their history for the 1st time. 

     

    I will first see if fully qualifying the path to dtexec makes any diff.  If it does I'll recommend to our tech that this path be added to the Windows "Path" on that server.

     

    I'm curious, do databases stay intact when a service pack is applied?   Is the server basically unavailable for the minutes during which the service pack is being applied?  Do packages deployed to MSDB stick around during a service pack upgrade?

     

    Sunday, April 13, 2008 4:48 PM
  •  db042188 wrote:

    I'm curious, do databases stay intact when a service pack is applied?   Is the server basically unavailable for the minutes during which the service pack is being applied?  Do packages deployed to MSDB stick around during a service pack upgrade?

     

     

    Data is safe during service pack. Basically you have to stop the server and restart it after SP2 finished.  Normally a few minutes.

    Sunday, April 13, 2008 5:59 PM