CRM 2011: trigger a plugin from a stored procedure

No, it’s not possible, you’re dreaming. Or is it? Well, sort of. What’s possible is the following:

  1. I have created an entity called “new_pluginlauncher”. I have made a plugin firef upon the creation of a record of this entity, from which  I can do anything, depending on the parameters passed when creating the record. I can do things such as win or reopen a quote, create an order, close a case, well whatever really. As long as the correct guids and parameters are set upon record creation.
  2. I have created an SSIS package, based on Kingswaysoft,  that does that: create a record of that new_pluginlauncher entity
  3. I have deployed the SSIS package appropriately in the SQL Server instance
  4. I have created a stored procedure that calls that SSIS package

Bingo! From now on, I can access plugin code, on demand, from a SQL Server stored procedure.

And the best thing is: this is completely supported.

 

Here’s the code of the stored procedure. Pay attention to allowing xp_cmdshell.

ALTER Proc [dbo].[proc_CreatePluginLauncher]
@entitytype varchar(20),
@actiontype varchar(20),
@recordid varchar(50)
as
begin
declare @command varchar(8000),
@packagelocation varchar(1000),
@packagename varchar(200),
@param varchar(2000)
set @packagelocation = ‘SSISDBCRMCreateCRMPluginLauncher’
set @packagename = ‘CreatePluginLauncher.dtsx’
set @param = ‘select ‘ +
@entitytype + ‘ as EntityType, ‘ +
@actiontype + ‘ as ActionType, ‘ +
@recordid + ‘ as RecordGuid’
set @command =
‘dtexec /ISSERVER ‘ + @packagelocation + @packagename + ‘ ‘ +
‘/set package.variables[User::SQLCommand].Value;””‘ + @param + ‘”” ‘
—-now execute dynamic SQL by using EXEC.
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @command
select @returncode
end

Leave a Reply

Your email address will not be published. Required fields are marked *