Running BBDW USPs in BBCRM using the Global Change Spec

By: Alan Eager

Posted on: July 3, 2017

We can use the feature of a CLR Global Change Spec to connect to BBDW and run a SQL Stored Procedure.  This will appear in the Global Change screen in the Admin Functional Area.  One such example is to use a USP to call the BBDW.RESET_ETL USP.

The two Global Change tasks listed deal with Running the ETL Reset USP and running a custom SQL Agent Job both in the BBDW SQL instance and database.

The Spec Code will call a VB CLR and is as follows:

 <GlobalChangeSpec
    xmlns="bb_appfx_globalchange"
    xmlns:c="bb_appfx_commontypes"
    ID="63462369-B1C7-4778-8957-AA325A1FAC44"
    Name="BBDW Load SISK File Schedule Job"
    Description="BBDW Load SISK File Schedule Job"
    Author="Blackbaud Professional Services"
    DisplayName="BBDW Load SISK Files"
    GlobalChangeFolder="BBDW"
    >
    <CLRGlobalChange AssemblyName="Blackbaud.CustomFx.CRMAudit" ClassName="Blackbaud.CustomFx.CRMAudit.BBDWLoadSISKGlobalChange" />

</GlobalChangeSpec> 

VB Code:

You can download the VB Code here

Global Change Spec to call SQL Agent Job

You can do a similar thing to call the msdb.do.sp_start_job which is the SQL Agent Stored Procedure to run a SQL Agent Job.

 <GlobalChangeSpec
    xmlns="bb_appfx_globalchange"
    xmlns:c="bb_appfx_commontypes"
    ID="1CDBEE54-9B77-4C29-B07B-C20EC8FF5AE2"
    Name="BBDW Run SQL Agent Job"
    Description="Ability to run and schedule a BBDW SQL Agent Job"
    Author="Blackbaud Professional Services"
    DisplayName="BBDW Run SQL Agent Job"
    GlobalChangeFolder="BBDW"
    >
    <CLRGlobalChange AssemblyName="Blackbaud.CustomFx.BBDW.Catalog.Utils" ClassName="Blackbaud.CustomFx.BBDW.Catalog.Utils.BBDWRunSQLAgentJobGlobalChange" />

  <ParametersFormMetaData>
    <c:FormMetaData>
      <c:FormFields>
        <c:FormField FieldID="SQLAGENTJOBNAME" DataType="String" Caption="SQL Agent Job Name" MaxLength="255" 
         DefaultValueText="BBETL_SSIS_BBCRM_Prod_CUSTOM_External">
      </c:FormField>
      </c:FormFields>
    </c:FormMetaData>
  </ParametersFormMetaData>
</GlobalChangeSpec>

VB Code:

You can download the VB Code here

This means you can setup a SQL Agent Job that performs a scheduled task where it can be maintained and managed in CRM

This shows the Global Change spec being displayed in the Global Change screen which when edited prompts you for the name of the SQL Agent Job.  This will then run the SQL Agent Job when processed from the Global Change screen or called using the CRM Scheduler to add your “Global Change” task you just created – in this case BBETL_SSIS_BBCRM_Prod_CUSTOM_External.

Queue Screen

In the Queue screen in the Administration functional area, Add a new Queue item (Click on the Add button).

 

Then you can Schedule your Global Change process in Queue

 

Leave a Reply

Privacy Policy | Sitemap | © 2011 Blackbaud, Inc. All Rights Reserved

Digital Ocean