various shenanigans.

SSIS transformation for Sharepoint List Adapter

I was using the SSIS Sharepoint List Adapter and ran into an issue with importing calculated columns and People columns.

The import process was inserting USERID;#Username into my People column in the destination database table. It was

also inserting datetime;#12/12/2013 into my date fields.



I found a handy script component that will transform those columns on the fly.

You have to insert the script component as a transformation between the Sharepoint List Source and your destination. You must also allow
all columns to pass through. The column you want to manipulate must be set to Read/Write.




Paste this in the Input0_ProcessInputRow function…

string[] delimiter = new string[] {“;#”};
string[] peopleSplit = Row.PeopleField.Split(delimiter,StringSplitOptions.None);
string result = string.Empty;

for (int i = 1; i < peopleSplit.Length; i += 2)
result += peopleSplit[i];
if (i != peopleSplit.Length – 1)
result += “, “;

Row.PeopleField = result;

Change the “PeopleField” string to your appropriate column.


Close Bitnami banner