Clean Up SMA Database After a Module Deletion

Background

I noticed an issue while I was writing a SMA integration module. I once made an mistake in the .json file and I noticed I couldn’t import the updated module back to SMA even after I firstly deleted the old version. I’ll explain this issue using a sample dummy module.

Reproducing The Issue

To reproduce the issue, I firstly created a dummy module with 3 files:

The module .psm1 and the connection file .json (notice there’s a connection field named “ComputerName” in the .json file):

I zipped and imported this module into SMA, everything looks good so far. and the connection fields are correctly displayed (as expected):

I then update the .json file (Changed the connection field name from ComputerName to ComputerFQDN):

I zipped the updated module, tried to import the module into SMA, but got an error:

I then tried to delete the existing module and imported again, but I got the same error. I also noticed that even after the module has been deleted, the connection is still available to be selected:

My Resolution

Since I couldn’t find any documentation on how to completely remove an Integration Module, I went ahead and developed a SQL script to completely remove the module and module connection from the various tables in the SMA database. Here’s the SQL script:

USE SMA
Declare @ModuleName Varchar(max)
Declare @ConnectionName Varchar(max)
Set @ModuleName = 'MyModule'
Set @ConnectionName = 'MyModuleConnection'

PRINT 'Deleting Connection Field values'
delete from Core.ConnectionFieldValues Where ConnectionKey in (Select ConnectionKey from Core.Connections Where ConnectionTypeKey in (Select ConnectionTypeKey from Core.ConnectionTypes Where Name = @ConnectionName))

PRINT 'Deleting Connections'
delete from Core.Connections Where ConnectionTypeKey in (Select ConnectionTypeKey from Core.ConnectionTypes Where Name = @ConnectionName)

Print 'Deleting Connection Fields'
delete from Core.ConnectionFields Where ConnectionTypeKey in (Select ConnectionTypeKey from Core.ConnectionTypes Where Name = @ConnectionName)

PRINT 'Deleting Connection Types'
delete from Core.ConnectionTypes where Name = @ConnectionName

PRINT 'Deleting Activity parameters'
delete from Core.ActivityParameters where ActivityParameterSetKey in (Select ActivityParameterSetKey from Core.ActivityParameterSets where ActivityKey in (Select ActivityKey from Core.Activities Where  ModuleVersionsKey in (Select ModuleVersionsKey From Core.ModuleVersions where  ModuleKey In (Select ModuleKey from Core.Modules Where ModuleName = @ModuleName))))

PRINT 'Deleting Core Activity parameter Sets'
delete from Core.ActivityParameterSets Where ActivityKey in (Select ActivityKey from Core.Activities Where  ModuleVersionsKey in (Select ModuleVersionsKey From Core.ModuleVersions where  ModuleKey In (Select ModuleKey from Core.Modules Where ModuleName = @ModuleName)))

PRINT 'Deleting Core Activities'
Delete from Core.Activities Where ModuleVersionsKey in (Select ModuleVersionsKey From Core.ModuleVersions where  ModuleKey In (Select ModuleKey from Core.Modules Where ModuleName = @ModuleName))

PRINT 'Deleting Job Modules'
delete from Core.JobModules Where ModuleVersionsKey in(Select ModuleVersionsKey from Core.ModuleVersions where ModuleKey in (Select ModuleKey from Core.Modules Where ModuleName = @ModuleName))

PRINT 'Deleting Module Version'
Delete from Core.ModuleVersions where ModuleKey In (Select ModuleKey from Core.Modules Where ModuleName = @ModuleName)

PRINT 'Deleting Modules'
Delete from core.Modules where ModuleName = @ModuleName

To use this script, you will need to change the @ModuleName and @ConnectionName variable to suit your module:

After I ran this script, I was able to import the updated module:

Disclaimer

Although I’ve used this for multiple modules in multiple SMA environments and so far have not found any problems, I did not consult this workaround with SMA experts, please use this at your own risk. Please don’t blame me if it breaks your environment.