Pulling useful info from a MOSS 2007 Person or Group Field dumped via SSIS package

I’ve got a list defined that, as one of the fields (actually a few of the fields), has a Person or Group picker. I’m extracting the entire list through an SSIS package using the SharePoint List Source data flow source dumping into a SQL table. The PoG picker field d开发者_如何学Pythonumps its data like so (each line being a single data item):

163;#Jones, Danny S.
179;#Smith, Sandra M.
164;#Thomas, Rob Y.
161;#Ross, Danny L.
2064;#Smith, Michael D.

I would guess that the number preceeding the ;# is some sort of User ID that SharePoint keeps with the user instead of something helpful like an ADS guid. Can I use SSIS to pull SharePoint’s user profiles so I can match the ID shown to an ADS guid or ADS username, and if so, how? I've tried using the Web Service task within SSIS to call the User Profile service (http://www.my.site/_vti_bin/UserProfileService.asmx), but I get an error about the WSDL being a wrong version.

---

Unfortunately, the ID shown in site fields is local to the list of users against that site.

Each User is uniquely identified by the site and list guids along with the ID field, but the ID is not unique across users lists and so cannot be used for anything other than indexing into that table.

The other issue with this data is that the profile display is updated regulary by the one of the UserProfileSynchronization service timber jobs. I have experienced times when the display name of the user is not updated correctly and will be set to the account name from Active Directory.

To get an idea of what is going on under the hood, have a look at the All_UserData table in a content database.

In Summary

Only the name part of the field is usable in a meaningful way and even that is not completely reliable, but good enough perhaps.

---

Can you modify the fields that are exported from SharePoint? Can you add a calculated person field based on this field? If so, then you can have that Person field store a different form of person data like their username or e-mail address which are far more useful in interacting with other systems.

---

Nat's answer is close. It's actually the UserInfo table. The numbers correspond to that table's tp_ID column. Unfortunately, I still can't figure out how to pull this info using SSIS, so I'm resorting to writing a console app that pulls the table's data through the Sharepoint web service and dumping that into a database table and scheduling it with Windows Task Scheduler. Also, because of how Sharepoint works, each root site collection has different IDs for each person, so I'll need to make separate pulls for each root site collection. Here's the method I'm using:

    private static XElement GetUserInfo(string siteCollectionListsSvc)
    {
        SharepointListsSvc.ListsSoapClient ws = new SharepointListsSvc.ListsSoapClient();
        ws.Endpoint.Address = new System.ServiceModel.EndpointAddress(siteCollectionListsSvc);

        ws.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
        ws.ClientCredentials.Windows.AllowNtlm = true;
        ws.ClientCredentials.Windows.ClientCredential = (System.Net.NetworkCredential)System.Net.CredentialCache.DefaultCredentials;

        XElement userInfo = ws.GetListItems("UserInfo", String.Empty, null, null, "4000", null, null);

        return userInfo;
    }

The method argument would be something like "http://www.my.site/_vti_bin/lists.asmx". My app config that sets up the binding and endpoint:

<configuration>
<system.serviceModel>
    <bindings>
        <basicHttpBinding>
            <binding name="ListsSoap" closeTimeout="00:01:00" openTimeout="00:01:00"
                receiveTimeout="00:10:00" sendTimeout="00:01:00" allowCookies="false"
                bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
                maxBufferSize="5000000" maxBufferPoolSize="524288" maxReceivedMessageSize="5000000"
                messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
                useDefaultWebProxy="true">
                <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
                    maxBytesPerRead="4096" maxNameTableCharCount="16384" />
                <security mode="TransportCredentialOnly">
                    <transport clientCredentialType="Ntlm" proxyCredentialType="None"
                        realm="" />
                    <message clientCredentialType="UserName" algorithmSuite="Default" />
                </security>
            </binding>
        </basicHttpBinding>
    </bindings>
    <client>
        <endpoint address="http://www.my.site/_vti_bin/lists.asmx"
            binding="basicHttpBinding" bindingConfiguration="ListsSoap"
            contract="SharepointListsSvc.ListsSoap" name="ListsSoap" />
    </client>
</system.serviceModel>
</configuration>

Notice that I increased the //binding/@maxBufferSize and //binding/@maxReceivedMessageSize from the default of 65536 to 5000000. We've got about 3000 records that could be returned, and the default size wasn't nearly big enough. Since these are all internal calls, I'm not worried about network lag. Other changes from the default binding are in the //security element, specifically the @mode and //transport/@clientCredentialType attributes.

When you get the XML back, the number (stored in the PoG field) is in the //z:row/@ows_ID attribute, and his corresponding ADS login is in the //z:row/@ows_Name attribute. You also get the email address back in the //z:row/@ows_EMail attribute.

Hope this helps others get through the same issue!