azure-devops-migration-tools: TF51005: The query references a field that does not exist. The error is caused by «[Custom.ReflectedWorkItemId]»

Anyway, I try to run the code but errors show up:

[16:43:59 INF] Processor: WorkItemMigration [16:43:59 INF] Migration Context Start: WorkItemMigration [16:43:59 INF] WorkItemMigrationContext::InternalExecute ... [16:44:00 INF] MigrationClient: Access granted to https://dev.azure.com/yyy/ for xxx (xxx@xxx.com) ... [16:44:03 INF] MigrationClient: Access granted to https://dev.azure.com/zzz/ for xxx (xxx@xxx.com) [16:44:04 INF] Migrating all Nodes before the Processor run. [16:44:05 WRN] The node \xxx\Iteration\Sprint 1 is being excluded due to your basePath setting. [16:44:06 INF] Querying items to be migrated: SELECT [System.Id], [System.Tags] FROM WorkItems WHERE [System.TeamProject] = @TeamProject AND [System.WorkItemType] NOT IN ('Test Suite', 'Test Plan') ORDER BY [System.ChangedDate] desc ... [16:44:13 INF] Replay all revisions of 20 work items? [16:44:13 INF] Found target project as test-han [16:44:13 INF] [FilterWorkItemsThatAlreadyExistInTarget] is enabled. Searching for work items that have already been migrated to the target... [16:44:13 ERR] Error running query Microsoft.TeamFoundation.WorkItemTracking.Client.ValidationException: TF51005: The query references a field that does not exist. The error is caused by «[Custom.ReflectedWorkItemId]». at Microsoft.TeamFoundation.WorkItemTracking.Client.Query.Initialize(WorkItemStore store, String wiql, IDictionary context, Int32[] ids, Int32[] revs, Boolean dayPrecision) at Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore.Query(String wiql, IDictionary context) at MigrationTools._EngineV1.Clients.TfsWorkItemQuery.GetWorkItemsFromQuery(TfsWorkItemMigrationClient wiClient) in D:\a\1\s\src\MigrationTools.Clients.AzureDevops.ObjectModel\_EngineV1\Clients\TfsWorkItemQuery.cs:line 40 [16:44:13 FTL] Error while running WorkItemMigration Microsoft.TeamFoundation.WorkItemTracking.Client.ValidationException: TF51005: The query references a field that does not exist. The error is caused by «[Custom.ReflectedWorkItemId]». at Microsoft.TeamFoundation.WorkItemTracking.Client.Query.Initialize(WorkItemStore store, String wiql, IDictionary context, Int32[] ids, Int32[] revs, Boolean dayPrecision) at Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore.Query(String wiql, IDictionary context) at MigrationTools._EngineV1.Clients.TfsWorkItemQuery.GetWorkItemsFromQuery(TfsWorkItemMigrationClient wiClient) in D:\a\1\s\src\MigrationTools.Clients.AzureDevops.ObjectModel\_EngineV1\Clients\TfsWorkItemQuery.cs:line 70 at MigrationTools._EngineV1.Clients.TfsWorkItemQuery.GetWorkItems() in D:\a\1\s\src\MigrationTools.Clients.AzureDevops.ObjectModel\_EngineV1\Clients\TfsWorkItemQuery.cs:line 30 at MigrationTools._EngineV1.Clients.TfsWorkItemMigrationClient.FilterExistingWorkItems(List 1 sourceWorkItems, TfsWiqlDefinition wiqlDefinition, TfsWorkItemMigrationClient sourceWorkItemMigrationClient) in D:\a\1\s\src\MigrationTools.Clients.AzureDevops.ObjectModel\_EngineV1\Clients\TfsWorkItemMigrationClient.cs:line 54 at VstsSyncMigrator.Engine.WorkItemMigrationContext.InternalExecute() in D:\a\1\s\src\VstsSyncMigrator.Core\Execution\MigrationContext\WorkItemMigrationContext.cs:line 120 at MigrationTools._EngineV1.Processors.MigrationProcessorBase.Execute() in D:\a\1\s\src\MigrationTools\_EngineV1\Processors\MigrationProcessorBase.cs:line 47 [16:44:13 ERR] WorkItemMigration The Processor MigrationEngine entered the failed state...stopping run[16:44:13 INF] Application is shutting down...

