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.