Microsoft Sharepoint

I've also seen this requirement very recently where a customer is planning a data migration from one document management system to Sharepoint. At the moment we are looking at what options there are to use FME to do this via the Sharepoint Web Service. There are 4 million documents to migrate, which is far too much for CTRL-X and CTRL-V!

Has anyone done this via FME?

Would a batch file in windows be easier to copy all the data up?

http://social.technet.microsoft.com/wiki/contents/articles/4437.upload-files-from-a-local-machine-to-a-sharepoint-document-library-automatically-dos-command.aspx

Hi Nick

For a quick reference, you can upload documents by sending a SOAP message to the CopyIntoItem method of copy.asmx.

I build up the SOAP message using XMLTemplater.

An example ROOT Template:

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
 <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <CopyIntoItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
      <SourceUrl>nothing</SourceUrl>
      <DestinationUrls>
        <string>$(SP_SITE){fme:get-attribute("SP_list_root_folder")}{fme:get-attribute("SP_path")}</string>
        <string>$(SP_SITE){fme:get-attribute("SP_list_root_folder")}{fme:get-attribute("SP_path2")}</string>
      </DestinationUrls>
      <Fields>
        <FieldInformation Type="Text" DisplayName="Surname" Value="Smith" />
        <FieldInformation Type="DateTime" DisplayName="Date of Birth" Value="1965-11-03" />
      </Fields>
      <Stream>{fme:get-attribute("document_base64_encoded")}</Stream>
    </CopyIntoItems>
  </s:Body>
</s:Envelope>

$(SP_SITE) is an example for workspace parameter and {fme:get-attribute("SP_list_root_folder")} for an attribute.

There are 4 important bits:

1. <DestinationUrls>

    

   You need the full protocol://server/list_root_folder/SP_path/document.ext URL where you want the document to upload.

    

   The folder must exist on SharePoint

    

   You can upload the document to multiple destinations.

    

   You can upload the document to the same server where the copy.asmx rwsides only.
2. <Stream>
   base64 encoded content of the document. I could upload documents up to 40 MB. It is worth to restrict the size because large documents can make the workspace stall.
3. <Fields>

    

   Optionally you can set metadata.

    

   The Type and DisplayName should match. (There is another request that reveals what metadata exist in the list.)

    

   DateTime values should be in ISO format.
4. 
   The contents of <SourceUrl> is indifferent but cannot be empty (I don't know why)

Set Write XML Header to Yes in the XMLTemplater.

 

You can use the HTTPCaller transformer to send the message to the web service:
Request URL: protocol://yoursharepointserver/_vti_bin/copy.asmx
HTTP Method: POST

You need to add a header (SOAP method to call) in the Headers section :
Name: SOAPAction
Value: http://schemas.microsoft.com/sharepoint/soap/CopyIntoItems

In Body section:
Upload Data: Specify Upload Body
Upload Body: set the Result Attribute of the XMLTemplater
Content Type: text/xml 
Base64 encoding the document content is quite easy:

1. AttributeFileReader transformer: set the Source File Character Encoding to Binary (FME-binary)
2. BinaryEncoder transformer: Encoding Type: Base64

If you upload large number of documents, it is worth to delete file content, base64 encoded and SOAP request attributes after HTTPCaller to avoid eating up memory.
I created a CSV with the document source file path, SharePoint URL and optionally metadata columns. that drive the upload.
In theory the workspace is straightforward: CSV reader -> AttributeFileReader -> BinaryEncoder -> XMLTemplater -> HTTPCaller -> AttributeRemover -> Inspector
There are a few possible problems: missing or unreadable source file, large file, invalid characters in filename (SharePoint is more restrictive than tile system), ...
Another thing, proper attribute display names and types:
Send a request to

Request URL: protocol://yoursharepointserver/_vti_bin/lists.asmx

Headers:
Name: SOAPAction
Value: http://schemas.microsoft.com/sharepoint/soap/GetList

 

Body:

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
    <Body>
        <GetList xmlns="http://schemas.microsoft.com/sharepoint/soap/">
            <listName>$(SP_LIST_NAME)</listName>
        </GetList>
    </Body>
</Envelope>

The XML response will contain Type, DisplayName, .. of all attributes.
My workspaces are a bit specific and big but if there is any interest I can get rid of the unnecessary bits and upload.
Hope it helps.

Just dropping an additional contribution to this thread should anyone find it. We're expecting a Sharepoint 'package' to land in the next few weeks from the good people at Safe that will include the ability to access formats supported by FME directly on Sharepoint via a web connection as well as a dedicated SharepointConnector Transformer. I believe there will be many very happy people 😀 .