I am attempting to migrate work items from our old ticket tracking system into VSTS using the Excel integration feature (from Team Explorer, right-click on Work Items folder and select "Add Work Items with Microsoft Excel"). I have exported the work item data from the old ticket tracking system and converted values to match those expected by VSTS and can paste the data into Excel, matching the data to the proper columns (all cols added from Team menu).

The problem is that when I attempt to publish the work items to VSTS, I encounter many field validation problems stemming from the fact that these are not "new" work items. For example, the State of most of the work items I would like to migrate is actually "Closed" but the only value allowed for State by Excel/VSTS is "Active". I also would like to make a couple fields editable that are currently read-only (Created By and Created Date).

I note that I can turn off the validation warnings within Excel itself, but the publication process still enforces the data validation. So, what I'm looking for is a way to disable or adjust the data validation that is applied during the publication of new work items from Excel to VSTS. After successful migration, I will reinstate the original validation.

Is there a way? Or should I give up on Excel and code a custom import using the work item object model? Any advice greatly appreciated.

Hello,

Can you confirm if you are retaining the IDs of the work items? It might be better to just copy the work items into a new list object so that they get new ids. Then using Excel's bulk editing capability, it should be fairly simple to set values for all required fields.

The reason you get those validation errors is because of the states and transitions defined in the Process template on which the team project is based. You can modify your work item type definition to change the rules on new item creation. Do try this out in a test environment before implementing it in your production environment.

The Created By and Created Date are system defined fields for when and who created a particular work item. Can you explain why you want to make those fields editable? What kind of data do you want to store in there?

Hope this helps!

Thank you for the reply!

Yogita Manghnani wrote:

Can you confirm if you are retaining the IDs of the work items? It might be better to just copy the work items into a new list object so that they get new ids. Then using Excel's bulk editing capability, it should be fairly simple to set values for all required fields.

No, I am not retaining IDs. The ID column in Excel is blank and I am doing nothing to change that, so the work items will get new IDs when they are published to VSTS. There is no need to use bulk editing, since all values are exactly the way I want them. It is just that many of those values are not considered "valid" for "new" work items. For example, the State of many existing work items is "Closed" but they will only successfully publish to VSTS if I change them to "Active".

Yogita Manghnani wrote:

The reason you get those validation errors is because of the states and transitions defined in the Process template on which the team project is based. You can modify your work item type definition to change the rules on new item creation. Do try this out in a test environment before implementing it in your production environment.

Yes, I am working in a test environment. I have tried to update the process template used by the (test environment) team project, but I do not see where it is restricting the value of State to only allow it to be "Active" for new work items. I thought it would be part of the workflow settings, but here is the workflow transition for a new work item in the template (I do not see any mention of State here)...

Valid transistions are specified in <Transistions> element and as you see the only valid transistion with "from" state ="" is the first one which allows it to go to Active. Setting the state as Closed for new item means changing change from "" to "Closed", and it is not a valid transistion and hence it fails.

You could add a transistion from "" to "Closed" to workaround it, and also relax rules in all other fields temporarily, but it is not possible to enter random values on fields like Changed by, changed date etc. Only option to enter invalid values (as per current rules) into work item is to programmatically enter them directly using webservice with rulevalidation turned off. See http://blogs.msdn.com/amitgh/archive/2006/08/20/709737.aspx on how to do it, and as you see it involves extensive coding against webservice and our webservices do not have much documentation.

For your scenario, it seems you could relax rules except the ones where you cannot relax such as ChangedDate to enter data from other db and then add the rules before users enter data. We are investigating creating a migration toolkit to solve these kinds of problems.