May 11, 2006 at 9:35 am
Hi All
When I am executing a SSIS Package (Internally It calls some child packages) from a Job (Sql Agent), it is taking around 100% more time comparing to running these packages from Business Intelligence studio(on same server)!!.
Can anybody gives some insight on this?...
Regards
Agson Chellakudam
May 11, 2006 at 3:46 pm
My initial thought on this might be dealing with pulling network files. Now, when you're running the BI studio, are you running it on your local dev box and connecting to the server? Or... are you actually running BI studio directly on the server? What kind of SSIS tasks are you performing? Any file system copies that use network paths?
May 11, 2006 at 11:39 pm
Hi Robert
First of all thank you for your reply..
I am running BI studio on the same server.
I am dealing with network files in the workflow, But I noticed the delay in 'Data flow' task. This dataflow task is loading around 5 Million Lookup data as pre execute..
If I am calling as a Job, Are the workflows come under SQL Server Memmory constraints? or Will it run as an outside program?..I am doubtful that SQL Server resource(memory,processor) constraints are making this delay.. Is any way to execute SSIS workflows as an outside program?
Regards
Agson Chellakudam
May 12, 2006 at 2:42 pm
Hmm... In this case, I'm not exactly sure why you have the variance in run times. I expected the lag to be on the network file access steps, but if it's on the data flow step, something else is probably at work here. Here's the next step. Run a trace and catch the cpu, disk io, and duration from both of the runs of your SSIS package. If they're both similar in time, it's gotta be some weird quirk of the SSIS engine or something.
As far as the rest of your questions, I'm not sure of the answers to these.
May 15, 2006 at 8:50 am
SQL Server memory constraints do not affect package execution. SSIS is subject to its own memory limitations: http://blogs.conchango.com/jamiethomson/archive/2005/05/29/1486.aspx
I don't know what you mean by "execute SSIS workflows as an outside program"
Are you quite sure that the delay is in populating the LOOKUP cache? If so, are you populating it with the same number of records when you execute in the 2 environments? [ N.B. The LOOKUP component fires an Information event stating how many rows are populated into the cache.]
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply