Delay In Package Starting When Running From SQL Agent
Jul 19, 2006
Hi,
I wonder if anybody can shed any light on this problem. I have a SQL Agent job which has three steps, each step runs an SSIS package.
The job is scheduled to start at 11.00 pm, which is does successfully. However, it has been taking between 2 and 3 hours to run, which is way longer than it should.
When I've looked at the logging, I've found that the although the job starts at 11.00 pm, the first package (in job step 1) does not start executing until about 11.30. It finishes in about 5 minutes, there is then about an hour delay before the second package (in job step 2) starts. This finishes in about 10 minutes, then there is another hour delay before the third package (in job step 3) starts.
I've tried configuring the steps as SSIS jobs, and also as cmd jobs using dtexec, both exhibit the same behaviour.
Any ideas about what could be causing this delay? The packages are stored in msdb on the same server as the SQL Agent job, if that makes any difference.
All my SSIS packages consistently take 1 minute to start when they are scheduled via the SQL Agent.
For example, a package runs in 20 seconds in the BI Studio. I transfer it to the SQL server and store it in MSDB. I then run it manually from SSMS - Integration Services and it takes 20 seconds. I then schedule it via a job in the SQL Agent and it takes 1 minute 20 seconds.
I can see from some simple logging that there is consistently a delay of 1 minute between the job starting and the package starting.
I have also switched every occurrence possible of DelayValidation to TRUE in all my packages and tasks. All this did was reduce the package run time from 27 seconds to 20 - the 1 minute delay still exists.
This happens on all my packages on all my servers. Any ideas.....?
I'm testing my IS package in my local PC which extracts data from one SQL Server table to another SQL Server database. In BIDS, it's running without error. I also tried it in cmd with DTExec /f "c:package.dts", it also ran ok. I tried assigning it in SQL Agent as job. with same command DTExec. This time, it ran successfully but got 0 rows. I dunno what went wrong, why suddenly it cannot get data from the source.
I'm new here and hope you will be able to help me.
I have created several SSIS packages with Visual Studio 2005. They all work fine in debug mode. I have been able to make them work with a ODBC connection by using a ADO.NET connection.
Then I exported them to the file system in my SQL Server 2005 database and created a task in SQLAgent to run them.
All the packages using the ODBC connection fail with the following error :
Login failed for user XXX Error : 18456; Severity : 14 , State : 8
This error is a password mismatch.
I tried several database users and checked the passwords multiple times.
It looks like SQL Agent is not able to retrieve the password although it is stocked in both the ODBC connection and the SSIS connection.
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "'I:MyFolderMyRptsMyApplicationMyDatabase.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
The above path "I:...." is mapped on the server that SQL Server is running on.
This package runs with NO problems in MS Visual Studio.
I have a number of packages that I have moved from an old server. Each package was scheduled with a SQL Agent job. On the old server everything ran fine. All of the packages run fine from VS, from DTEXECUI and I have tried one from the command line with DTEXEC and it worked.
When I run from the SQL Agent job, I don't get a failure, the package just hangs. I let one of the agent jobs sit for an hour with no progress. The package typically takes about 15 minutes to complete.
Below is the output from my package log up to the point that it hangs:
thanks for the valuable information all the time!!! saved me a lot of time...
our team developes a system for text data improvement using ssis .
we have a few heavy packages that we want to execute on two separate "SSIS servers" that will be dedicated to runnung these packages only, and repeatedly. the main sql server will be placed on a different server machine .
my question is:
what is the best way to do this?
if we schedule these packages as a job of the Sql Ajent- does that mean that the packages will be executed on the sql server machine (which is not what we want) ? or could we define a remote machine to run the package on, and specify our "SSIS servers"?
or- should we use a simple scedualer on the "SSIS servers" using a dtexec command? but then i loose the benefits in using the sql agent- such as logging, notifications, etc.
do we need to install sqlserver on these "ssis servers"?
Okay, i've got an ETL package which works flawless, we've created a scheduled job for it which also worked. This ETL accesses a flat file located on a server on a different location in the same domain.
Last week we start a migration to a new network, the location where the flat file is stored has already moved to the new domain but the SQL server agent is still on the old domain. There is a trust between the 2 domains, the network user from the old domain has been added on the server in the new domain where the flatfile is located.
If i run the etl package manually it works flawless, now if i run the etl using the SQL server agent it won't run anymore.
I get the following error:
Executed as user: OIdDomaindwh. ...n 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 21:15:00 Error: 2007-10-02 21:15:02.10 Code: 0xC001401E Source: Rolmer_Product Connection manager "Flatfile Product" Description: The file name "\fs-cha-fs01dwhproduit.csv" specified in the connection was not valid.
Don't tell me its a problem related to the user that the SQL Server Agent is set to because its set to OldDomaindwh which is the same user i use to login on the server where i can manually run it without problems. The problem only occures when i run it using the SQL server agent which uses the same user to execute packages.
This is a very difficult problem i doubt anyone here will be able to crack this nut, but if they do i'll be very impressed because it would require a real smart network admin with SQL experiance to figure this one out...
I'm trying to deploy a SSIS package and am having issues getting it to execute from a SQL Agent job. I've read this article http://support.microsoft.com/?kbid=918760 over and over and although it's helpful I must still be missing something.
The SSIS package uses a configuration file for connection string information and the ProtectionLevel is set to DontSaveSensitive. I'm using a proxy account with the appropriate permissions. I can execute the package from SQL Management Studio and from dtexec.exe just fine. I even copied the command line out of the job and used that to execute it with dtexec.exe and it worked.
I'm not sure what to try next. It's like it's not even getting to the SSIS package. I have logging turned on in the package and I"m not getting back any errors from it. Any ideas? One thing that I think is strange is that the job is running on an instance of SQL but Integration Services doesn't seem to support instances. Could that be the issue?
I don't know what else to try at this point...any advice is greatly appreciated!
1) Gave the sql agent account permissions to the msdb database and other databases
2) Checked the paths for "Data Sources" under properties of my agent in the SSIS properties
3) Set the authentification to Windows and used the sql agent account (a domain account that I setup) to run the agent. I also tried my login. So it's not a permission issue, because I do not get permission errors...it's past this.
What works:
1) Other SQL Agent packages...they are not running an SSIS package, just SQL
2) Running the SSiS package by right-clicking on it in Management Sudio. No errors found in the execution and the package does it's intended function
3) Package runs fine with absolutely no errors in VS 2005
What doesn't work:
1) My agent that points to my SSIS package. All this agent does is run that package. When I run the agent, I get a minimal error message in the job history logs stating the following error message:
03/09/2006 12:49:58,Run EN Process,Error,0,BG-22SQL,Run EN Process,(Job outcome),,The job failed. The Job was invoked by User domainmy_account. The last step to run was step 1 (Run EN SSIS Package).,00:00:02,0,0,,,,0
03/09/2006 12:49:58,Run EN Process,Error,1,BG-22SQL,Run EN Process,Run EN SSIS Package,,Executed as user: domainsqlagent_account. The package execution failed. The step failed.,00:00:01,0,0,,,,0
I have setup a step in SQL Server Agent to run a SSIS package that I have created. However the step fails straight away and refers me to the history log, which doesnt seem to show what the problem is.
I've tried running the package manually through dtexec.exe and it runs through fine. Does anyone know what the problem could be?
Hello, I have created a package that runs without problem. I run the package with the command dtexec /F "package_name.dtsx" > package_name.txt.
Then I run the same package from SQL Server Agent, everything is OK
Then, I tried to edit the command line to have the output file, but I got an error.
The command line is: dtexec /F "package_name.dtsx" MAXCONCURRENT "-1" / CHECKPOINTING OFF /REPORTING E > package_name.txt. (MAXCONCURRENT "-1" / CHECKPOINTING OFF /REPORTING E are created by default)
My package is connecting to an external data provider using an OLEDB driver . The package runs fine in debug mode.When i tried to run the same from SQL server agent it failed  to aquire the connection. The OLEDB provider does not contain too much of information , ( connection string, initial catalog, blank user name and password).The same package executes successfully if i run using dtexec in BAT file.But if i use the dtexec in sql server job step as operating system command and try to run, the job will fail reporting " can not aquire the connection".
We have SQL 2000 (SP2) , Our SQL server agent showing green light as started from EM , It is succussfully restarting from NT Services too . But When I open the properties of the jobs from EM its gives error message SQL DMO error " SQl Agent is starting , try later " . And in EM Next run Date & Time not available .
MDDB database is accessable & in Log file its recovered fully
I'm trying to run a task that executes a script file (cmd). When i run it with in bids with my own users (domain admin) it works. When i start a cmd prompt and try to run the cmd file directly from the network location where it is it works (with my own rights and with the sql server agent user).
Now when i try to run in from smss > agent jobs > job and run job it never completes. Im not getting any error message either it just keeps on running on the step ??? It seems like a rights issue, but the account running the sql server agent is able to execute the cmd file directly from the command prompt.
There are no errors in any error logs anywhere and no error is displayed...
Ps. Im running the job step as a integration service pacgake.
Hi, when call a sub-package from the parent package with the executeoutofprocess=true I have about 5-10 sec delay before sub-package starts running. ( this is a big delay for me as the sub_package is in a foreach loop)
the sub_package itself it's very simple and contains just one script task with a small script in in it. even if this delay is for validation I still can't understand why it is taking 5-10 ses. cheers
Hi, when I try to run my package with dtexec.exe, it starts fine but in the process it package calles another subpackage and at the time there is big delay before start processing the subpackage. the subpackage has been setup so executionoutofprocess pramater has been set to true. any idea what migth be the problem. I have to metion even when I run this with in the visual studio still I have a big delay.
I developed an application uses sqlserver 2005 express edition
I create all queries in storedprocedures.every things work perfect but some times I get long delay in running queries. but after some minutes it comes regular . I coudlnt find any relation between delay and time of work.it comes by chance . also I set timeout for running query for 30 sec. but some times it took more than minutes.
The SQLAgent service was modified to login as a domain account. SQLAgent would not start. I made sure that the account was in the Admin group on the server, I setup the account as a System Administrator in SQL. I made sure that the network protocols in Client Network Utiliity and Server Network Utility where the same as other servers using the domain account. The message I'm getting is...
SQLServerAgent could not be started (reason: Unable to connect to server; SQLServerAgent cannot start).
I modified the properties for SQLAgent back to login as local account and I still get the same problem.
For three months I have be starting the SQL Agent using the following bat file
EXEC XP_CMDSHELL 'NET START SQLSERVERAGENT'.
Starting this week the agent is not starting. This is the error message
10/26/00 6:25:36 PM - ! [000] Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: A required privilege is not held by the client)
I am unable to get SQL Agent service to run under the same domain name that my SQL Server service is running under. It will start and continue to run when Server and Agent are both ran under the Local Account. But once the services are switched over to the domain account, the Server starts, but the Agent fails. The domain user has administrative rights on the server, along with admin rights to the SQL instance.
Here is the event log.
Event Type: Error Event Source: SQLSERVERAGENT Event Category: Service Control Event ID: 103 Date: 3/28/2007 User: N/A Computer: SQL2 Description: SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to the SQL Server as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).
Any help would be greatly appreciated or links to threads with answers.
when I run a package from a command window using dtexec, the job immediately says success. DTExec: The package execution returned DTSER_SUCCESS (0). Started: 3:37:41 PM Finished: 3:37:43 PM Elapsed: 2.719 seconds
However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.
Need your advise on this. I have a DTS package which check for 2 dates and execute tasks when the date do not matched. The problem I am facing now is I could make the next step to start only if the previous step is completed. When the DTS package is executed, all steps being completed almost at the same time. See below / attached DTS package.
In the disgram, I have labelled 5 steps A ~ E, each step needs info from the finished product from previous step to produce correct result in it's own step. I couldn't schedule each step to run at different time because the DTS kicks off based on a file that comes in and each step doesn't have a fixed processing time to complete.
I have tried using On Success or On Complete and both options start the next step immediately not not wait for the job the complete or success. I guess this is because I have transferred the command to external when using command. Is there a way to control by some delay between each task?
Please advise. Thank you.
Each of the step has something like below (refreshing of excel file with macro build in):- I cannot build all macros into one file and run from the main excel.
select @MainUpdate=Main_Update_CET from APMEAPV_Compare select @TempUpdate=Temp_Update_CET from APMEAPV_Compare --select @MainUpdate, @TempUpdate
if @MainUpdate<>@TempUpdate begin DECLARE @commandK varchar(1000) SET @commandK='Start Excel.exe "D:Daily_Status_Report_EDWHEDWH_Runbook_BTS.xls"' exec master..xp_cmdshell @commandK, No_Output
I am currently experiencing a 30 second delay when starting an SSIS package from a query window or stored procedure in SQL 2005 Management Studio, using xp_cmdshell and dtexec.
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, this test was run using the same configuration and databases. For the MStudio run, if I look at the DTS log file I€™m creating or the PC Application log, it states the package doesn't actually start until 31 sec after the execute button is pressed. I€™ve tried executing the package as both a SQL package and a file package without any difference in elapsed times. I have also set DelayValidation = True for every Task, ConnectionManager and the package itself.
When I look at the package log one difference I see is that the Management Studio executes using €˜NT AUTHORITYSYSTEM€™, 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 think 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.
I also 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.
Hi, we have a SQL server 7.0 on windows 2000 m/c,from yesterday onwards suddenly sqlserver agent is not starting,till then it was working fine. I tried to start SQL server agent manually and i got following message
"[241] Startup error: Unable to initialize error reporting system (reason: The specified module could not be found) "
Then i tried to start from services in controll panel,but i could't.
Then i checked event log ,found following error messages
Application log:
1) "The description for event ID(105) in soure(SQL Server Agent) can not be found. The Localcomputer may not havethe nessary registry information or message DLL files to display messagees from a remote computer.The following information is part of the event: The handle is invalid."
Event ID: 105 Source : SQLServerAgent Category: Disk
system log:
1)The Sql server agent service failed to start due to the following error: The service did not respond to the start or control request in a timely fashion.
Hi Folks, Can anyone amongst u solve my problem..When i try to start the Sql Server Agent..i'm given the following error meassage.. Could not start Sql Server Agent.Error 2140.An Internal Windows NT error. Please help me if anybody can.
We have SQL Server 7.0 running on an NT 4.0 sp5 box. Whenever we stop the SQL Server Service, we get the message that SQL Agent will also be stopped. If we do this, all the services seem to stop fine, but when we try to start the SQL Server Agent Service back up, it won't let us. In order for it to restart, we have to reboot the server so that it starts 'automatically'. Does anyone have any ideas as to why this is?
How do you start SQL Agent? MS states not to start thru Service Manager as it may corrupt registry. There is no Resource for SQL Agent in 7.0 cluster. The service is down, need to start to get jobs to run. I've just checked (enabled) the properties so Agent will start on O/S reboot or start after Unexpected stop. How do I get it start in the meantime? Thanks!
Hi have you guys seen this error when trying to start the sql agent?
SQLServerAgent could not be started (reason: sqlserveragent must be able to connect to sqlserver as sysadmin, but '(unkown)' is not a member of the sysadmin rol.
What is this all about I can start the sql server service with the same account i'm trying to start the agent.
Whenever I restart the Server , SQL server service is starting properly, but the agent is showing the green sign in EM ,agent Log also its showing its started. But in EM the jobs are not showing the next run time its showing ( Date and time not available). When I double click the jobs its showing the following error message Micorsoft SQL - DMO ( ODBC Sql state 42000) Error 14258 : can not perform this operation while SQL server agent is starting) FYI :This server contain the databases of SMS too .
