SSIS and SharePoint List Sources with Lookup Fields

If you are not familiar with the Microsoft SQL Server Community Samples: Integration Services site, then I recommend that you check it out:

https://sqlsrvintegrationsrv.codeplex.com/

The site contains excellent SSIS samples and documentation on how to build solutions for everyday problems using SSIS. One of the most useful samples covers using a SharePoint list as a data source. A special SSIS Data Adapter project has been created and packaged so that you can run an installer and then use SharePoint lists in your SSIS package. The project can be found here:

https://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

After following the installation procedure you end up with a SharePoint List Source and SharePoint List Destination in your toolbox.
You can then use these sources and destinations in any Data Flow Task.


While using the SharePoint List sources today I ran into an issue using a lookup field. Essentially, my package was copying data from a SharePoint list into a local database table. The data exposed by the List Source included the internal SharePoint lookup value. Essentially, it was including the field type and the separator (in this case ‘string;#’), which I did not want in my table.

There is an easy fix to remedy this situation though.

All that is needed to strip out the unwanted data is to add a Derived Column data flow transformation to your data flow task after your SharePoint List source.



In the Derived Column transformation you will need to use a string expression to strip out the unwanted data.

TRIM( SUBSTRING( YOUR_COLUMN_NAME, FINDSTRING( YOUR_COLUMN_NAME, “;#”, 1 ) +2, 255) )

With this simple transformation, I was able to cleanse my lookup data and transfer it to the database without issue.

I’d love to hear if anyone uses a different technique when dealing with this situation.
I hope this helps!