Issue with Job Executing, but never failing/working

• JLG-489362

  SSC Veteran

  Points: 229

  More actions

  November 21, 2007 at 8:03 am

  #119572

  Folks,

  I have an SSIS package that I want to run that basically

  runs a .cmd file to copy files from a different server, process them, then update tables with data (pretty mundane stuff)

  This job works FINE when I double click the dts package, or if I run the dtexec command from the command line.

  I set up a SQL Agent Job to run this every night, but I cannot get it to run.

  I have it running from FILE, and when it executes, it hits the step for running the SSIS package, and starts, but just sits there -- it doesn't actually do anything.

  I have the package logging to a file where the rest of the files are, and it DOES get that the it STARTS, but that's it:

  PackageStart,SERVERNAME,IDNAME,SSISPACKAGENAME,{0797F1F4-909A-44ED-8481-D277C6791406},{8A241613-A388-42DE-BE1F-B35CA577BFF5},11/21/2007 9:45:53 AM,11/21/2007 9:45:53 AM,0,0x,Beginning of package execution.

  Nothing else -- no indication in the log file, the package log file, the event viewer, etc.. It just sits at EXECUTING.

  I have restarted the Agent, I have created a proxy that has cmdexec and ssis and had the step run as that (either this proxy or the sql agent id both exhibit this behavior).

  Any ideas where to look/how to debug? This is the first time I'm using SSIS and Sql Agent.

  Any help GREATLY appreciated.

• brendt hess

  Hall of Fame

  Points: 3256

  More actions

  November 29, 2007 at 2:16 pm

  #755688

  Permissions, permission, permissions.

  When you can run a DTS package, and another process can not, it is, almost inevitably, that the security context of the other process does not have permissions to do *something*. In this case, with the package in a file, it may be that the process does not have permissions to either the directory the file is in, or to the file itself.

• JLG-489362

  SSC Veteran

  Points: 229

  More actions

  November 29, 2007 at 2:59 pm

  #755711

  Thanks -- I DID eventually get this to work.

  Unfortunately it was by creating a NEW SSIS project,

  and adding EACH STEP one at a time, and running the job at each phase until it worked.

  I thought it might be permissions, which is why I created the login/credential/proxy stuff, but still wouldn't work.

  The problem I have is that there is no good way to debug this.

  The SSIS logging didn't show anything (and I turned EVERYTHING on) -- only that the package started.

  The SQL Server Agent job said nothing in the history or when it was running, the event log had nothing -- no real way to look at anything to try and determine what was going on.

  The job never failed, and never stopped executing! I would have thought that validation or SOMETHING would have shown up if permissions were off -- not just that the job runs forever!