March 13, 2013 at 6:58 am
Hi,
We have a huge process that we perform using SSIS, which is mostly just calling SP's using SQL task.
When we execute each SP on SSMS they execute in about 2-3 seconds. However, when the SQL task is executed it takes about 10-15 seconds thus increasing the total process time.
What could be the reason? Are we doing something wrong? There are about 31 Sps that get executed in all and the process time goes to around 5-6 minutes.
Please help!!
March 13, 2013 at 7:05 am
One possible work-around is to perform all of the stored procedure calls from a single umbrella procedure; i.e.,
create procedure Call_them_all
as
exec sProc1;
exec sProc2;
...
This way you don't need to establish a new connection for each stored procedure. Once you've done so with the main one it will be used for the others.
March 13, 2013 at 8:13 am
I agree with the work around. But the question still remains and that is, whether the SQL task is taking so much time only for the connection. Or is it something else?
March 13, 2013 at 12:53 pm
Most likely they're validating. Turn on Delay Validation.
Also, make sure you're using what's become a defacto standard for SSIS called procs:
SET NOCOUNT ON;
SET FMTONLY OFF;
WHILE 1=0
BEGIN
-- Result set for SSIS to use
SELECT
CONVERT(VARCHAR(30), NULL) AS ColumnA,
...
END
... Real code here.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 14, 2013 at 3:50 am
Evil Kraig F (3/13/2013)
Most likely they're validating. Turn on Delay Validation.Also, make sure you're using what's become a defacto standard for SSIS called procs:
SET NOCOUNT ON;
SET FMTONLY OFF;
WHILE 1=0
BEGIN
-- Result set for SSIS to use
SELECT
CONVERT(VARCHAR(30), NULL) AS ColumnA,
...
END
... Real code here.
...unless you're using 2012, in which case the result set definition moves to the Execute SQL task.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 15, 2013 at 1:33 pm
Phil Parkin (3/14/2013)
...unless you're using 2012, in which case the result set definition moves to the Execute SQL task.
Haven't gotten my hands on 2012 yet, but does that include OLEDB Data Sources using a Proc as a source as well?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 16, 2013 at 1:52 am
This is 2008 R2.
I have used delay_validation before but FMTONLY is new. Will try and get back.
Thanks you so much for all your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply