logging output from Execute T-SQL task

Question

logging output from Execute T-SQL task

bobd125

SSC Eights!

Points: 901
More actions
January 10, 2013 at 1:30 pm

#147806

I have a Execute T-SQL task which purged records from table. I would like to record in a log file the record count of how many records where deleted. Is this done by turning logging on?

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

bobd125 (1/10/2013)
I have a Execute T-SQL task which purged records from table. I would like to record in a log file the record count of how many records where deleted. Is this done by turning logging on?

Your ExecuteSQL task needs to return the count, which you can map to a package variable.

The way in which you do this depends on the exact SQL you have written, but @@rowcount might be your friend.

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Here is a tutorial on how you might be able to capture the number of affected rows into a variable, depending on how your SQL Statement in your Execute SQL Task is constructed.

http://microsoft-ssis.blogspot.com/2011/03/rowcount-for-execute-sql-statement.html

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

bobd125 (1/10/2013)
I have a Execute T-SQL task which purged records from table. I would like to record in a log file the record count of how many records where deleted. Is this done by turning logging on?

Not sure why you're using SSIS for such a simple thing. Just write a stored procedure and have it capture the rowcount(s) in a log table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Jeff Moden (1/13/2013)
bobd125 (1/10/2013)
I have a Execute T-SQL task which purged records from table. I would like to record in a log file the record count of how many records where deleted. Is this done by turning logging on?
Not sure why you're using SSIS for such a simple thing. Just write a stored procedure and have it capture the rowcount(s) in a log table.

That would seem to require a decision to shift where the logging took place since the original question talked about a log file. If you wanted the log data to a table, but later bring the information out to a file so it could be emailed (typical case) then I guess that could be a compromise.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato