Пакетное удаление проектов DQS

http://social.technet.microsoft.com/wiki/contents/articles/14065.delete-dqs-projects-created-from-running-ssis-dqs-cleansing-activities.aspx#Description

Automated TSQL Script Approach

In the case where there are too many projects to manually delete, you may use the TSQL script described here to automate the cleanup for a certain date range.

1. The script uses a date and time range to target the project deletion to a scope of time.
2. The script bulk deletes Data Quality projects according to the ‘Type’ flag which is set to 3 by default (1 = KB, 2 = Cleansing project DQS Client, 3 = SSIS Project)
3. The script deletes project in both Locked and Unlocked states.
4.  If a project fails to delete, the script continues to delete the remaining projects.
5.  Printed text output shows progress of the deletions and any errors which occur.

Instructions

1. The Windows account executing the TSQL script should have a ‘dqs_administrator’ role; we recommend the account to have a sysadmin role on the box.
2. Run the script from SQL Server Management Studio while connected to SQL Server instance running the DQS instance (hosting the DQS_MAIN and DQS_PROJECTS databases)
3. Modify the ‘FromDate’ and ‘ToDate’ dates and times  in the script to define the window for cleansing up the projects

TSQL Code

SET NOCOUNT ON

USE DQS_MAIN

  

DECLARE @FromDate datetime

DECLARE @ToDate datetime

DECLARE @ProjectId bigint

DECLARE @LockClientId bigint

DECLARE @DqProject varbinary(max)

DECLARE @ResultRecords varbinary(max)

        ,@ErrMessage VARCHAR(max)

        ,@rowcount INT

        ,@errCount INT = 0

  

--Update From date and To date here before execution of script

SELECT @FromDate = CAST('2012-10-19 00:00:01.001' AS datetime)

SELECT @ToDate = CAST('2012-10-19 23:59:59.997' AS datetime)

  

PRINT '***************************************************************'

PRINT CAST(GETDATE() AS VARCHAR(MAX)) + ' :: ' + 'Executing script for date range ' + CAST(@FromDate AS VARCHAR(MAX)) +' to ' +  CAST(@ToDate AS VARCHAR(MAX))

  

DECLARE DELETE_PROJECTS_CURSOR CURSOR FOR

        SELECT [ID], ISNULL([LOCK_CLIENT_ID],-1)

        FROM [DQS_Main].[dbo].[A_KNOWLEDGEBASE]

        WHERE [TYPE] = 3 -- BatchDQProject, projects that are generated by SSIS packages

        AND [CREATE_DATE] BETWEEN @FromDate AND @ToDate

      

OPEN DELETE_PROJECTS_CURSOR

      

FETCH NEXT FROM DELETE_PROJECTS_CURSOR

INTO @ProjectId, @LockClientId

  

WHILE @@FETCH_STATUS = 0

BEGIN

    BEGIN TRY

        PRINT CAST(GETDATE() AS VARCHAR(MAX)) + ' :: ' + 'Operating on Project: [' + CAST(@ProjectId AS VARCHAR(MAX)) +']'

        EXECUTE [KnowledgebaseManagement].[SetDataQualitySession] @clientId=@LockClientId, @knowledgebaseId=NULL

  

        IF (@LockClientId != -1)

        BEGIN

            EXECUTE [KnowledgebaseManagement].[DQProjectGetById] @ProjectId,@DqProject OUTPUT

            EXECUTE [KnowledgebaseManagement].[DQProjectExit] @DqProject,@ResultRecords OUTPUT

        END

  

        -- delete project's activity archive

        DELETE FROM [dbo].[A_PROFILING_ACTIVITY_ARCHIVE]

        WHERE [ACTIVITY_ID] IN (SELECT ID FROM [dbo].[A_KNOWLEDGEBASE_ACTIVITY] WHERE [KNOWLEDGEBASE_ID] = @ProjectId)

  

        -- refresh the project state

        EXECUTE [KnowledgebaseManagement].[DQProjectGetById] @ProjectId,@DqProject OUTPUT

        PRINT CAST(GETDATE() AS VARCHAR(MAX)) + ' :: ' + 'Deleting project: [' + CAST(@ProjectId AS VARCHAR(MAX)) +']'

        EXECUTE [KnowledgebaseManagement].[DQProjectDelete] @DqProject

        PRINT CAST(GETDATE() AS VARCHAR(MAX)) + ' :: ' + 'Deleted project: [' + CAST(@ProjectId AS VARCHAR(MAX)) +']'

    END TRY

    BEGIN CATCH

        PRINT CAST(GETDATE() AS VARCHAR(MAX)) + ' :: ' + 'An error has occurred with the following details'

        PRINT CAST(GETDATE() AS VARCHAR(MAX)) + ' :: ' + 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +

              ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +

              ', State ' + CONVERT(varchar(5), ERROR_STATE()) +

              ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +

              ', Line ' + CONVERT(varchar(5), ERROR_LINE());

        PRINT CAST(GETDATE() AS VARCHAR(MAX)) + ' :: ' + 'Error Message: ' + ERROR_MESSAGE();

        SELECT @errCount = @errCount + 1

        PRINT CAST(GETDATE() AS VARCHAR(MAX)) + ' :: ' + 'Skipping this project because of errors'

    END CATCH                 

    FETCH NEXT FROM DELETE_PROJECTS_CURSOR INTO @ProjectId, @LockClientId

END

IF @errCount > 0

    PRINT CAST(GETDATE() AS VARCHAR(MAX)) + ' :: ' + 'Script completed with ' + CAST(@errCount AS VARCHAR(MAX)) + 'errors'

ELSE

    PRINT CAST(GETDATE() AS VARCHAR(MAX)) + ' :: ' + 'Script completed successfully'

PRINT '***************************************************************'

  

BEGIN TRY

    CLOSE DELETE_PROJECTS_CURSOR

    DEALLOCATE DELETE_PROJECTS_CURSOR

END TRY

BEGIN CATCH

    --Do nothing

END CATCH