@@rowcount mapping to global variable

• Stuartb113

  SSC-Addicted

  Points: 440

  More actions

  March 31, 2008 at 4:18 am

  #121359

  Hi

  I have an Exec SQL Task that runs a simple insert statement but is it possible to capture the @@rowcount value and to map the resulting value to a variable?...i've tried but have not been successful 🙁

  In this instance i'd rather not use a stored proc or a row count transformation.

  thanks in advance..

• john.arnott

  SSChampion

  Points: 11882

  More actions

  April 1, 2008 at 3:53 pm

  #796910

  Stuart, I'm not sure where your problem is, but had no trouble using the @@rowcount. I set up my Exec SQL task with direct input and followed the insert statement with a SELECT @@rowcount. I used a couple of test tables to have the insert copy 2 of 5 rows from table Play001 to table Play002.

  insert into Play002

  select * from Play001 where TestText like 'B%'

  select @@rowcount

  Then, I set the task as having one row in the result set and assigned the output ordinal 0 to my int32 SSIS variable "Mycount". A subsequent script task was able to read it and include its value in a msgbox:

  MsgBox("Count is " + CStr(Dts.Variables("User::Mycount").Value))

• shamshudheen

  SSCrazy

  Points: 2390

  More actions

  April 2, 2008 at 5:17 am

  #797102

  Stuart (3/31/2008)

  ---

  Hi

  I have an Exec SQL Task that runs a simple insert statement but is it possible to capture the @@rowcount value and to map the resulting value to a variable?...i've tried but have not been successful 🙁

  In this instance i'd rather not use a stored proc or a row count transformation.

  thanks in advance..

  stuart

  it is possible to map the @@rowcount to a variable

  see the example

  DECLARE @Row_Count

  INSERT INTO order

  SELECT * FROM purchase_order

  WHERE orderdt = GETDATE()

  SET @Row_Count = @@rowcount

  PRINT @Row_Count

  you have select all and run in one execution

  hope it will help:)

  thanks

  shamsudheen