This topic describes the use of pre- and post-scripts with dSources that are created from SQL Server source databases.

Pre-scripts and post-scripts are Windows PowerShell code executed on the Staging Target host before and after a SnapSync of a dSource. You can specify pre- and post-scripts in the wizard for creating a dSource, or you can modify them afterwards by navigating to the Configuration > Standard tab. You can also set pre- and post-scripts using the Delphix command line interface (CLI) or REST Web API.

The Delphix Engine executes a pre-script on the Staging Target host prior to the SnapSync of a dSource. If it is an initial snapshot, or manual snapshot by selecting the snapshot button on the GUI, the pre/post scripts do not get executed for dSources. Since the dSource resides within a restoring database in the SQL instance on the Staging Target host, the script can perform queries on the instance if a database account is available. 

Hooks do allow for a pre/post snapshot hook, but the pre/post scripts do not

A post-script is executed after the SnapSync on a dSource completes. If the post-script fails, the provision, refresh, or rewind operation will also fail with an error message, and a fault will be created on the dSource.

Pre- and Post-scripts can be associated with a dSource in one of two ways:

During the dSource linking process

1. Login to the Delphix Admin application using delphix_admin credentials or another account with admin privileges.
2. In the top menu bar, click Manage.
3. Select Add dSource.
4. In the third panel of the Link dSource wizard, entitled Data Management, go to the bottom of the panel and click Advanced. 
   Fields for Pre Script and Post Script will appear.
   
5. Enter the calling syntax of the Windows PowerShell script into either or both of the appropriate fields.
   1. The calling environment is the used during linking, as shown in the Environment Details panel of the Delphix environment (Manage > Environments)
   2. Four (4) environment variables will be populated with the name of the Delphix dSource, the SQL Server instance name and port, and the SQL Server database name.

After linking, using the Configuration tab of the Datasets details page  

1. Login to the Delphix Admin application using delphix_admin credentials or another account with admin privileges.
2. In the top menu bar, click Manage.
3. Select My Datasets to display the SQL Server dSources and VDBs.
4. Select a SQL Server dSource from the listed Datasets in the left-hand navigation bar.
5. Click the Configuration panel.
6. In the Data Management column:
   
   1. Click the pencil icon  that appears to the right of Pre Script or Post Script attribute.
      
   2.  Enter the calling syntax of the Windows Powershell script into either  or both of the appropriate fields
      
      1. The calling environment is that of the primary Environment User account, as shown in the Environment Details panel of the Delphix environment (Manage > Environments)
      2. Four (4) environment variables will be populated with the name of the VDB, the SQL Server instance name and port, and the SQL Server database name.
   3. Click the check mark icon  to accept the change.

If a pre-script or post-script encounters an unrecoverable error during execution, the Delphix Engine expects the script to return with a non-zero exit code. Otherwise, the error will not be detected.

PowerShell gives you a few ways to handle errors in your scripts:

• Set $ ErrorActionPreference. This only applies to PowerShell Cmdlets. For scripts or other executables such as sqlcmd, PowerShell will return with exit code 0 even if there is an error, regardless of the value of  $ErrorActionPreference.  The allowable values for $ErrorActionPreference are:

  • Continue (default) – Continue even if there is an error
  • SilentlyContinue – Acts like Continue with the exception that errors are not displayed
  • Inquire – Prompts the user in case of error

  • Stop : Stops execution after the first error

• Use exception handling by using traps and try/catch blocks to detect errors and return with non-zero exit codes

• Use custom error handling that can be invoked after launching each command in the script  to correctly detect errors. The following example shows how you can use the function verifySuccess to detect whether the previous command failed, and if it did print, print an error message and return with an exit code of 1.

  function die {
      Write-Error "Error: $($args[0])"
      exit 1
  }
  
  function verifySuccess {
      if (!$?) {
          die "$($args[0])"
      }
  }
  
  Write-Output "I'd rather be in Hawaii"
  verifySuccess "WRITE_OUTPUT_FAILED"
  
  & C:\Program Files\Delphix\scripts\myscript.ps1
  verifySuccess "MY_SCRIPT_FAILED"