Package runs forever while running from SQL server agent Job scheduler

  • Hi guys ,

    I have defined one package from SSIS which performs following tasks:

    1. Launch FTP files from FTP server

    2. Extract those files, which are basically (.gz) zipped files and load extracted files to another folder.

    I am using FTP task for task 1 and Execute process task for task 2.

    No when I run this package from my SSIS then ot runs very fine and produces results as expected BUT when I schedule them from Job scheduler in SSMS then this package runs forever. I am not sure what is the problem but the package does not end executing itself.

    I am not sure if I have access problems. I have tried running packages from different locations but did not earn me any results.

    Any thoughts?

    Swaroop

  • [font="Comic Sans MS"]

    Hi Swaroop,

    Did you try adding logging? Can you see the package progress?

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • Can you please describe in detail what is logging and how to use it?

    The package only hangs when I use a job agent to execute it. It executes fine when I use SSIS to execute it.

    Is there something that the job agent requires some permissions to execute it and is not getting the required permissions?

    There are two tasks in that package ..first downloads the zip file from FTP ( the job agent does so) and the second unzips the zipped file. (this one hangs and executes forver).

    I am using Winzip to unzip the files.

  • [font="Comic Sans MS"]

    Hi Swaroop,

    I think - the package is not being kicked off by SQL Agent. You would need to check this.

    In order to be sure about this - you can implement SSIS logging inside the package. You can refer:

    http://msdn.microsoft.com/en-us/library/ms138020.aspx

    Simply - right click at any empty space in your package (control flow) - click logging OR click SSIS (on toolbar) -> Logging. Then select type of logging ..

    For your benefit - select all the events and execute the package. You will be able to see all the steps logged as the package proceeds. This is indepenmdent of whether it's being fired from BIDS or by SQL Agent.

    When it's triggered from SQLAgent - you can then see the log and understand where the issue is. In case you can't see the log is being created this means the package is not being triggered by SQL Agent job.

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • [font="Comic Sans MS"]

    For the last scenario - refer here:

    An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

    http://support.microsoft.com/kb/918760

    [/font]

    [font="Comic Sans MS"]--
    Sabya[/font]

  • Is Winzip opening a window when run via SSIS?

    It could be that the Winzip process is waiting for a response (which the scheduler is never going to give).

    -------------------------------------------------------------------------
    Normal chaos will be resumed as soon as possible. :crazy:

  • Hi Guys.

    Thank you for responding to my post and helping me out.

    I found the solution to the problem.

    I changed the log on as account of the SQL server Agent service to "This account" from "Local System" in the SQL Server configuration manager.

    I think the problem was that the SQL server agent was unable to access my C: or desktop as it lacked administrative priveleges. And thus it was not able to start the Winzip process for me.

    Setting the log on as account to "This account" and specifying my computer username as the Account Name solved the issue as my computer username is the administrator.

    I hope I have interpreted the problem correctly....please correct me if I am wrong.

    I appreciate ur help.

    Thanks,

    SWAROOP

  • I have a similar issue.

    select 1    this is all I have in a test SQL Agent job and runs forever.

    This is an enterprise 2019 on a datacenter server.

    on top of that, no history is saved.

    crack this one!

    Cheers,
    John Esraelo

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply