Search code examples
sql-serverssissql-job

SQL Job completes successfully but does not execute packages

I have taken a look at several articles including this unanswered question: SQL Server Job runs successfully but doesn't execute packages

I have the exact same problem in SQL Server 2012 using the integration services MSDB catalog. I can execute the SSIS packages manually from that catalog, but the agent job doesn't do anything except state that it completed successfully. I have also executed my SSIS packages from within Visual Studio and they worked just fine. Here's the situation and am wondering if it may be permissions:

  1. SSIS packages look for Excel files matching criteria in a network location.
  2. Once found, the SSIS packages writing the data into the database and archive the file to another folder on that same network location.
  3. Emails are sent upon any failure of import of data into the database or migration into the archive folders.

I have the SQL Agent job running the SSIS packages from a package store (MSDB) using the SQL Server Agent Service Account to run under. Currently we are not doing any sort of project deployment to these servers so I am sticking with package deployment. Here are some steps I've taken:

  1. Run packages manually from Visual Studio 2010 (fully successful).
  2. Run packages manually from SQL Server MSDB catalog (fully successful).
  3. Run job manually from SQL Server Agent using parent package as a step that will execute child packages as an external reference (success but nothing happens).
  4. Run job manually from SQL Server Agent using each package as its own step excluding the parent package (success but nothing happens).

Any ideas? Permissions to the network location or need a proxy? Again, I am running Microsoft SQL Server 2012 Enterprise Edition 64-bit. Many thanks for any help you can provide.

Solution

  • Found the problem. My SSIS package has a foreach loop container and, while the tasks inside the loop container couldn't access the destination, the loop container technically completed successfully. We had to give permissions to the account the steps were running under for the job to correct that. These permissions were put on the network location to allow that account access to read and write to that location. Additionally, my Excel connection was 64-bit so we enabled it to 32-bit runtime and this allowed that portion of the process to complete successfully. I re-enabled any disabled tasks and it looks good to go now. Thanks!