To get count of data processed in an SQL job

Question

To get count of data processed in an SQL job

setmohit

Grasshopper

Points: 19
More actions
January 29, 2009 at 7:58 am

#218113

Hi SQK gurus
I have written a several SSIS package and running them as job daily thru SQL server agent and have got email notification attached to it.
I want to add in the email notification the number of records each SISS package processed and how to save the log files also of these jobs.
Will appreciate any help on this.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

To get the number of rows affected is easy. You create an integer variable at the package level for each DataFlow task that you have. Then you add a RowCount transformation to assign the number of rows to a variable you associate with that DataFlow task. Then in your SendMail task, set the messagesource using an expression that includes all of the RowCounts.

What is it you need to do with your log files again?

Russel Loski, MCSE Business Intelligence, Data Platform

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 2

You could also add a script task with an execute stored proc in it that records the counts and inserts to your log table.

setmohit Grasshopper Points: 19 More actions · Answer 3

Thx , I want to store every day log files in location in server with timesamp attached to it.

Thx

Russel Loski SSCertifiable Points: 7059 More actions · Answer 4

Are you talking about the log file generated by SSIS or a log file that is the data source in your dataflow?

Russel Loski, MCSE Business Intelligence, Data Platform

setmohit Grasshopper Points: 19 More actions · Answer 5

Hi Thx , sorry can u pls explain detail , see when I see my log files of the job run it say X number record inserted or X number of records updated what I want to do is just get thing in email , creating a log table will be a new log log table or some system table in which I have to put.

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 6

If u want to log to a table and save the history then, u will need to have the infrastructure in place.

1. The logging table(s)

2. The stored procedure that logs into this table

But if u only want an email just read in the variables, build a message body of it using Expressions and send it in the email.