Tuesday, June 11, 2013

BCS BDC external data lookup field migration

Scenario
  • You want to programmatically set an External Data lookup field
    • Ex: Migrating data into a SharePoint list or library
Issue
  • In this case, we used Idera / Metalogix Migration Manager, which does not support External Data fields, nor do they plan on supporting it in the near future
    • This is likely due to the fact that they offer a client only (non-server deployed solution) and haven't figured out how to do this using the remote content deployment APIs
  • The result was that the external data field only displayed correctly in the View Item form and did not populate in the Edit item form or any of the related/projected fields in the view.
Resolution
  • Luckily I did have access to deploy a server solution, so I developed the following web part to handle the migration
    • If you are using Office 365 or don't have this access then you will need to develop your own solution or need to use a data synchronization solution instead (several vendors make these)
  • In order for this solution to work, you need to add a temporary column to the list that will store the IDs of the field you wish to migrate.  Note that this is not necessarily the display name.
    • In our case we migrated the ID column to a list column named SourceId, ran the tool, then deleted the SourceId column.
  • I utilized code from StackExchange and Jaspers' Weblog along with some forum posts, converting all to VB.Net for aesthetics
  • Download the code
    • The wsp solution file is located in the \SetExternalDataWebPart\bin\Release folder
    • You must enable the feature at the site collection level and add the web part to a page in the subsite that contains the list. 
    • In my test environment this tool updated 25 records every 10 seconds.
  • Notes
    • I found that in addition to setting the display name you also need to set the RelatedField to the BDCIdentity to get the correct value to display in the edit form and to be updateable via the refresh icon.
// Set the BCS field itself (Display Value)
listItem[dataField.Id] = dtBDCData.Rows[0][dataField.BdcFieldName].ToString();
// Set the related field to the BDC Identity
listItem[dataField.RelatedField] = dtBDCData.Rows[0]["BdcIdentity"].ToString();
  • Some other tips:
    • I had to use the RevertToSelf (BDC Identity) connection to get the refresh to function
    • I did not need to run with elevated or allowunsafeupdates except when debugging permissions.
  • Updated release info:
    • 6/27/2013
      • Added list ID range support to split copies into smaller chunks
        • Uses an SPQuery on ID field.
      • Added refresh interval for regular mid-copy status updates
        • These are actually just auto post backs using an asp.net timer control
    • 7/5/2013
      • Fixed refresh bug

18 comments:

  1. This is a great solution and I know the frustration that Idera does not support this. I downloaded the project and installed. I do have a question, is there a particular reason that the source ID column has to be an int? We have a few bcs fields where the ID is alphanumeric. Before I went trying to change the project I was wondering if there was maybe a specific reason this is not possible.

    -Michael-

    ReplyDelete
  2. That should be a fairly straightforward change. And there's no reason other than the client only needed an integer column migrated. :)

    I believe the code you should change is here:
    ElseIf field.Type = SPFieldType.Integer Or field.Type = SPFieldType.Number Then

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  3. Robert, I have deployment 101 question: we have deployed the wsp but nothing appears in the drop downs, do we need to hardcode values in the code as you seem to suggest in your note above?

    ReplyDelete
  4. Have you tinkered with a CSOM solution to migrating and updating a External Data column?

    ReplyDelete
  5. Hi Robert,

    I have to migrate Sharepoint 2007 list from one site to another. Sites are in different servers. I have user Sharepoint service to retrive the list items and

    migrated all the values except "Business Data". Any special thing we have to do?

    Thanks

    ReplyDelete
  6. Hi Robert - I have deployed the wsp and activated the feature at the site collection level. When trying to add the web part to a page in the subsite, however, I'm unable to find it in the gallery in the ribbon. What is the web part called? Also, what issues would you foresee with deploying this into a 2013 environment?

    Thanks!

    ReplyDelete
    Replies
    1. It should be under the "Custom" group. Named "SetExternalData".

      I don't see any problem with implementing this in 2013, you'll just need to open the solution in Visual Studio 2013.

      Delete
    2. Thanks Robert! I did get that sorted out... I have it running but am having to debug.

      Now I'm getting the following error when I hit line "entInstance = entity.FindSpecific(id, lsinstance)" in the "Get methods collection" block: "Failed trying to write value '14939' with Type 'System.Int32' as an Identifier of Type 'System.String' for Entity (External Content Type) with Name 'External Employee Data' in Namespace". I'm not sure what it's trying to do...

      Delete
    3. Robert - disregard that last reply...I figured out that it was another spot where type is integer whereas my value is a string. Thanks - I got it working!! :)

      Delete
    4. Robert - I got the web part working well in our test environment, but when I deployed to production I am now getting this error: System.InvalidOperationException: Sequence contains no matching element
      at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source, Func`2 predicate)
      at SetExternalDataWebPart.SetExternalDataUserControl.GetEntityInstance(String ns, String entityName, String entityId)

      I think it's happening when it is iterating through the secondary fields. Have you seen this error before? Thanks!

      Delete
  7. Hi Robert,

    I downloaded and installed your web part without any issues on SharePoint 2013. I would like to change the the source id to a text value (SP Text column) in the code as the external ID column we use is navchar (alphanumeric value) but ran into Framework errors when editing the web part in Visual Studio 2010 on a SharePoint 2013 server. I am unable to import/open the web part in Visual Studio 2013/2015. Have you updated this web part for SharePoint 2013/ Visual Studio 2015 and if so can I get a copy? Many thanks, George.

    ReplyDelete
  8. Is this file able to be downloaded still? I'm having difficulty learning C# and building it myself. But I've come across a scenario where I need this in SharePoint 2013 also.

    ReplyDelete
    Replies
    1. Thanks for the comment. Dropbox changed their public links. It should work now.

      Delete
    2. Thanks, I was able to download it. Sorry for such a beginner question, It's my first time cracking open VS. If I attempt to open the solution in VS 2015, I get an error "Incompatible" and it doesn't list what assets are missing from my fresh install.

      I'm just trying to do that switch from Integer to String for the Source Lookup. I'm sorry if you're really busy. but can you assist me?

      Delete
    3. Well I figured the resources I was missing and added them. now I'm just trying to get other elements installed and figure out how to tweak it. But if you have the string version available, it would be greatly appreciated.

      Delete
    4. Sorry, I never created a version other than the one provided. You could ask the user above, but it's unlikely he kept his code.

      Delete