0

A simple example would be a maintenance plan that clears a directory of old files and create some copy backups of all databases on that instance to that same directory. This is run daily

The first cleanup step contains the path where the files are deleted. The second step actually creates the copy backups in the same directory as the first step. So any changes made to the path have to be done in multiple places. For a simple 2 step maintenance plan this is not really a big issue but could be for more complicated plans.

So is there a way that plan steps can share information? I don't necessarily mean passing information between steps but perhaps a global space of the steps?

I could not see anything in the toolbox that would work for this. Best I got is that I could add two new steps.

  1. Create a temp table with the path (and any other constants.)
  2. Using that path delete directory contents
  3. Run copy-backups
  4. Delete temp table

I had thought this question: Can I pass data between stages in a maintenance task would have helped but it was focused on sending completion information in email.

Improve this question
4
  • Are you talking about using SQL Server maintenance plans to do file system maintenance and processing? That sounds like something that would be better handled by Powershell or even SSIS. – Kris Gruttemeyer Mar 17 '17 at 13:35
  • I could totally PowerShell this yes. My colleague is better versed with MPs so this would be something that he would be able to work with and understand faster. PowerShell is my thing though – Matt Mar 17 '17 at 13:37
  • While not specifically a Sql Server solution, one possibility would be to create windows 'share' names that reference the underlying directories and use the share name in the maintenance plans. If the directory information needs to change, just drop and recreate the 'share' name. – Scott Hodgin - Retired Mar 17 '17 at 14:02
  • Yes that could work as well. Tie it to cnames so that shares can move and the process need not be affected. – Matt Mar 17 '17 at 14:03
0

For global parameters we would use package configuration files, perhaps they would work for you as well: Package Configurations

Improve this answer
2
  • That would apply to SSIS correct? Not to maintenance plans? – Matt Mar 17 '17 at 13:53
  • 2
    Under the hood, maintenance plans are basically SSIS packages. – RDFozz Mar 17 '17 at 14:48

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.