SQL Server Performance

SSIS Package runs slowly when scheduled as a job

Discussion in 'SQL Server 2005 Integration Services' started by brynjon, Dec 10, 2007.

  1. brynjon New Member

    My SSIS package completes in 50 mins when executed from the SSIS instance object explorer in Management Studio. However when I schedule it as a job it consistently takes over 4 hours to complete. The package is deployed to the local MSDB database and the job that runs it is also local (i.e. everything is on the same box). The package itself has no configurations. Why should scheduling the package make it run so much slower ?
    tia
    Brynjon
  2. satya Moderator

    What kind of permission the SQL SErver service account on thsi server?
    Does this package includes any network resource usage?
    http://support.microsoft.com/kb/933835 fyi, also you could look into the package log for more information where it is lacking the performance with PROFILE.
  3. brynjon New Member

    HI satya
    thanks for this
    The SQL service account is sysadmin on the instance hosting the MSDB database into which the package has been deployed. The package does use network resource as it imports a number of tables from a SQL database on a remote instance into a database on the local (to the package) instance. The package being run is the same (and succeeds) in both cases - it's just that when it runs as a scheduled job step it is very much slower.
    Brynjon
  4. satya Moderator

    So you need to check connectivity between thsi server and remote server, make sure you have alias defined within SQL configuration manage on source to destination server. Also ensure you are using TCP/IP or named pipes, if so check the remote server do accepts remote connections.
  5. brynjon New Member

    Hi Satya
    Fairly sure this is not a connectivity problem. The package (whether it is run from a job or by executing it in SSIS) does succeed so it must be able to connect. What I need to find out is why the same package is so much slower when it runs from within a job ?
    regards
    brynjon
  6. satya Moderator

    Have you checked the connection settings within Configuration manager in this case?
    When a scheduled package fails, and you need to run it interactively, then make sure you are running the package on the server itself, by logging into the server directly. There is a lot of confusion about, where exactly a package runs. DTS or SSIS is a client side application. A package when scheduled using SQL Agent as a job, it runs on the server. When you run the package interactively, then the package runs on the local machine. That is, you connect to a remote SQL Server using Enterprise Manager or SSMS on workstation, the DTS packages you run will run on your workstation. This will result in slower package execution as the data flows over the network, between your local machine and the server. If you cannot log onto the server directly, then you need to make sure all the files, servers referenced in the package are accessible from your machine as well.
  7. brynjon New Member

    Satya
    Thank you for explaining this. I was not comparing like with like.
    I wrongly assumed that when you run a package (deployed to MSDB on a remote box) by executing it from the SSIS instance registered in Management Studio Object Explorer on my workstation that it runs on the remote box.
    My stupidity was compounded by the fact that there already existed a problem with the SSIS instance (possibly KB913817 related), one of the symptoms of which was an inability to register SSIS in Management Studio on the server itself (whereas it registers OK in MS on my workstation) so I had never actually run the package on the server itself. Had I done so, I probably would have discovered that the package runs as slowly as the job.
    thanks again
    Brynjon

Share This Page