SSIS package execution delay

Question

SSIS package execution delay

dan preziosi

Old Hand

Points: 332
More actions
March 27, 2007 at 2:18 pm

#116539

I’m experiencing a 30 second delay when starting an SSIS package from a query window in SQL 2005 Management Studio, using xp_cmdshell and dtexec. If I run the same package from the BI Dev environment or from dtexec at a command prompt I don’t see this delay. I’ve also tried executing the package as a SQL package and a file package. When I look at the package log one difference I see is that the Management Studio executes using ‘NT AUTHORITY\SYSTEM’, BI Dev and the cmd prompt use the local user ‘[Server]\Administrator’, which in this case is the administrator. From this I have to believe it is some kind of user rights problem. I tried making an xp_cmdshell_proxy_account with admin rights but this didn’t seem to work either. I’ve included the query code below. Any ideas, help or solutions are greatly appreciated.

DECLARE @cmd varchar(250)
DECLARE @Result INT

SET @Result = 0
--SET @cmd = 'dtexec /F "C:\temp\DP2000 DataTransfer.dtsx" /DE ttalg /REP EW'
SET @cmd = 'dtexec /SQL "DP2000 DataTransfer" /DE ttalg /REP EW'

EXEC @Result = xp_cmdshell @cmd

SELECT @result

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Two questions come to mind. First, when you say there is a 30 second delay, are you saying it takes 30 seconds for query window to return from executing xp_cmdshell? Second, when run directly, how long does the SSIS package run?

dan preziosi Old Hand Points: 332 More actions · Answer 2

There is a 30 second delay from the moment I press execute in MStudio to when the Package actually starts executing.

When I run the package in BI Dev the execution results state an elapsed time of 4.82 sec, at a command prompt using dtexec the elapsed time is 3.48 sec, from MStudio the elapsed time is 33.86 sec. For the MStudio run, if I look at the DTS log file I’m creating in the package it states the package doesn't actually start until 31 sec after the execute button is pressed.

I believe SQL or the OS is waiting for something and after it times out at 30 sec, it allows the package to run. If this is the case I’m not sure what it might be or how to find it.

dan preziosi Old Hand Points: 332 More actions · Answer 3

Just some additional information, I've set DelayValidation = True for all tasks, connections and the package itself. None of these settings had any effect on the 30 sec delay.

dan preziosi Old Hand Points: 332 More actions · Answer 4

More Testing Info. This package is part of a development project and I had only created that one package, so I created some additional packages and this is what I saw:

Package 1: 1 Connection, 1 SQL select task, DelayValidation = true for all. 30 second delay still exists.
Package 2: Created new package and copied original package tasks and connections over. 30 sec delay still exists.
Package 3: Empty, nothing in package, no tasks and no connections. No 30 delay!! Package executes immediately in MStudio.
Package 4: Added Script task (No code, except setting Success) to Empty package, still no DB connections. 30 sec delay returns!!
Package 5: Created another Empty package, configured package logging and added a file connection. No 30 sec delay.

Is there any way to trace exactly what MStudio is doing while executing?? Maybe this would help.

Also some background on software and hardware:

Win 2003 R2 SP1, SQL 2005 SP2, standalone development server, not part of a domain
Dell PE 2850, (2) 3.0GHz Xeon, 4GB mem, dual NICs, Raid 1 and 5