In developing my data warehouse ETL processes, there are some packages and SQL Agent jobs that are both necessary to the process of deploying a new version of the ETL system - and yet are extremely dangerous.  These packages "reset" certain control infomation to a known state, which is a destructive operation I want to make sure I think about twice before executing - especially in a QA or production environment.  If I executed this kind of package in development on my local machine, I could use Script Tasks to pop up MessageBoxes to ask for this kind of confirmation.?

Are You Sure?

Of course, you can't do that when you're executing packages on a server!  There are also use cases beyond initial data loads.  You may allow your SSIS packages to be callable by a wide range of people or systems... but you may want some oversight or confirmation before those packages are actually run.  Here's one way to balance convenience of access and execution with some protection against unintended execution.
The Design
My basic thought process in finding an alternative to "confirmation" message boxes was:

• I can't have a popup... how else can the server talk to me?
• Ah - it can send me an email.  Bonus - it doesn't matter who kicks off the job, I can hardcode an email address in there so I get the message, or I can look up an email in a "responsibility" table so the email gets sent to a designated "process owner."
• Now how do I talk back to the server?  I can't reply to the email - it doesn't have a mailbox.
• Ah - it can watch a share for a file to appear that contains a confirmation to continue.
• So how do I make sure that I don't confuse the package, inadvertently acknowledging confirmation for "request #2" when I intended to confirm "request #1"?  You know how that happens every once in a while - you're editing a document, and some other program "pops up" to ask you a question... but you don't stop typing in time, and your keystrokes end up selecting something and dismissing the dialog!
• Ah - I can require the file contents to be something... special... and unique to that package execution.  Some kind of code that would be unlikely to mistype unless I spent some serious brain power on it.  Like a GUID.
• But I don't want to have to navigate to the share, create a new text file, and hand-transcribe a GUID.  I'll probably type it in wrong and have to restart the process again.  (Faulty humans!)
• Ah - I can attach the file to the email, so all I have to do is save it in the required location.

Yeah - OK already.  So it takes me a while to figure this stuff out...
The Implementation
I'd put up a sample package - but I'm too lazy busy to strip out the sensitive and custom stuff from one of my packages, so I'll just describe it.
Task 1: Add a Share
Find a location on your network and create a share that you give permission to both you (as the package execution confirmer) as well as the account you're going to be executing the SSIS package under.
Task 2: Script - Generate the Confirmation File Contents
Add an SSIS variable called "Confirmation File Name" that contains the UNC path (not driver letter path) to where you want the confirmation file placed.
Pass that variable into the Script Task, along with the MachineName and ExecutionInstanceGUID system variables.  I've chosen the ExecutionInstanceGUID as my "confirmation code" - you can choose something else entirely: a passphrase, or another GUID generated by Guid.NewGuid.  (It's more secure if you generate another GUID rather than use the execution instance - someone could guess your implementation and subvert the email step if they have access to the sysssislog table.)
Paste in code somewhat like this:

System.IO.StreamWriter confirmationFile
 
    (string)Dts.Variables["User::ConfirmationFilePathname"].Value);
confirmationFile.WriteLine(
  Dts.Variables["System::ExecutionInstanceGUID"].Value);
confirmationFile.Close();
Dts.TaskResult = (int)ScriptResults.Success;= System.IO.File.CreateText(

As you'll see in a bit, I've built the file in the exact spot that I intend to look for it... we'll take care of that.
Task 3: Send the Confirmation
Slap a Send Mail Task down, set it up to send you an email, and attach the file using the ConfirmationFilePathname variable.
Task 4: Delete the Confirmation File
As you pointed out earlier, if I "waited" for you to place that file in the location we want, it wouldn't work - because the file is already there!  So I need to delete it, making sure that I've got a clean spot for you to place your confirmation.
(You could have created the file in a separate location so this isn't an issue - but deleting it was simpler to set up.)
Task 5: Wait... and Watch
You can use a Script to do this entirely - I happen to have used my File Properties Task instead.  It's up to you, but in this step, you'll be waiting for the file to reappear.  How long you wait, and how often you check is also up to you - but I suggest you don't wait indefinitely.  Have your package fail if a certain timeout is reached.  If you use a Script to watch for the file, you'll be using System.IO.File.Exists and System.Threading.Thread.Sleep to do that.
Task 6: Read the File
If you receive your email, decide to confirm the action, and save it to the right share, your package will arrive at this step.  It's now got to open the file and check the contents with another Script Task to make sure you're responding to the proper request.  Here's one with an appropriate level of error handling:

bool fireAgain = true;
string confirmationFileName =
  (string)Dts.Variables["User::ConfirmationFilePathname"].Value;
Dts.TaskResult = (int)ScriptResults.Failure;string contents = "";
try
{
  System.IO.StreamReader confirmationFile;
  confirmationFile = System.IO.File.OpenText(confirmationFileName);
  Dts.Events.FireInformation(0, "",
    "Confirmation file opened.",
    "", 0, ref fireAgain);
  contents = confirmationFile.ReadLine();
  Dts.Events.FireInformation(0, "",
    "Confirmation file contents read.",
    "", 0, ref fireAgain);
  confirmationFile.Close();
}
catch (Exception ex)
{
  Dts.Events.FireError(0, "",
    "Unable to open '" + confirmationFileName + "' to "
    + "check contents: " + ex.Message,
    "", 0);
}

if (contents == (string)Dts.Variables["System::ExecutionInstanceGUID"].Value)
{
  Dts.Events.FireInformation(0, "",
    "Confirmation file contents validated.",
    "", 0, ref fireAgain);
  Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
  Dts.Events.FireError(0, "",
    "Confirmation file contents failed to "
    + "match expected code.", "", 0);
}

If the confirmation file contains the right code, it will return success, otherwise it will fail with an appropriate message.
Task 7+: Do Your Dirty Work
Your package just got the green light to trash the place (or do whatever critical action was requested)!