Requested Change: Blackbaud to alter the script BBETL.bat (Windows Batch File) that resides in the c$\Program Files\Microsoft SQL Server\120\DTS\Packages\BBETL\CRM_RPT_BBDW folder to not destroy_snapshot (CRM_SNAPSHOT_FOR_ETL) which execute CALL :sqlcmd_snapshot_exists which in turn executes the script DropSnapshot. This script drops the snapshot at the end of a successful or failed Data Warehouse load and is also invoked at the beginning of a Warehouse load in case the snapshot was still in existence for some reason. These scripts get re-built every time you execute the Blackbaud Data Warehouse deployment wizard when service packs are deployed consequently modifications to the BBETL by the Foundation to support extensions are then over-written.

Why a Change: The FSU Foundation has in excess of 45 extension packages that are listed in the BBDW_PackageList_EXT text file that execute in the load run stream after the Blackbaud tables are loaded. Some of these SSIS packages formerly referenced CRM as a data source and now reference the snapshot to avoid deadlocks. The deadlock issue appears to be the underlying issue that required Blackbaud to add the snapshot feature to the warehouse load process.

When performing maintenance activities on these packages it is necessary to repoint them to CRM during maintenance/development activities and then repoint them to the snapshot when they are deployed to production. This cumbersome workflow is required since the snapshot does not exist (persist) between warehouse loads. This workflow is prone to errors. It is our (FSU Foundation’s) contention that Blackbaud should take into account these maintenance activities on extension packages and leave the snapshot in place between data warehouse loads. The snapshot should of course be dropped and rebuilt on each successive load. Another advantage is that when chasing data consistency/quality issues it is useful to have a snapshot of CRM in place that reflects the source for the current Data Warehouse data load at a consistent point in time.

Change Specifications: The lines of code in the script BBETL highlighted bold italics below are believed to be the only required script changes. Basically the behavior is that if successful the snapshot will not be destroyed but will be replaced upon the next scheduled warehouse load. If the load is unsuccessful the snapshot will be dropped.
-- BBLETL.bat --
:start_etl
ECHO :start_etl
SET BBETL_CRM_RPT_BBDW=C:\Program Files\Microsoft SQL Server\120\DTS\Packages\BBETL\CRM_RPT_BBDW\BBDW_ETL_CONFIG.dtsConfig
SET BBETL_WRITEBACK_CRM_RPT_BBDW=C:\Program Files\Microsoft SQL Server\120\DTS\Packages\BBETL\CRM_RPT_BBDW\BBDW_ETL_WRITEBACK_CONFIG.dtsConfig
ECHO "C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTExec.exe" /FILE "C:\Program Files\Microsoft SQL Server\120\DTS\Packages\BBETL\CRM_RPT_BBDW\BBDW_ETL.dtsx" /CONFIGFILE "C:\Program Files\Microsoft SQL Server\120\DTS\Packages\BBETL\CRM_RPT_BBDW\BBDW_ETL_CONFIG.dtsConfig" /CONFIGFILE "C:\Program Files\Microsoft SQL Server\120\DTS\Packages\BBETL\CRM_RPT_BBDW\BBDW_ETL_WRITEBACK_CONFIG.dtsConfig" /Reporting EWPI 
"C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTExec.exe" /FILE "C:\Program Files\Microsoft SQL Server\120\DTS\Packages\BBETL\CRM_RPT_BBDW\BBDW_ETL.dtsx" /CONFIGFILE "C:\Program Files\Microsoft SQL Server\120\DTS\Packages\BBETL\CRM_RPT_BBDW\BBDW_ETL_CONFIG.dtsConfig" /CONFIGFILE "C:\Program Files\Microsoft SQL Server\120\DTS\Packages\BBETL\CRM_RPT_BBDW\BBDW_ETL_WRITEBACK_CONFIG.dtsConfig" /Reporting EWPI 

IF %ERRORLEVEL% EQU 0 GOTO DONE
REM IF %ERRORLEVEL% NEQ 0 GOTO abort

REM GOTO destroy_snapshot

:abort
ECHO :abort
REM Save the error level so we can return it later
SET SAVED_ERRORLEVEL=%ERRORLEVEL%

CALL :sqlcmd_snapshot_exists
IF %ERRORLEVEL% NEQ 0 GOTO destroy_snapshot
REM If the snapshot doesn't exist, then we're done
GOTO done

:destroy_snapshot
ECHO :destroy_snapshot
CALL :sqlcmd_destroy_snapshot
IF %ERRORLEVEL% NEQ 0 GOTO retry_destroy
REM Verify the snapshot was actually destroyed
CALL :sqlcmd_snapshot_exists
IF %ERRORLEVEL% NEQ 0 GOTO retry_destroy
GOTO done

:retry_destroy
ECHO :retry_destroy
CALL :pause_n_seconds 60
CALL :sqlcmd_destroy_snapshot
IF %ERRORLEVEL% NEQ 0 GOTO destroy_failed
REM Verify the snapshot was actually destroyed
CALL :sqlcmd_snapshot_exists
IF %ERRORLEVEL% NEQ 0 GOTO destroy_failed
GOTO done

:destroy_failed
ECHO :destroy_failed
ECHO "Unable to destroy the snapshot."
REM We'll exit with the ERRORLEVEL from the failed attempt to destroy
EXIT %ERRORLEVEL%

:done
ECHO :done
ECHO ****ETL FINISH****