Much Slower SSIS Package Execution in Production Environment

Saturday, June 9, 2007

I had been working on a simple ETL SSIS package to simply pull data from a file, parse it, and insert it into some tables on a SQL 2005 box.  In my dev and testing environment the package ran extremely fast… under a couple of seconds.  But as soon as I added the package to our QA environment and schedule the package to be executed as a step on a SQL Agent job, the package took up 4-5 minutes to execute.


I knew the data loading and extracting could not be causing the problem.  When I looked at what services were running on the box, I noticed SSIS was not running.  I tried to start it and it errored out with “Service could not be started in timely fashion”.  However, the package still ran (slow, granted, but it still executed).  I finally found some insite into the problem from this KB article on Microsoft: http://support.microsoft.com/?kbid=918644 .


Apparently, SSIS needs to ping this host http://crl.microsoft.com/ in order to start correctly.  Instead of applying the hotfix, I just added the line 127.0.0.1     crl.microsoft.com to the host file on the box.  I attempted to start the SSIS service again and “voila”, it was running.  The package is executing within a respectable time range once again.


One Comment

  1. http:// says:

    Hi Mika,

    I have developed ETL packages (10 in numbers). The main workflow calls the remaining 9 packages using
    package execute task. We have very small data i.e. just 50 rows. Our ETLs are doing lot of data manupulation using Data flow tasks to clean the data. We are using For Each Loop container in each package.

    When we run it for Visual studio, it hardly takes 3-4 minutes. When compiled and deployed on same machine, it takes around 50 minutes. It is hard to imagine the time it’s taking to run.

    Please provide any pointer on why it will be that much slow with so less data? If it’s code issue then how it’s running fast on visual studio. Is there anything I can do to improve the performance back to 3-4 minutes.

    Also provide your pointer if I can capture and monitor the execution logs to find the performance issues?

    Thanks,

    Rahul

Leave a Reply