Home Forums SQL Server 2005 Business Intelligence SSIS package not completing when running from SQL Server Agent job RE: SSIS package not completing when running from SQL Server Agent job

  • Chances are it is not a permission issue related to reaching the executable or related to something the SSIS package or executable are trying to do otherwise the job would simply fail. That said, it could be related to the aforementioned fact that the packge is now going to be running under the context of either the proxy account the job step is set to run as, or the SQL Agent service account.

    What does the executable do?

    I have seen lots of posts with similar descriptions where the root cause is attributed to something the package is doing, either directly or indirectly, that generates an interactive prompt. Since this is an unattended process there is no one to answer the question in the prompt so the package just sits there indefinitely. Examples of this scenario are SFTP and PGP. When you first kick off a process that requires the use of an encryption key you might be prompted to accept the use of the keys so they can be added to the current user's keyring. In this instance, if the user running the SSIS package via the SQL Agent job has never used these keys then a prompt will be generated.

    The fix is to log in as the user running the package via the job, and then run the command line in the job step interactively to see if there are any prompts or messages.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato