HOWTO: Identify source container mapped to various target links of the same type (4301133)

Issue
Sometimes one source container might be mapped by an accident to multiple targets of the same type, like multiple EV targets. If not many mappings are affected, this operation can be done manually. If more (several hundred or thousands) mappings are affected following guidance using SQL query will help to sort it out. The SQL script below assigns all duplicate mappings to the TARGET Tag (source Tag cannot be used as source containers are equal to each other).
How to do it
In the Archive Shuttle user interface, go to tag management and create a new tag. Always create a new tag, rather than using an existing one. Here’s how to do it:

1. Go to SQL and locate the tag ID in the Container tag table.
2. Replace the tag ID in the SQL script later in this article. The tag ID is set to 100 in the script now, replace that with your tag.
3. Run the SQL script below.
4. In Archive Shuttle, go to Stage1 and add column Target, then add column Tag.
5. Set the filter in this Target Tag column to the tag you created and put into the SQL
   script.
6. All redundant mappings where one source and multiple targets exist will be assigned to your tag. Tags are replacing groups in future versions.

Examples

• If only one source exists for one target, nothing is executed.
• If none of the duplicates are completed in stage2 (assuming 4 duplicates were found), 1 stays and 3 will be assigned to your tag.
• If one or more of duplicates finished stage2 (assuming 4 duplicates were found), 1 finished stays and the rest finished or not will go to your tag.

Now you should be able to filter duplicates and do whatever you want using the TARGET tag (not the SOURCE tag), the recommendation is just to disable Migration and Item collection not deletion on all affected mappings.
SQL to use

--change tag for dups (TARGET tag)
insert into [ArchiveShuttleDirectory].[dbo].[ContainerToContainerTag] (ContainerId, ContainerTagId)
select c.targetcont, 100 from --100 replace by your tag id
(select a.sourcecontainerid as sourcecont, a.targetcontainerid as targetcont, a.stage2finished as stage2finished, b.cntTotal as cntTotal,
ROW_NUMBER() OVER(PARTITION by a.sourcecontainerid ORDER BY a.stage2finished DESC) cntObso
from [ArchiveShuttleDirectory].[dbo].[Stage1View] a
left outer join
(SELECT sourcecontainerid, count(targetcontainerid) as cntTotal
FROM [ArchiveShuttleDirectory].[dbo].[Stage1View]
group by sourcecontainerid
having count(targetcontainerid) > 1) b ON a.sourcecontainerid = b.sourcecontainerid
where b.cntTotal > 1) c
where c.cntObso > 1