How to migrate Asset Status to Business Workflow tasks

Remember to take a backup first, and test with Test before you touch Prod!

The below SQL script can be configured to migrate asset status to business workflow. The script is intelligent enough not to touch assets that already have an asset status, so you will not end up with duplicate tasks from trying out the system.

To configure the script, look at the parameters under “Set these parameters”:

“instanceOwnerId” should be the id of the user that will end up owning these tasks, usually something like “System” since this is asset lifecycle.
“businessWorkflowId” should be the id of the business workflow to migrate to.
Lastly, the values being inserted table should map from the optionValue of the given asset status to the unique id of the stage in the business workflow. The stage names will NOT be validated, so make sure they are correct, or you risk breaking the entire system. The last flag is if this task should be considered “retired”. The value should probably correspond to the value you defined for the stage in the business workflow, otherwise, it might lead to confusion.

Once that is done, you simply run the script. When it is done running, you should be able to see the new tasks in the MM UI.

Some notes:

The script takes the stage assignee from the current asset status “owner”. If no “owner” is specified, then the task is left unassigned.
The title of the tasks is assigned to the English title for the asset. If the asset doesn’t have a title, then assetcode (original file name) will be used.
The script is safe to run multiple times since it will skip assets that already have a task for the specified business workflow.
If something fails when the script runs, all changes are undone, and the system is left as it was before the script was run.

Remember to take a backup first, and test with Test before you touch Prod!

declare @statusMapping table(statusValue nvarchar(max), stageUniqueId nvarchar(max), retired bit);

----- Set these parameters ------

declare @instanceOwnerId int = 10000;
declare @businessWorkflowId int = 2;


insert into @statusMapping(statusValue, stageUniqueId, retired)
values ('#54a0ff;creative', 'creative', 0),
       ('#1dd1a1;approved', 'approved', 0),
       ('#ee5253;expired', 'expired', 1);


----- Script, do not touch ------

declare @assetStatusLabelId int;
select @assetStatusLabelId = iml.item_metafield_labelid from item_metafield_label iml
join item_metafield im on iml.item_metafieldid = im.item_metafieldid
join item_item_metafield iim on im.item_metafieldid = iim.item_metafieldid
join item i on i.itemid = iim.itemid
where ItemGuid = '6fe35f10-c810-497b-af9c-e52c3d583593'
and iml.languageid = 3

declare @assetTitleLabelId int;
select @assetTitleLabelId = iml.item_metafield_labelid from item_metafield_label iml
join item_metafield im on iml.item_metafieldid = im.item_metafieldid
join item_item_metafield iim on im.item_metafieldid = iim.item_metafieldid
join item i on i.itemid = iim.itemid
where ItemGuid = '5eb3eefc-a043-410f-89b0-29ed3ef37078'
and iml.languageid = 3

declare @assetStatusOwnerLabelId int;
select @assetStatusOwnerLabelId = iml.item_metafield_labelid from item_metafield_label iml
join item_metafield im on iml.item_metafieldid = im.item_metafieldid
join item_item_metafield iim on im.item_metafieldid = iim.item_metafieldid
join item i on i.itemid = iim.itemid
where ItemGuid = 'fc948200-c103-41fb-8dae-bd1c5c6ce281'
and iml.languageid = 3

declare @workflowVersionId int;
select @workflowVersionId = versions.Id from BusinessWorkflowService_WorkflowDefinitionVersions versions
where versions.WorkflowDefinitionId = @businessWorkflowId;


declare @missingAssets table(itemId int, assetId int);
insert into @missingAssets(itemId, assetId)
select ia.itemid, ia.assetid 
from item_asset ia
join asset a on a.assetid = ia.assetid
join asset_damcatalog_folder adf on adf.assetid = ia.assetid
where a.deleted = 0 and
not exists (
	select items.ItemId from BusinessWorkflowService_WorkflowInstanceItems items
	join BusinessWorkflowService_WorkflowProjectInstances instances on instances.Id = items.WorkflowInstanceId
		where instances.WorkflowDefinitionVersionId = @workflowVersionId
		and items.ItemId = ia.itemid
);

declare @assets table (assetId int, itemId int, uniqueStageId nvarchar(max), title nvarchar(max), retired bit, createdAt datetime, statusUpdatedAt datetime, stageOwnerId int)

insert into @assets(assetId, itemId, uniqueStageId, title, retired, createdAt, statusUpdatedAt, stageOwnerId)
select a.assetid, ia.itemid, map.stageUniqueId, a.assetcode, map.retired, a.upload_date, imv_status.DateModified, im.memberid 
from @missingAssets missingAsset
join asset a on a.assetid = missingAsset.assetId
join item_asset ia on a.assetid = ia.assetid
join item i on ia.itemid = i.itemid
join item_metafield_value imv_status on i.itemid = imv_status.itemid
join item_combo_value icv on imv_status.item_metafield_labelid = icv.item_metafield_labelid and imv_status.item_combo_valueid = icv.item_combo_valueid
join @statusMapping map on map.statusValue = icv.optionvalue
left join item_metafield_value imv_title on i.itemid = imv_title.itemid and imv_title.item_metafield_labelid = @assetTitleLabelId
left join item_metafield_value imv_owner on i.itemid = imv_owner.itemid and imv_owner.item_metafield_labelid = @assetStatusOwnerLabelId
left join item_member im on im.itemid = imv_owner.ref_itemid
where imv_status.item_metafield_labelid = @assetStatusLabelId;

begin transaction;

declare @createdOwners table(memberId int, ownerId int);

insert into BusinessWorkflowService_Owners(Discriminator, MemberOwner_MemberId)
output inserted.MemberOwner_MemberId, inserted.Id into @createdOwners
select distinct 'MemberOwner', a.stageOwnerId from @assets a
where a.stageOwnerId is not null

insert into BusinessWorkflowService_Owners(Discriminator, MemberOwner_MemberId)
values ('MemberOwner', @instanceOwnerId)

declare @instanceOwnerOwnerId int = scope_identity();

declare @createdTasks table(assetId int, itemId int, taskId int);
merge BusinessWorkflowService_WorkflowProjectInstances as target
using @assets as src
    left join @createdOwners owner on src.stageOwnerId = owner.memberId
on (1 = 0)
when not matched then
    insert (WorkflowDefinitionVersionId, CurrentStageName, CreatedAt, Guid, InstanceOwnerId, LastModified, StageOwnerId, Retired, Name, CurrentStageEnteredAt)
    values (@workflowVersionId, src.uniqueStageId, src.createdAt, newid(), @instanceOwnerOwnerId, src.statusUpdatedAt, owner.ownerId, src.retired, src.title, src.statusUpdatedAt)
output src.assetId, src.itemId, inserted.Id into @createdTasks(assetId, itemId, taskId);

insert into BusinessWorkflowService_WorkflowInstanceItems(WorkflowInstanceId, ItemType, ItemBaseId, ItemId)
select t.taskId, 'asset', t.assetId, t.itemId from @createdTasks t;

commit transaction;

Remember to take a backup first, and test with Test before you touch Prod!