I have a Maintenance plan that I wish to execute nightly. The purpose is to set an archived bit against records that are more than 60 days old. This bit all works fine, however I'd like to log (and email an operator) about how many records were affected.

I have a stored proc that does all the heavy lifting, and it is defined in an execute T-SQL task with an out parameter that contains the number of records affected.)

So how do I pass that value to a notify task, or log it?

  • You should consider fixing typos, missing or uneeded letters in your question and its title. – Julien Vavasseur Nov 9 '15 at 13:05
  • What about logging and notiying directly in T-SQL? Is that an option or do you really need to do it in SSIS? – spaghettidba Nov 9 '15 at 13:49
  • I can look at doing that. (Never done this before, so its all new for me, hence the question. Its easier to get guidance and do it right 1st time than have to unlearn and redo stuff later on :-) – Matt Nov 9 '15 at 14:37
  • Critical note: SQL Server maintenance plans (from SQL 2005 on) are SSIS packages built by the system. – RDFozz Jan 11 '19 at 21:01

Using an SSIS expression on the Notify Operator task properties for the message subject or body, you can construct whatever you want from variables written earlier.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.