Report rowcount to SQL AGENT from Execute SQL task in SSIS package

1
votes

I have a number of SSIS packages that run net change import processes from one database to another. At the destination server data is inserted into a temp table by a data flow task, and then I run a very simple set of net change insert/update/delete queries in an Execute SQL task.

I'd like to report the number of rows inserted, deleted and updated back to SQL agent - specifically I'd like these counts to show up in the "view history" interface in SSMS for any jobs running this SSIS package.

I feel like this should be incredibly simple (and it's no doubt going to turn out to be), but I can't work out how to do it.

I'm running SQL Server 2008 R2

1
sql-server-2008ssis
Have you thought about intentionally storing this data instead of leaving it in the SQL Agent job log? It's a common practice in my mind to keep track of package executions, their duration and the total number of rows that went through the process (original count and then recounted as modified, new, unchanged) – billinkc
Thanks. We do log to table for a lot of tasks, we also log to file in other cases, but in this case it's just an eyeball check I'm after, and it would be helpful to have that in the job history interface, since it's a quick and easy interface. – DanBennett

1 Answers

0
votes

It's working with CmdExec and T-SQL job type. Give it a try for SSIS job type.

In Execute SQL task, store count of updated/inserted/deleted rows. Use PRINT statements in the task. Then go to New Job => Steps => New => under New Job Step click Advanced pane, and check the "Include step output in history" checkbox.