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.

SqlTask2

 

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.

SqlTask

 

 

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
Bitnami