search

fan130 / mssql-ssis-community-samples

Microsoft Public License
30 stars 18 forks source link

Two data sharepoint source and lookup #3

Open sudnep opened 6 years ago

sudnep commented 6 years ago

I am using ssis to copy list item from one source list to destination. I am using SharePoint List Adaptor for SSI from codeplex. Here is the scenario. Lets call first List List1 ( has two columns ColA and ColB) and Second list List 2 (has one column ColC). If ColA value matches ColC then I need to replace ColA with ColB then write this into new List called List3.

In SSIS I am treating list1 and list2 as two different data source. How can I implement above change?

cpassuel commented 6 years ago

You can use Cache Manager to cache List2 and make a lookup between List1 and cache to get matching values then you can update List1, and with a multicast you can write data in List3

FearNotDaniel commented 6 years ago

An alternative solution is to use a Merge Join component with a LEFT join, ensuring both sources are sorted and setting properties to declare this fact. If there is no match, the output will contain NULL in the columns of the 'right' source. You can test for this and generate a derived column that takes the value of ColA or ColB as appropriate, then write the value of this derived column into List3.

Your question is a little unclear whether you also want to write the updated value back to List1, if so then use @cpassuel's idea of a multicast component to direct the output into two different destinations.

Having said that, an issues list isn't really the right place to ask for support, rather for either reporting bugs or requesting new features. Questions like this would be better placed on Stack Overflow or an appropriate forum.