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
optionValueof 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 no task will be created.
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 @existingTasks table(itemId int INDEX IX1 CLUSTERED);
insert into @existingTasks(itemId)
select items.ItemId from BusinessWorkflowService_WorkflowInstanceItems items
join BusinessWorkflowService_WorkflowProjectInstances instances on instances.Id = items.WorkflowInstanceId
where instances.WorkflowDefinitionVersionId = @workflowVersionId;
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, imv_title.value, map.retired, a.upload_date, imv_status.DateModified, im.memberid from asset a
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
join item_metafield_value imv_title on i.itemid = imv_title.itemid
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
and imv_title.item_metafield_labelid = @assetTitleLabelId
and not exists(select t.itemid from @existingTasks t where t.itemId = ia.itemid)
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!