merge data in a sharepoint2010 user profile property sync with sql using BCS

Question

i have configured the user profile syncing with my custom SQL table. i require 4 properties that are with 6 columns in the table

& the scenario is i have to concatenate the strings of the 3 columns of sql table in one property of user profile.

say sql structure is like

customer_id number(10) not null, customer_name varchar2(50) not null, address varchar2(50),
city varchar2(50),
state varchar2(25), zip_code varchar2(10),

i want to map address property of user profile with string = address + city + state

please suggest any way to achieve this .

Answer 1

This type of conditional behaviour is not suitable for a normal BCS connector. Instead create a .NET connectivity assembly that encapsulates your SQL calls, for example in LINQ, and create the logic in C#.

More info here

Answer 2

Not sure how to do it with BCS, but with PowerShell it would be pretty easy:

http://iedaddy.com/2010/06/sharepoint-2010-updating-user-profile-properties-with-powershell/

In the aboce example it uses a .csv file for data, but obviously other data connections could be utilized.