And here is my config: { “ChangeSetMappingFile”: null, “Source”: { “$type”: “TfsTeamProjectConfig”, “Collection”: “https://dev.azure.com/yyy/”, “Project”: “y01”, “ReflectedWorkItemIDFieldName”: “Custom.ReflectedWorkItemId”, “AllowCrossProjectLinking”: false, “AuthenticationMode”: “AccessToken”, “PersonalAccessToken”: “MY_TOKEN”, “LanguageMaps”: { “AreaPath”: “Area”, “IterationPath”: “Iteration” } }, “Target”: { “$type”: “TfsTeamProjectConfig”, “Collection”: “https://dev.azure.com/zzz/”, “Project”: “z01”, “ReflectedWorkItemIDFieldName”: “Custom.ReflectedWorkItemId”, “AllowCrossProjectLinking”: false, “AuthenticationMode”: “AccessToken”, “PersonalAccessToken”: “MY_TOKEN2”, “LanguageMaps”: { “AreaPath”: “Area”, “IterationPath”: “Iteration” } }, “GitRepoMapping”: null, “LogLevel”: “Information”, “Processors”: [ { “$type”: “WorkItemMigrationConfig”, “Enabled”: true, “ReplayRevisions”: false, “PrefixProjectToNodes”: false, “UpdateCreatedDate”: true, “UpdateCreatedBy”: true, “WIQLQueryBit”: “AND [System.WorkItemType] NOT IN (‘Test Suite’, ‘Test Plan’)”, “WIQLOrderBit”: “[System.ChangedDate] desc”, “LinkMigration”: false, “AttachmentMigration”: false, “AttachmentWorkingPath”: “c:\temp\WorkItemAttachmentWorkingFolder\”, “FixHtmlAttachmentLinks”: false, “SkipToFinalRevisedWorkItemType”: true, “WorkItemCreateRetryLimit”: 5, “FilterWorkItemsThatAlreadyExistInTarget”: true, “PauseAfterEachWorkItem”: false, “AttachmentMaxSize”: 480000000, “AttachRevisionHistory”: false, “LinkMigrationSaveEachAsAdded”: false, “GenerateMigrationComment”: false, “NodeStructureEnricherEnabled”: null, “NodeBasePaths”: [ “/” ], “WorkItemIDs”: null, “MaxRevisions”: 0 } ], “Version”: “11.11”, “workaroundForQuerySOAPBugEnabled”: false, “WorkItemTypeDefinition”: { “sourceWorkItemTypeName”: “targetWorkItemTypeName” }, “Endpoints”: { “InMemoryWorkItemEndpoints”: [ { “Name”: “Source”, “EndpointEnrichers”: null }, { “Name”: “Target”, “EndpointEnrichers”: null } ] } }

I try to run the query shown above with Wiql playground. And it says that “@TeamProject” is not a valid name

Please help !!!

_Originally posted by @frankShih in https://github.com/nkdAgility/azure-devops-migration-tools/discussions/1115#discussioncomment-2251656_

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 24 (7 by maintainers)

Most upvoted comments

Yes you will have to add it to all the work item types (except the ones you are excluding from the import) for the tool to work.

Yes you have added the field in correct place. It does not matter where on the form you add it. But I noticed you have named it “ReflectedItemId” so on the config file you will have to use this instead of ReflectedWorkItemId

Not sure I understand your other question.

Try replacing Config.ReflectedWorkItemId with Config.ReflectedItemId If it still does not work, try replacing Config.ReflectedItemId with just ReflectedItemId

mihirdilip on Mar 2, 2022

I used ReflectedWorkItemID as a custom field added to each of the work item types in the inherited process. To be clear, you could call the referenced field whatever you want (or use an unused field that is text). You would just need to update the config to match. I think it needs to be a text field for it to work correctly.

When I selected ReflectedWorkItemID and went to Edit (from a work item in my inherited process) you can see the full field reference name in ADO. This is what goes in your config file.

"ReflectedWorkItemIDFieldName": "Custom.(PutYourFieldNameHere)",

JFriedman09 on Mar 2, 2022

Here is my config that I use for similar scenario… Note that the WIQLQueryBit under WorkItemMigrationConfig should filter on what you would like to import. Also, you can either run all the processors in one go or enable and disable as you wish. Hope this helps.

{
  "ChangeSetMappingFile": null,
  "Source": {
    "$type": "TfsTeamProjectConfig",
    "Collection": "http://localhost:8080/tfs/Development/",
    "Project": "s",
    "ReflectedWorkItemIDFieldName": "TfsMigrationTool.ReflectedWorkItemId",
    "AllowCrossProjectLinking": true,
    "AuthenticationMode": "Prompt",
    "PersonalAccessToken": "",
    "LanguageMaps": {
      "AreaPath": "Area",
      "IterationPath": "Iteration"
    }
  },
  "Target": {
    "$type": "TfsTeamProjectConfig",
    "Collection": "https://dev.azure.com/TestOrg/",
    "Project": "s",
    "ReflectedWorkItemIDFieldName": "Custom.ReflectedWorkItemId",
    "AllowCrossProjectLinking": true,
    "AuthenticationMode": "Prompt",
    "PersonalAccessToken": "",
    "LanguageMaps": {
      "AreaPath": "Area",
      "IterationPath": "Iteration"
    }
  },
  "Endpoints": {
    "TfsWorkItemEndpoints": [
      {
        "$type": "TfsWorkItemEndpointOptions",
        "Name": "IterationSettingsSource",
        "Direction": "Source",
        "Organisation": "http://localhost:8080/tfs/Development/",
        "Project": "s",
        "AuthenticationMode": "Prompt",
        "ReflectedWorkItemIdField": "TfsMigrationTool.ReflectedWorkItemId",
        "LanguageMaps": {
          "$type": "TfsLanguageMapOptions",
          "AreaPath": "Area",
          "IterationPath": "Iteration"
        },
        "EndpointEnrichers": null,
        "Query": {
          "$type": "QueryOptions",
          "Query": "SELECT [System.Id], [System.Tags] FROM WorkItems WHERE [System.TeamProject] = @TeamProject AND [System.WorkItemType] NOT IN ('Test Suite', 'Test Plan') ORDER BY [System.ChangedDate] desc",
          "Paramiters": {
            "$type": "Dictionary`2",
            "TeamProject": "s"
          }
        }
      },
      {
        "$type": "TfsWorkItemEndpointOptions",
        "Name": "IterationSettingsTarget",
        "Direction": "Target",
        "Organisation": "https://dev.azure.com/TestOrg/",
        "Project": "s",
        "AuthenticationMode": "Prompt",
        "ReflectedWorkItemIdField": "Custom.ReflectedWorkItemId",
        "LanguageMaps": {
          "$type": "TfsLanguageMapOptions",
          "AreaPath": "Area",
          "IterationPath": "Iteration"
        },
        "EndpointEnrichers": null,
        "Query": {
          "$type": "QueryOptions",
          "Query": "SELECT [System.Id], [System.Tags] FROM WorkItems WHERE [System.TeamProject] = @TeamProject AND [System.WorkItemType] NOT IN ('Test Suite', 'Test Plan') ORDER BY [System.ChangedDate] desc",
          "Paramiters": {
            "$type": "Dictionary`2",
            "TeamProject": "s"
          }
        }
      }
    ],
    "TfsTeamSettingsEndpoints": [
      {
        "$type": "TfsTeamSettingsEndpointOptions",
        "Name": "TeamSettingsSource",
        "Direction": "Source",
        "Organisation": "http://localhost:8080/tfs/Development/",
        "Project": "s",
        "AuthenticationMode": "Prompt",
        "ReflectedWorkItemIdField": "TfsMigrationTool.ReflectedWorkItemId",
        "LanguageMaps": {
          "$type": "TfsLanguageMapOptions",
          "AreaPath": "Area",
          "IterationPath": "Iteration"
        },
        "EndpointEnrichers": null
      },
      {
        "$type": "TfsTeamSettingsEndpointOptions",
        "Name": "TeamSettingsTarget",
        "Direction": "Source",
        "Organisation": "https://dev.azure.com/TestOrg/",
        "Project": "s",
        "AuthenticationMode": "Prompt",
        "ReflectedWorkItemIdField": "Custom.ReflectedWorkItemId",
        "LanguageMaps": {
          "$type": "TfsLanguageMapOptions",
          "AreaPath": "Area",
          "IterationPath": "Iteration"
        },
        "EndpointEnrichers": null
      }
    ]
  },
  "FieldMaps": [
    {
      "$type": "FieldValueMapConfig",
      "WorkItemTypeName": "*",
      "sourceField": "System.State",
      "targetField": "System.State",
      "defaultValue": "New",
      "valueMapping": {
        "New": "New",
        "Approved": "New",
        "Specified": "New",
        "On Hold": "New",
        "Active": "Active",
        "Coding": "Active",
        "Ready to Build": "Resolved",
        "Ready to Test": "Resolved",
        "Verified Failed": "Active",
        "Verified": "Closed",
        "Released": "Closed",
        "Closed": "Closed",
        "Done": "Closed",
        "Removed": "Removed"        
      }
    }
  ],
  "GitRepoMapping": null,
  "LogLevel": "Information",
  "Processors": [
    {
      "$type": "TfsAreaAndIterationProcessorOptions",
      "Enabled": true,
      "PrefixProjectToNodes": false,
      "NodeBasePaths": [
        "RootAreaNode1",
        "RootAreaNode2",
        "SpecificAreaNode1\\SpecificChildNode1",
        "RootIterationNode1",
        "RootIterationNode2",
        "SpecificIterationNode1\\SpecificChildNode1"
      ],
      "ProcessorEnrichers": null,
      "SourceName": "IterationSettingsSource",
      "TargetName": "IterationSettingsTarget"            
    },
    {
      "$type": "TfsTeamSettingsProcessorOptions",
      "Enabled": true,
      "MigrateTeamSettings": true,
      "UpdateTeamSettings": true,
      "PrefixProjectToNodes": false,
      "Teams": [
        "Team1",
        "Team2"
      ],
      "ProcessorEnrichers": null,
      "SourceName": "TeamSettingsSource",
      "TargetName": "TeamSettingsTarget"
    },
    {
      "$type": "WorkItemMigrationConfig",
      "Enabled": true,
      "ReplayRevisions": true,
      "PrefixProjectToNodes": false,
      "UpdateCreatedDate": true,
      "UpdateCreatedBy": true,
      "LinkMigration": true,
      "AttachmentMigration": true,
      "AttachmentWorkingPath": "c:\\temp\\WorkItemAttachmentWorkingFolder\\",
      "FixHtmlAttachmentLinks": true,
      "SkipToFinalRevisedWorkItemType": true,
      "WorkItemCreateRetryLimit": 5,
      "FilterWorkItemsThatAlreadyExistInTarget": true,
      "PauseAfterEachWorkItem": false,
      "AttachmentMaxSize": 480000000,
      "AttachRevisionHistory": false,
      "LinkMigrationSaveEachAsAdded": false,
      "GenerateMigrationComment": false,
      "NodeStructureEnricherEnabled": null,
      "WIQLQueryBit": "AND [System.AreaPath] UNDER 'AreaYouWantToImport' AND [System.IterationPath] UNDER 'IterationYouWantToImport' AND [System.WorkItemType] NOT IN ('Test Suite', 'Test Plan', 'Test Case', 'Shared Parameter')",
      "NodeBasePaths": [
        "NOTHING TO IMPORT"
      ],
      "WorkItemIDs": null,
      "MaxRevisions": 0
    }
  ],
  "Version": "11.11",
  "workaroundForQuerySOAPBugEnabled": false
}

mihirdilip on Mar 3, 2022

Yes you will have to add it to all the work item types (except the ones you are excluding from the import) for the tool to work.

Yes you have added the field in correct place. It does not matter where on the form you add it. But I noticed you have named it “ReflectedItemId” so on the config file you will have to use this instead of ReflectedWorkItemId

Not sure I understand your other question.

Try replacing Config.ReflectedWorkItemId with Config.ReflectedItemId If it still does not work, try replacing Config.ReflectedItemId with just ReflectedItemId

You really save my day, man !!!

The root cause is just some kind of typo between config file “Config.ReflectedWorkItemId” and the custom field I set in all workitems “ReflectedItemId”.

Update my config file and the migration succeed!!!

frankShih on Mar 3, 2022

the primary error though as @MrHinsh pointed was that the field couldn’t be found, the query being shown in the output is just informational

GordonBeeming on Mar 1, 2022

@teamproject parameter isn’t a special macro, it is passed in as a parameter by the client library when executing the query. it’s from the days before we had macros from memory.

The new official current project macro (not parameter) is @project, see list of macros below https://docs.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops#macros-or-variables

In your case to mimic what the client library is doing just replace @teamproject with the project name or in removing the team part should switch it to using the macro.

GordonBeeming on Mar 1, 2022