Revisions Spec: Reset

An important first action for revisions is to reset ETL. You can create a stored procedure called RESETETL_EXT to reset extensions.The following element, contained within a revisions spec, contains embedded Transact-SQL. The deployment utility runs the Transact-SQL to create a procedure called RESETETL_EXT if it does not exist. For more information about the CREATE PROCEDURE statement, see Microsoft's MSDN article at CREATE PROCEDURE (Transact-SQL).

  <DBRevision ID="5">
    <ExecuteSql>
      <![CDATA[	
if not exists(select * from sysobjects so where so.type = 'P' and so.name = 'RESETETL_EXT')
	exec sp_executesql N'create procedure BBDW.[RESETETL_EXT] as set nocount on;'
	]]>
    </ExecuteSql>
  </DBRevision>

Note: There is no need to alter the above code snippet. But, when you add this revision or any other revision like it to your revisions file, ensure the procedure name is preceded by the schema (BBDW).

After revisions create the stored procedure, you add lines to the procedure to perform a series of table truncations and to execute stored procedures to drop indexes. RESETETL_EXT is also executed by RESETETL (if RESETETL_EXT exists). You can use RESETETL as a guide for RESETETL_EXT. The following lines of code are from RESETETL.

truncate table BBDW.[FACT_CONSTITUENTSMARTFIELD];  
exec BBDW.[CREATE_OR_DROP_FACT_CONSTITUENTSMARTFIELD_INDICES] 0;

To update the RESET_ETL stored procedure as you extend the database, you can add revisions that alter the stored procedure. Added lines for truncations and create/drop indexes call other stored procedures. To ensure the additions are repeatable, add the stored procedures called by these lines through a revision. Examples for revisions that add truncations and create/drop indexes are included in this document.