Hi Everyone, I have created SSIS packages to move data from AS400 to SQL Server which are scheduled daily.some of the packages in sql agent are taking longer duration more than 9 hours to complete.IF I run same package in Business intelligence studio manually, it is completing in less than 4 hours.Due to this problem my schedule packages are not competing on time.please help me to sort out this issue. I am unable to understand why there is a difference in task completion duration between manual interaction and schedule jobs. My environment is windows server 2003 with sql server 2005 with SP3.please help me to sort out this issue.
+1
A:
The best way to get around this problem is to watch the scheduled task by using some debug statements and messages. For example, have some insert statements in the stored procedures the SSIS package is invoking. This way u will get to know what control is taking more time than expected. First try to isolate the control that is making the difference.
Also, you can invoke the package from command prompt using:-
dtexec /f filename.dtsx
This will print out all the messages in the console at each step as well.
A_Var
2010-09-22 07:40:56
thanks i will try to check with ur suggestion
Mohammed Mushtaq
2010-09-22 16:10:19
+1
A:
Use SSIS logging in the package to log to a database table. Set logging to record start and end of tasks. By running the package in BIDS and comparing it to the logging when it is run on the server you will see which tasks are taking too long. See http://msdn.microsoft.com/en-us/library/ms138020.aspx for more info on SSIS logging (in sql 2008)
StephaneT
2010-09-22 09:37:27
A:
Might it be that the SQL server is less powerful than your client or has more load when you execute the package?
Business intelligence Studio the package is executed on your local client with it's CPU and RAM (I think).
Cilvic
2010-09-22 11:58:06
It is not installed on the client system.I am running schedule packages in my production server.same server i am using to execute packages from Business intelligence Studio as well so the cpu and ram consumption is same in both the sceanrio
Mohammed Mushtaq
2010-09-22 15:19:24
I disagree. Packages executed in BIDS run on your desktop and connect to the sql server only for executing sql, as Cilvic says.
StephaneT
2010-09-26 17:59:31
A:
Check what version of DTSEXEC you are using. May be you are using 32-bit version at one place and 64-bit at the other one.
Faiz
2010-09-27 10:39:13
No brother it is a 32 bit version because my production server is 32 bit and the package which i developed are related to 32 bit versions.
Mohammed Mushtaq
2010-10-03 14:43:50