SSIS packages get delayed by 45 seconds each in a scheduled job

Question

SSIS packages get delayed by 45 seconds each in a scheduled job

des mullin

SSC Rookie

Points: 36
More actions
February 7, 2008 at 4:04 pm

#183755

I have a number of SSIS packages and if I run each interactively they take 2 seconds.
They are extracting records from another SQL 2005 server and inserting them into a local copy of the database.
But when they are in and Scheduled job they all take 45 seconds each.
At first I thought that it was an authentication problem but I have tried various methods of making the conecction and no change. The users involved has sysadmin rights on both servers.
Any ideas would be appreciated..
The step in the job is
INSERT MD_JobQueue
(UserID, JobDescription, Status, SubSystem, Command, StartTime)
VALUES ("LPC\lawsondm", "Populate [CFACIL] in [Base71_Staging]", 1, "TSQL", "'", getdate())
SET NOCOUNT ON
DECLARE @res integer
exec @res = master.dbo.xp_cmdshell 'dtexec /SERVER JOHNNY /SQL "Base71_CFACIL_M3 BE 13_1 Full" > "C:\Temp\BPW Background [Populate [CFACIL] in [Base71_Staging]].ERR"'
if @res <> 0 RAISERROR 34000 'Task Failed'

Viewing 5 posts - 1 through 4 (of 4 total)

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

beath SSCrazy Points: 2939 More actions · Answer 1

Hi there.

We've been having the same problem and it's taken ages to find the solution. It would seem that when you start an SSIS package from a scheduled job the first thing it tries to do is connect to the interent to http://crl.microsoft.com. If your network is anything like ours then internet access is blocked from servers and so it takes ages to time out before eventually starting the package.

A suggested solution is to add 127.0.0.1 crl.microsoft.com as an entry into the hosts file on the server so that the web address is resolved to the local host and doesn't try and connect to the internet. This worked for us and the 1 minute delay to our packages has gone.

You can read allabout it here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1523670&SiteID=1

Hope this helps

cheers