January 15, 2009 at 4:09 am
Hi,
I have SSIS package which has 5 dataflows and each dataflow copies around 10 million records. When i execute this package from Business Intelligence studio, It was completing in 15min to 20min. But when i execute the same package from stored procedure(executed from SQL Job), it was returning the final result(either success or failure) after 2 to 3 hrs from the time it completes the table download. Please advise me
Thanks in advance.
January 16, 2009 at 1:56 am
Can anyone help me to resolve this issue pls?
January 16, 2009 at 2:19 am
Lets try to get into it...
Please provide answer to the following queries:
1. Are you excuting on the same server in both the scenarios?
2. Were the number of records initially (before load) were same in both the scenarios?
3. Are there any schema changes (like any index added on the destination table) after you executed it from BIDS and before executing from the job?
I also not sure why this is happening , ..Just trying to help (if it works):)
-Vikas Bindra
January 16, 2009 at 3:04 am
Just adding to the previous reply
These are the steps to take for increasing the performance of ssis package execution
1) We can increase the defaultbuffersize and the defaultbuffermaxrows size of a data flow task
But need to take care for the number that you keep exactly.
January 18, 2009 at 11:59 pm
Thanks for the help Vikas & Srikanth,
Here are the answers for the above queries
1. Are you executing on the same server in both the scenarios?
Yes, i have deployed the package in a particular location and executing the same in both the ways.
2. Were the number of records initially (before load) were same in both the scenarios?
Number of records were same in both the ways
3. Are there any schema changes (like any index added on the destination table) after you executed it from BIDS and before executing from the job?
No, I have not made any changes in the schema.
I am individually tracking the StartTime and EndTime for all 5 data flows. The Performance of the each dataflow was same in both the ways. There is no change in time for coping data. I think the only problem is that it was not immediately returning the results when we execute using dtexec. Please see the below is the code
Declare @cmd varchar(1000),
@result INT
set @cmd = 'dtexec.exe /f "E:\SSIS\Package1.dtsx"'
EXEC @result = master..xp_cmdshell @cmd,no_output
IF @result <> 0
BEGIN
--Logging info when failure
RETURN (-1)
END
Hope this is very clear
Thanks in Advance
January 19, 2009 at 12:26 am
surya.narayana (1/15/2009)
Hi,I have SSIS package which has 5 dataflows and each dataflow copies around 10 million records. When i execute this package from Business Intelligence studio, It was completing in 15min to 20min. But when i execute the same package from stored procedure(executed from SQL Job), it was returning the final result(either success or failure) after 2 to 3 hrs from the time it completes the table download. Please advise me
Thanks in advance.
Can you tell us what operations you are performing in 5 data flows? this will be helpful to track the solution
Abhijit - http://abhijitmore.wordpress.com
January 19, 2009 at 12:37 am
It's a bit weird....There is no problem preformance problems with dtexec (as per my knowledge).
Can you execute your SP once again and see whether there are any blockings happening at the server (using sp_who2/sp_who)?
Is there someone else who has faced similar issue?
-Vikas Bindra
January 19, 2009 at 6:07 am
In dataflows, We are coping data from As400 to SQL sever. We have only Oledb Source and Oledb Destination. One guy who is in the other team has similar issue, but no solution
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply