SharePointListAdapters adding # to values

May 4, 2009 at 8:39 AM
I am using the Sharepoint list adapters to pull data from sharepoint view to SQL tables. The task is working fine. The output data contains values like String#;800 where as the data shown on sharepoint viesw is just 800. Not sure why the additional string is appended while importing the data. Has anyone faced the similar issue? If yes, please suggest the solutions?

Thanks in advance for looking into it.

May 4, 2009 at 6:12 PM
That occurs when the SharePoint list data is a lookup or calculated column...use a Scrpt Task to strip out that stuff...the value there-if it is a lookup column-is the ID of the list item used for the lookup...can be useful-save doing a Lookup Task to find the ID!

If Row.MailingSuffix.Contains("#") Then
Row.MailingSuffix = Row.MailingSuffix.Remove(0, Row.MailingSuffix.IndexOf("#") + 1)
End If


May 7, 2009 at 7:49 PM
Thanks Brandsmith. being a newbie to BIDS, sorry for dumb request.. Could you please elaborate on the task and where and how it should be added. if possible please give some example steps.

Thanks much!
Jan 26, 2010 at 10:48 PM

Hi brand, this SSIS addin support sharepoint lookup columns?

Should I use a CAML Query to get rid or the nasty characters (;#)???

Thank you!

Feb 7, 2011 at 4:42 PM

Add a "derived column" data flow transformation control between the source and destination. In the "Expression" box enter the following (replace supplier with the column name):

SUBSTRING(Supplier,(FINDSTRING(Supplier,"#",1) + 1),255)

In the "Derived Column" input "Replace"