Friday, March 6, 2015

Use KingswaySoft and SSIS to Create SharePoint Folders

If you have the Dynamics CRM to SharePoint integration enabled you know the out of the box process will create top level folders in SharePoint based on the CRM record. I’ve found many instances where additional layers of child folders need to be created. Depending on the specific requirements you can write a plugin or a SharePoint workflow to create the folder structure. But what happens when you’ve already got a large amount of pre-existing folders that need to be backfilled with the new structure? If you wrote a plugin you could certainly extract out the bulk of the code and create a one time use application to do the work. If you are using a SharePoint workflow you are probably noticing the SharePoint doesn’t allow you to run workflows against multiple records at the same time through the UI.

If you have SSIS experience one solution is to use the KingswaySoft SSIS adapters for Dynamics CRM and SharePoint to create the folders.

Consider this scenario, you have a large number of CRM Accounts that have been integrated with SharePoint. You need to then create several sub-folders under the Account level folder for all the existing records. This example will walk through using the KingswaySoft toolset with SSIS to create those folders.

The first thing we need is a list of the names of the Account folders in SharePoint. When creating a folder in SharePoint we really need to specify the relative url of the folder that needs to be created. Knowing the exact name of the existing folder will give a perfect start to building the relative path to the new folder. To find the Account records that have been integrated with SharePoint do an advanced find on Document Locations and for criteria just select Regarding (Account) and nothing under it. You can remove all the columns in the result except for Relative URL.


Grab the FetchXML using the ‘Download Fetch XML’ button at the top and it will look something like this:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="sharepointdocumentlocation">
    <attribute name="relativeurl" />
    <link-entity name="account" from="accountid" to="regardingobjectid" alias="af" />

In your SSIS project, insert the FetchXML into the KingswaySoft Dynamics CRM Source using a Source Type of FetchXML.


Next add a Derived Column component. Here we are creating columns that will contain the relative path of the folders that need to be created. ‘Level 1 Folder’ will be directly under the folder that represents the CRM Account and ‘Level 2 Folder’ will be under ‘Level 1 Folder’. You can see it’s just a matter of preceding the Relative URL we are getting from CRM with a ‘/’ and then appending another ‘/name’. Sub-folders just require another ‘/name’ to be appended to the end. Just remember SharePoint folder names can’t contain the following characters: ~#%&*{}\<>?/+|”


For each top level SharePoint folder you need to create add a KingswaySoft SharePoint Destination specifying the Destination List and the ‘CreateFolder’ Action.

If you are creating multiple levels of folders (parents with children) you only need to add the SharePoint Destination for the lowest level in the hierarchy as it will create all the folders in between automatically in a single step.


Set the ‘Input Column’ value to the Derived Column that contains the path of the folder to create.


And that’s pretty much it. Run the job and watch the folders get created. If you find this useful be sure and do the right thing a purchase a license :) Besides backfilling existing records this could be translated into creating the initial top level Account folder in SharePoint so the user doesn’t manually have to go through the step of clicking on the ‘OK’ prompt they normally see when the integration doesn’t yet exist for a record.