Problems running SSIS package in SS Agent (file not found)

• Doug Andersen

  Old Hand

  Points: 325

  More actions

  November 12, 2007 at 8:09 pm

  #119465

  I've had a frustrating day trying to debug the typical problem whereby a SSIS package runs fine in Visual Studio but fails immediately when attempting to run in SS Agent, throwing up a "...the system cannot find the file specified" error.

  After thinking it was something complicated (problem with the FTP task, proxy issue, password issue) I simplified and found that it's one of the first tasks, an execute process task that calls a .bat file, and it's clearly not finding the .bat file even though I'm calling out the full path of the bat file (c:\documents and settings....) in the task. Everything is running on the same machine so that shouldn't be an issue. I tried putting the path to the .bat file in UNC format, wasn't able to get that to work.

  I've tried running at the SQL Service Agent Account and as a proxy account that I created (and given cmd exec privileges, etc), nothing seems to help.

  Suggestions very welcome! Thanks in advance

• Doug Andersen

  Old Hand

  Points: 325

  More actions

  November 13, 2007 at 12:10 pm

  #750801

  I seem to have fixed this by randomly changing parameters. I had been running the step as "cmd exec" in SQL Server agent as suggested by several people and it did give me more details on where the job was failing (specifically it couldn't find the file), but once I switched it back to SSIS package it runs OK (in combination with a proxy).