Wednesday, February 16, 2011

BCS, PeopleSoft, and the SharePoint User Profiles

I was trying to import PeopleSoft data into SharePoint User Profiles using a BCS connection to an Oracle view that was provided by my DBA.  The only field that existed in both environments was “NTUSERNAME”.  Although not the most ideal, this would work as they are unique in their environment (Single Domain/Forest).  I had the connection working and returning data to a list so I thought I was home free.  I went into the ForeFront Identity Manager Client and made the change to my BCS Management Agent so that “NTUSERNAME” from PeopleSoft was matching up to the Metaverse “username” field populated by “sAMAccountName” from AD.  Figured I was good to go.  Kicked off a full synchronization and a whopping two accounts matched up out of almost 2000 expected.

<INSERT DAYS OF BANGING HEAD AGAINST WALL>

We started to look and see what was different about these two accounts.  Using TOAD we ran the query that was providing the View and looked for the two accounts.  Strangely enough they were lowercase and all the others returned were UPPERCASE.  I hit up my Identity guys and they said that shouldn’t matter in a Join.  For giggles though, I had my DBA re-write the query so it returned ALL values in the NTUSERNAME field as lowercase.  I ran a full synchronization and they came over like a champ!

Just throwing it out there in case I can save anyone else from a world of frustration…

Jeff

2 comments:

  1. Hi Jeff,
    Could you explain the full step by step process of how you went about doing this - Did you create ECT in the SPD 2010 and connect to the oracle DB view. Could you share your findings and method to pull data to reflect in User Profiles?

    ReplyDelete
  2. We actually made use of the BCS Meta Man by Lightning Tools (http://www.lightningtools.com/bcs/bcs-meta-man.aspx).

    ReplyDelete