1

Closed

SharePoint List Destination: Support SharePoint Lookup column types

description

In current version if a SQL text column is mapped to a SharePoint lookup column the DTS job cannot complete. It is desirable for the SharePoint list destination to look up list item id in the lookup list using the value in SQL text column and populate the SharePoint lookup column accordingly.
Closed Oct 17, 2011 at 4:28 PM by kevinidzi

comments

kevinidzi wrote Sep 27, 2011 at 1:44 PM

Try changing the column from a text to a string then. Only issue is that you'll be capped at 4000 length, which may not be an issue. You can change data type in advanced properties.

leiyu wrote Sep 27, 2011 at 3:26 PM

The issue is on SharePoint side - it internally stores the lookup value as <ItemID>;#<LookupValue>. If just <LookupValue> is stored an error will occur.

kevinidzi wrote Oct 6, 2011 at 7:33 PM

That is correct. You would need to know the internal IDs to store it back to SharePoint. I understand this request as something to allow the user to just pass in the target string (which is not unique) and derive the id and use that, keeping in mind there are multi-selection fields as well.

That is a good idea and I'll update this to be a feature.

kevinidzi wrote Oct 6, 2011 at 7:35 PM

Just to be clear, i realize this is not your scenario, but you CAN store back <itemid> or <itemid>;;<itemid>;;<itemid> to store the values in SharePoint. But you would need to know the ID, which I'm assuming is your root issue here.

leiyu wrote Oct 7, 2011 at 5:49 PM

I had to address this issue for a project I am working on. I have uploaded my changes as a proposed patch.

kevinidzi wrote Oct 11, 2011 at 10:06 PM

I'm working to integrate this in currently, and am thinking of making the column with no id's be a new column, and still retain the other column with ids. Since this component has been around a few years, it would be a breaking change to remove the IDs from all lookup fields for users who may be actually parsing it or doing any transformation on it.

kevinidzi wrote Oct 11, 2011 at 10:57 PM

In reviewing your code, it is a good start. I noticed you actually loading the target list and field data. I'd almost rather someone have anotehr data source to that target list and then join in the data as needed.
Currently, if it is not multi-select, you can actually just set the ID into the lookup field, and it will link it. You do nto need the lookup value -- or the ;# .... but in multi-selection cases, i see that would not work. However, having the field get a list of <LookupValues> and reverse looking them up might lead to duplication, so if it was just a list of <itemids> where you could join in another sharepoint list to get the appropriate IDs, I would prefer that pattern as it is more fundamental for SSIS and the way it works.

fitzrandolph wrote May 3, 2012 at 6:37 PM

Quick question, has this been resolved? I still can not figure the process in getting text from my OLE DB Source into a Lookup Column in a SharePoint List Destination.
And thank you on an awesome job here.

Sign in to add a comment