locked
Has anyone found a solution to import data from BDC into person type fields in Sharepoint 2010 user profiles? RRS feed

  • Question

  • We have setup user profiles OK and are syncing infomation from AD into user profiles. We have also setup an BDC connection to our SQL based HR system. We want to import two fields into user profiles, Department and manager. While department works fine we can not import the manager into profiles as the field type is person.

    Question has anyone found a solution to import data from BDC into person type fields in Sharepoint 2010 user profiles?

    Any help would be most approcated?

    Andrew
    Wednesday, February 23, 2011 2:08 PM

Answers

  • Yes I have and I have blogged it.

    http://www.sharepointfabian.com/blog/Lists/Posts/Post.aspx?ID=181

    Storyline

    So if you have ended up here then you are searching like nothings business looking for resources on how to use SharePoint Business Connectivity Services (BCS) to Extend User Profile Services to enhance the Social Experience of the End User, or you saw a Tweet to come see it J That said, what are we going to be doing? Here is our problem and solution.

    Problem Stated

    In our company we use Active Directory to as our Source of Record (SOR), we also use a ERP System (call it Siebel, CRM, SAP… doesn't matter) to store other related data to our employees, vendors, clients, etc. We want to capture information from that ERP system to include into our MySite and also make this information searchable, tag-able, off-line able, viewable/consumable in the Browser UX and Fat Clients. How do we do that?

    Resolution

    The process we will undertake will follow the chronological process below; but before we get there, we have a few Assumptions of the Farm [these assumptions are based on what should already be configured on the Farm as in mine]

    • Assumptions
      • User Profile Service has been started
      • User Profile Synchronization Service has been started
      • Synchronization Connections are present from Active Directory (AD)
      • Profile Synchronization has occurred from the AD Source
      • MySites are provisioned
    • Process
      • Create an External Content Type in either SharePoint Designer 2010 or Visual Studio 2010 representative of the ERP system data
        • Set the permissions accordingly so that the data is available to the system
        • Create a Profile Page for Search
      • Create a Managed User Property field which maps an Identifier in both AD & the ERP System
      • Create a Synchronization Connection for the ERP System
      • Create as many Managed User Property fields which maps to elements that you want to bring in from the ERP System
      • Determine how you want those fields to be consumed in the Profile Store, MySites, Searchability, etc.
      • Perform a Full User Profile Synchronization
        • Review the process using the "Synchronization Service Manager" in the 14 hive – basically a FIM tool
      • Review your User Profile
      • Review your MySite

    Connect with Me!

    Fabian G. Williams | Twitter @FabianWilliams | Blog http://www.sharepointfabian.com | Email: fabian@adotob.com

    Adotob Logo

    Saturday, February 26, 2011 6:14 AM

All replies

  • Yes I have and I have blogged it.

    http://www.sharepointfabian.com/blog/Lists/Posts/Post.aspx?ID=181

    Storyline

    So if you have ended up here then you are searching like nothings business looking for resources on how to use SharePoint Business Connectivity Services (BCS) to Extend User Profile Services to enhance the Social Experience of the End User, or you saw a Tweet to come see it J That said, what are we going to be doing? Here is our problem and solution.

    Problem Stated

    In our company we use Active Directory to as our Source of Record (SOR), we also use a ERP System (call it Siebel, CRM, SAP… doesn't matter) to store other related data to our employees, vendors, clients, etc. We want to capture information from that ERP system to include into our MySite and also make this information searchable, tag-able, off-line able, viewable/consumable in the Browser UX and Fat Clients. How do we do that?

    Resolution

    The process we will undertake will follow the chronological process below; but before we get there, we have a few Assumptions of the Farm [these assumptions are based on what should already be configured on the Farm as in mine]

    • Assumptions
      • User Profile Service has been started
      • User Profile Synchronization Service has been started
      • Synchronization Connections are present from Active Directory (AD)
      • Profile Synchronization has occurred from the AD Source
      • MySites are provisioned
    • Process
      • Create an External Content Type in either SharePoint Designer 2010 or Visual Studio 2010 representative of the ERP system data
        • Set the permissions accordingly so that the data is available to the system
        • Create a Profile Page for Search
      • Create a Managed User Property field which maps an Identifier in both AD & the ERP System
      • Create a Synchronization Connection for the ERP System
      • Create as many Managed User Property fields which maps to elements that you want to bring in from the ERP System
      • Determine how you want those fields to be consumed in the Profile Store, MySites, Searchability, etc.
      • Perform a Full User Profile Synchronization
        • Review the process using the "Synchronization Service Manager" in the 14 hive – basically a FIM tool
      • Review your User Profile
      • Review your MySite

    Connect with Me!

    Fabian G. Williams | Twitter @FabianWilliams | Blog http://www.sharepointfabian.com | Email: fabian@adotob.com

    Adotob Logo

    Saturday, February 26, 2011 6:14 AM
  • Hi Fabian,

     

    Many thanks for the reply. However the problem is that using BDC to import fields into user profiles only works for fields of type string. Therefore if you want to use BDC to import into the user profile field "Manager" it does not allow you as this field is not a type "string" but type "person". The UI in the "Manager" field simply show blank for BDC data sources as these don't match being of type "string"

     

    Regards

     

    Andrew

     

     

    Friday, March 4, 2011 11:47 AM
  • Before you plug your own blog in the forums, please read the question. 

    I'm having the same problem so let me add some additional information and clarification.

    The way the Add New Mapping section works is that it will only present you with Attributes in the drop down list that match the "data type" as defined by SharePoint in the Property Settings section's Type field. 

    For Andrew's specific example the Manager field has a "data type" of Person.  None of the fields coming from his ECT have a "data type" of Person they are all defined as String, Integer, etc.  Therefore nothing shows up in the Attribute drop down list when he's trying to map a field from his ECT to Manager.

    The next logical thought is that Microsoft has created User Defined Data Types in SQL Server for Person, URL, etc. that we must create in our source data to get the import to work.  Unfortunately if you review the User Defined Data Types in the Profile database you will see there are none.  These "data types" are defined by the contents of the table DataTypeList in the Profile database.

    The type Person is an nvarchar(250), URL is an nvarchar(2048), etc. 

    We created User Defined Data Types of Person and URL that matched these specifications exactly, however, the ECT still interprets and presents them as String values.  So even though our Manager field in our data source is of type (Person(nvarchar(250)) it is still considered a String by the ECT.

    Hell Andrew I'm sorry I don't have an answer for you at the moment, but frankly I'm just glad I'm not the only one having this problem. At this point I'm out of good ideas...now if you want a bad idea...

    If you remove the AD mapping in the Manager attribute you can use a T-SQL script to insert the desired values into the Profile database. 

    The UserProfile_Full table gives you the Id of the individual profiles.

    The PropertyList table gives you the list of all the profile properties, for me Manager has a PropertyID of 6.

    The UserProfile_Value table gives you the values stored in each of the property fields.

    You'll just insert a new record into the UserProfile_Value table.  Your T-SQL statement may vary:

    /*[BEGIN: T-SQL]*/

    -- Make a backup of your Profile database before you do this. 
    -- Your probably want to run a DELETE against this table first to remove any existing Manager field values before you do your initial import.
    -- You can pull the GUID from another value in that table as they all seem to have the same PartitionID (at least in our case they do).
    -- @RecordID is the RecordId field value from the UserProfile_Full table.
    -- @ManagerNetworkId is the value you want to insert into the Manager field.  For us the acceptable value is DOMAIN_NAME\NETWORK_ID, I'm not sure if another format will work.

    INSERT INTO UserProfileValue (RecordID, PropertyID, PropertyVal, SecondaryVal, [Text], OrderRank, Privacy, PartitionID)
    VALUES (@RecordId,6,@ManagerNetworkId,NULL,NULL,NULL,1,'0C37852B-34D0-418E-91C6-2AC25AF4BE5B')

    /*[END:   T-SQL]*/

    Microsoft frowns on any direct modifications to these tables, hence the lack of documentation, so proceed at your own risk.   

    Cheers,

    Matt

    EDIT - 4/16/2011

    After speaking with the SharePoint Development support team I got the following response:

    "The normal datatypes such as int, float, char and string are simple data types and will map in user synchronization situations.  The datatypes such as URL and Person are complex datatypes and will not map in user synchronization situations."

    I got this response via email so I didn't have a chance to call them on the fact that sounds like bullshit to me so I'll be following up and trying to get a real answer to this on Monday.

    Cheers,

    Matt

    EDIT - 4/19/2011 

    The real answer is that Microsoft has designed this process around the expectation that you are housing and maintaining all of this information in Active Directory.  So by design there is no way to map information from an ECT to the properties defined with the pseudo data types of Person, URL, etc.  The easiest two options to get around this are:

    1. Write a program that will synch this information with AD so that when you do the import from AD it will be brought into SharePoint properly.  If you've done any programming against AD then you're already familiar with ADSI and/or DirectoryServices from the .Net Framework.  If not, there is a ton of information to be found on the web about this. 
       
    2. Write a T-SQL script to INSERT the information into the SharePoint Profile database and then set up another script as a job to UPDATE the information on a periodic basis.

    I would say number 1 is probably your best bet once you move into a production environment.  For development puproses though, if you just want to get the information into the profiles so that you can demo the user profiles and the org chart funcationality, it would probably be quicker to just do number 2.

    Cheers,

    Matt

    Don't forget to backup your Profile database before running this.

    Here is a T-SQL script for adding/updating Manager, it is not ideal but it will get you started. This is set up for updating the URL for our employee biographies, but the concept is still the same for the Manager field.  Just make sure you set the right PropertyId value when you do the UPDATE. 

    (Sorry the code block markup was screwing up the spacing everytime I edited the post, so I just pasted it in here.  It should copy and paste fine into Management Studio.)

    DECLARE @NetworkId varchar(12), @BioURL varchar(2048)
    DECLARE @RecordId bigint, @BioURLProperty int

    DECLARE People_Cursor CURSOR FOR
     SELECT NetworkId, BioURL
     FROM [SHPT_Custom].[dbo].[People]
    FOR READ ONLY

    OPEN People_Cursor

    FETCH NEXT FROM People_Cursor INTO @NetworkId, @BioURL

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN

     PRINT '@BioURL = ' + ISNULL(@BioURL,'')
     
     SELECT @RecordId = RecordID
     FROM [Database-Server-Name].[Profile-Database-Name].dbo.userprofile_full
     WHERE NTName = @NetworkId
     
     PRINT '@RecordId = ' + ISNULL(CONVERT(varchar,@RecordId),'')
     
     IF (@RecordId IS NOT NULL)
     BEGIN
         
      SELECT @BioURLProperty = COUNT(*)
      FROM [Database-Server-Name].[Profile-Database-Name].dbo.userprofilevalue
      WHERE PropertyID = 10 --Website
       AND RecordID = @RecordId
       
      PRINT '@BioURLProperty = ' + ISNULL(CONVERT(varchar,@BioURLProperty),'')
      
      IF (@BioURLProperty = 0)
      BEGIN
       INSERT INTO [Database-Server-Name].[Profile-Database-Name].dbo.UserProfileValue (RecordID, PropertyID, PropertyVal, SecondaryVal, [Text], OrderRank, Privacy, PartitionID)
       VALUES (@RecordId,10,@BioURL,NULL,NULL,NULL,1,'0C37852B-34D0-418E-91C6-2AC25AF4BE5B')
      END
      ELSE IF (@BioURLProperty = 1)
      BEGIN
       UPDATE [Database-Server-Name].[Profile-Database-Name].dbo.UserProfileValue
       SET PropertyVal = @BioURL
       WHERE RecordID = @RecordId
        AND PropertyID = 10
      END   
     END
     PRINT '-----------------------------------------------------------'
    FETCH NEXT FROM People_Cursor INTO @NetworkId, @BioURL
    END 

    CLOSE People_Cursor
    DEALLOCATE People_Cursor

    EDIT - 05/03/2011

    Below is some sample code from a command line C# application that could be used to update AD directly instead of using a T-SQL script.  There is no error trapping or debugging code here it's just an example of how to update some of the main AD fields for whatever your needs may be.

    USE THIS CODE AT YOUR OWN RISK.  Make sure you run this in a development environment first.  You never want to be cavalier about doing any sort of programming against AD.

        static void Main(string[] args)
        {
          DataTable dt = GetData();
    
          DirectoryEntry de = new DirectoryEntry();
          de.Path = "LDAP://MYSERVERNAME/DC=COM";
          de.AuthenticationType = AuthenticationTypes.Secure;
          de.AuthenticationType = AuthenticationTypes.ServerBind;
          de.Username = "MyUserName";
          de.Password = "MyPassword";
          /*
            [0] NetworkId
            [1] physicalDeliveryOfficeName
            [2] telephoneNumber
            [3] streetAddress
            [4] l
            [5] st
            [6] postalCode
            [7] mobile
            [8] facsimileTelephoneNumber
            [9] ipPhone
            [10] title
            [11] department
            [12] ManagerNetworkId
            [13] employeeId
            [14] employeeType
            [15] AssistantNetworkId
          */
          foreach (DataRow dr in dt.Rows)
          {
            //Uncomment the following two lines if you want to on apply this to only a subset of users
            //if (dr[X].ToString() == "X")
            //{
            DirectorySearcher mySearcher = new DirectorySearcher(de);
            mySearcher.PageSize = 100000;
            mySearcher.Filter = "(&(objectCategory=user)(sAMAccountName=" + dr[0].ToString() + "))";
            mySearcher.PropertiesToLoad.Add("samAccountName");
            mySearcher.PropertiesToLoad.Add("Manager");
            mySearcher.SearchScope = SearchScope.Subtree;
    
            SearchResult result;
            SearchResultCollection resultCollection = mySearcher.FindAll();
    
            if (resultCollection != null)
            {
              if (resultCollection.Count == 1)
              {
                result = resultCollection[0];
    
                DirectoryEntry Userde = result.GetDirectoryEntry();
    
                if (result.Properties.Contains("sAMAccountName"))
                {
                  //******************************************************************************************
                  // SET FLOOR (physicalDeliveryOfficeName)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[1].ToString()))
                  {
                    (Userde.Properties["physicalDeliveryOfficeName"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["physicalDeliveryOfficeName"]).Value = dr[1].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET WORK PHONE (telephoneNumber)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[2].ToString()))
                  {
                    (Userde.Properties["telephoneNumber"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["telephoneNumber"]).Value = dr[2].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET WORK ADDRESS (streetAddress)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[3].ToString()))
                  {
                    (Userde.Properties["streetAddress"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["streetAddress"]).Value = dr[3].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET WORK CITY (l)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[4].ToString()))
                  {
                    (Userde.Properties["l"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["l"]).Value = dr[4].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET WORK STATE (st)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[5].ToString()))
                  {
                    (Userde.Properties["st"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["st"]).Value = dr[5].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET WORK ZIP (postalCode)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[6].ToString()))
                  {
                    (Userde.Properties["postalCode"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["postalCode"]).Value = dr[6].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET MOBILE (mobile)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[7].ToString()))
                  {
                    (Userde.Properties["mobile"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["mobile"]).Value = dr[7].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET FAX (facsimileTelephoneNumber)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[8].ToString()))
                  {
                    (Userde.Properties["facsimileTelephoneNumber"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["facsimileTelephoneNumber"]).Value = dr[8].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET WORK PHONE EXTENSION (ipPhone)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[9].ToString()))
                  {
                    (Userde.Properties["ipPhone"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["ipPhone"]).Value = dr[9].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET JOB TITLE (title)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[10].ToString()))
                  {
                    (Userde.Properties["title"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["title"]).Value = dr[10].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET DEPARTMENT (department)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[11].ToString()))
                  {
                    (Userde.Properties["department"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["department"]).Value = dr[11].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET MANAGER (manager)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[12].ToString()))
                  {
                    (Userde.Properties["manager"]).Clear();
                  }
                  else
                  {
                    DirectorySearcher managerSearcher = new DirectorySearcher(de);
                    managerSearcher.PageSize = 10;
                    managerSearcher.Filter = "(&(objectCategory=user)(sAMAccountName=" + dr[12].ToString() + "))";
                    managerSearcher.PropertiesToLoad.Add("samAccountName");
                    managerSearcher.PropertiesToLoad.Add("DistinguishedName");
                    managerSearcher.SearchScope = SearchScope.Subtree;
    
                    SearchResult managerResult;
                    SearchResultCollection managerResultCollection = managerSearcher.FindAll();
    
                    if (managerResultCollection != null)
                    {
                      managerResult = managerResultCollection[0];
                      string managerName = (string)managerResult.Properties["DistinguishedName"][0];
                      (Userde.Properties["manager"]).Value = managerName;
                    }
    
                    managerSearcher.Dispose();
                    managerResultCollection.Dispose();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET BILLING ID (employeeID)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[13].ToString()))
                  {
                    (Userde.Properties["employeeID"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["employeeID"]).Value = dr[13].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET BILLING TITLE (employeeType)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[14].ToString()))
                  {
                    (Userde.Properties["employeeType"]).Clear();
                  }
                  else
                  {
                    (Userde.Properties["employeeType"]).Value = dr[14].ToString();
                  }
                  Userde.CommitChanges();
                  //******************************************************************************************
                  // SET ASSISTANT (assistant)
                  //******************************************************************************************
                  if (String.IsNullOrEmpty(dr[15].ToString()))
                  {
                    (Userde.Properties["assistant"]).Clear();
                  }
                  else
                  {
                    DirectorySearcher assistantSearcher = new DirectorySearcher(de);
                    assistantSearcher.PageSize = 10;
                    assistantSearcher.Filter = "(&(objectCategory=user)(sAMAccountName=" + dr[15].ToString() + "))";
                    assistantSearcher.PropertiesToLoad.Add("samAccountName");
                    assistantSearcher.PropertiesToLoad.Add("DistinguishedName");
                    assistantSearcher.SearchScope = SearchScope.Subtree;
    
                    SearchResult assistantResult;
                    SearchResultCollection assistantResultCollection = assistantSearcher.FindAll();
    
                    if (assistantResultCollection != null)
                    {
                      assistantResult = assistantResultCollection[0];
                      string assistantName = (string)assistantResult.Properties["DistinguishedName"][0];
                      (Userde.Properties["assistant"]).Value = assistantName;
                    }
    
                    assistantSearcher.Dispose();
                    assistantResultCollection.Dispose();
                  }
                  Userde.CommitChanges();
                }
              }
            }
            mySearcher.Dispose();
            resultCollection.Dispose();
            //} //Comment/Uncomment Here For Filter IF Statement
    
            de.Close();
            de.Dispose();
          }
        }


     

    • Proposed as answer by Thaldin Tuesday, April 19, 2011 2:31 PM
    • Edited by Thaldin Tuesday, April 19, 2011 4:35 PM Adding SQL Script
    Friday, April 15, 2011 8:25 PM