• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

Record Count In Execute SQL Task

I am trying to log counts of records inserted via a sql task. Can anyone tell me how best to do this. Right now I log counts in the data flow by using VB code in a Scrip Component, but I need to be able to capture counts in the SQL Task.
0
earngreen
Asked:
earngreen
1 Solution
 
tigin44Commented:
you may use the @@ROWCOUNT

your insert statement goes here

insert into ......
values(.......)

following the insert statement the @@ROWCOUNT returns the number of rows affected in that ddl operation.  by using that you may issue a command like

INSERT INTO yourLogTable
SELECT @@ROWCOUNT, GETDATE()... etc...

 


0
 
sameer2010Commented:
A word of caution.
@@ROWCOUNT would be reset for every instance of insert. So, you might have to save it for logging before/after commit.
0
 
earngreenAuthor Commented:
sameer2010

I am not sure what you mean.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
tigin44Commented:
@@ROWCOUNT value changes after every ddl statement. for that reason you should use it immediatly after you run your query or store that value for later use.. this what sameer2010 mean.
0
 
PedroCGDCommented:
Create a stored procedure with an output parameter using the @@RowCount function and get it from a SQLtask inside SSIS.
FOr SQL task details check this link:
http://www.sqlis.com/post/The-Execute-SQL-Task.aspx
helped?
Regards,
Pedro
0
 
earngreenAuthor Commented:
This worked great. Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now