July 31, 2009 at 10:57 am
Is it possible to set the number of records a stored procedure containing an INSERT statement inserts in to a table in to a user variable in ssis please? There are no resultsets for insert statements so I can't use that functionality within an execute sql task. I want to do a send mail task to email how many records were inserted by the the stored procedure. Didn't want to do this via logging really as that would be too much detail.
July 31, 2009 at 11:17 am
The only way that I know to do it would be to modify the stored procedure to either 1) use the SET ROWCOUNT command (deprecated, I think), or 2) use the TOP(..) operator on the INSERT..SELECT, or 3) use the ROW_NUMBER() function in the INSERT..SELECT statement.
(3) is probably the most preferred solution, but slightly more difficult than the others.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 31, 2009 at 11:34 am
Add an output parameter to the stored proc, assign it the value of @@Rowcount after the insert statement, and grab the value of the output in SSIS. SSIS's Execute SQL action allows it to get values from output parameters. That'll do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 31, 2009 at 5:50 pm
oops, I think I misread the question. :blush:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 3, 2009 at 11:00 am
That's worked like a dream thanks!!! Brilliant!!!
August 3, 2009 at 2:35 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 4, 2009 at 8:06 am
Quick question...if I have three separate insert statements within the same stored procedure does the @@rowcount get re-set after each insert? If so, could I set three output parameters and set them to @@rowcount after each insert to make the number of records added by each of the inserts visible to an external query please?
August 4, 2009 at 8:40 am
I think that should be the best approach!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply