calling stored procedure from ssis

Question

calling stored procedure from ssis

sachinrshetty

SSCrazy

Points: 2062
More actions
June 3, 2010 at 6:34 am

#235893

ALTER PROCEDURE card_import
AS
BEGIN
SET NOCOUNT ON;
DECLARE CUR CURSOR
FOR
SELECT CARDID, BUSN_NM FROM ##TEMP_CARD
DECLARE @ZCID NVARCHAR(50), @ZBNM VARCHAR(250)
OPEN CUR
FETCH NEXT FROM CUR INTO @ZCID, @ZBNM
WHILE(@@FETCH_STATUS = 0)
BEGIN
UPDATE Card SET businessunit_name = @ZBNM WHERE Card.CardID = @ZCID
FETCH NEXT FROM CUR INTO @ZCID, @ZBNM
END
CLOSE CUR
DEALLOCATE CUR
DROP TABLE ##TEMP_CARD
END
I have called the above stored procedure from a execute sql task in ssis but the package is not running.
what's the mistake that i have done.
sachin

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 1

Can you please confirm what you mean by "the package is not running"?

The the SSIS task execute and then fail or execute reporting success but not actually perform the operations in the procedure?

www.sqlAssociates.co.uk

sachinrshetty SSCrazy Points: 2062 More actions · Answer 2

sachinrshetty

SSCrazy

Points: 2062

June 3, 2010 at 7:08 am

#1175919

ssis task execute and then fails

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 3

Can you post the failure message?

www.sqlAssociates.co.uk

sachinrshetty SSCrazy Points: 2062 More actions · Answer 4

Sorry no messages it just turns out to red colour. 🙂

Chris Kitchen Hall of Fame Points: 3090 More actions · Answer 5

You'll need to turn on logging in SSIS, this will provide important information as to the issue. I assume you've taken the syntax and executed it successfully in a query?

www.sqlAssociates.co.uk

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 6

sachinrshetty (6/3/2010)
ssis task execute and then fails

I think its because of the Temp Table... Where are you creating the Temp table.I just see a Drop statement in your SQL code. If any other task is using the create statement to create the Temp table.. you need to change the properties of the Temp table connection Manger , set the RetainSameConnection Property to TRUE and Delay validation property of the Execute SQL task to TRUE

OR

Just Remove the DROP statement and run the Execute SQL task.. Let me know how it works, without any error message its hard to tell what's going wrong with the Task.

WayneS SSC Guru Points: 95460 More actions · Answer 7

I'd suggest replacing that c.u.r.s.o.r with:

UPDATE c

SET c.businessunit_name = t.BUSN_NM

FROM Card c

JOIN ##TEMP_CARD t

ON c.CardID = t.CARDID

You don't need the DROP TABLE statement - as soon as the last connection using the global temp table goes out of scope (ends), it will drop itself.

A possibility for the error: I believe that SSIS first runs the execute cmd with "SET FMTONLY ON" set, in order to retrieve the metadata information. If the temp table isn't built, it fails. I've seen places where we've need to put at the beginning of the proc "SET FMTONLY OFF", so that the query will always run. The drawback is that the procedure will be run twice - once when getting the metadata, and once to return the results.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sachinrshetty SSCrazy Points: 2062 More actions · Answer 8

Hi all,

I found the answer in some blog and here it is

"My answer was always "Set RetainSameConnection=TRUE on the connection manager and set DelayValidation=TRUE on all tasks that use the temp table. Setting RetainSameConnection=TRUE ensures that the temp table does not disappear when the task that creates it has completed. Setting DelayValidation=TRUE ensures that the tasks do not try to check that the table exists before it is created."

Thank u very much guys for helping me out. Above solution works if anybody using temporary table in ssis.

Thanks a lot u all guys.

Sachin

Phil Parkin SSC Guru Points: 246985 More actions · Answer 9

sachinrshetty (6/3/2010)
Hi all,
I found the answer in some blog and here it is
"My answer was always "Set RetainSameConnection=TRUE on the connection manager and set DelayValidation=TRUE on all tasks that use the temp table. Setting RetainSameConnection=TRUE ensures that the temp table does not disappear when the task that creates it has completed. Setting DelayValidation=TRUE ensures that the tasks do not try to check that the table exists before it is created."
Thank u very much guys for helping me out. Above solution works if anybody using temporary table in ssis.
Thanks a lot u all guys.
Sachin

That looks the same as what Divyanth suggested.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 10

Probably my explanation wasn't as good as BLOG's post.. 🙂