I created a very simple SSIS package (it just updates a single row in a table). When I execute the package from the command line (using dtexec), it takes about a second to finish, as expected. But when I execute it using dtexec via xp_cmdshell, it takes about 91 seconds. When I use a SQL job to execute the package as an operating system type, it takes 91 seconds. Using a SQL job to execute it as a SSIS package takes again 91 seconds. It appears that something is causing a delay of about 90 seconds before the package actually gets executed. I tried changing the SSIS service account, but that didn't change anything. Why is executing the package through SS2005 different than executing it directly from the command prompt?
Are you sure it is even executing when it takes 91 seconds? Can you verify that the insert occured? I'm suspecting that it is timing out, probably due to a permissions issue. SSIS is notorious for the poor quality and quantity of its warning and error messages.
If it's not practically useful, then it's practically useless.
Yes, I can confirm that it's executing because I can query the data that it's updating. I created this SSIS package mainly as a test before I convert the production DTS packages to SSIS packages. I'm concerned that all of the packages will exhibit this strange 90 second delay. Does executing an SSIS package from the command line use a different set of permissions than executing it through SS2K5?
NOTE: When I right click the SSIS package and execute it that way, it runs in about one second, as when I use dtexec from the command line. So the 90 second delay seems to happen if I use dtexec from the SSMS query window, or I use dtexec in a SQL job.