June 2, 2009 at 8:42 am
This seems like it should be pretty easy but I'm drawing a blank.
I'm using SSIS to create a handy-dandy user/manager lookup table from the SharePoint user profile table.
The problem is the source holds the following information:
FriendlyName Bob Jones
NTName domain\bjones
Manager domain\psmith
In my new table I've created a ManagerName column that I was hoping to populate with the FriendlyName of the manager... At the moment I can't figure out how to get it to populate in the SSIS package.
In other words, this is the result I want.
FriendlyName Bob Jones
NTName domain\bjones
Manager domain\psmith
ManagerName Paul Smith
June 2, 2009 at 8:51 am
Hi Kevin,
From your data source connect to a multicast transform. The first output is to be sorted by ntname the second by manager. Then join the two thread (probably left outer join).
HTH
Alexander
June 2, 2009 at 11:09 am
Or run a simple UPDATE query after the import has completed ...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 2, 2009 at 12:04 pm
Thanks!
The update statement worked... I was right, it was pretty simple as soon as started thinking "outside the table"
Instead of referencing the newly created table I added an Execute SQL statement that went back to the original source... put that after my data flow task completed and, presto!
For the record:
update dbo.new_table
set ManagerName = (select PreferredName
from WSS_SharedServices1_DB..UserProfile_Full
where WSS_SharedServices1_DB..UserProfile_Full.NTName COLLATE DATABASE_DEFAULT = dbo.new_table.Manager COLLATE DATABASE_DEFAULT)
June 2, 2009 at 5:39 pm
kevin.lanners (6/2/2009)
Thanks!The update statement worked... I was right, it was pretty simple as soon as started thinking "outside the table"
Instead of referencing the newly created table I added an Execute SQL statement that went back to the original source... put that after my data flow task completed and, presto!
For the record:
update dbo.new_table
set ManagerName = (select PreferredName
from WSS_SharedServices1_DB..UserProfile_Full
where WSS_SharedServices1_DB..UserProfile_Full.NTName COLLATE DATABASE_DEFAULT = dbo.new_table.Manager COLLATE DATABASE_DEFAULT)
Glad you made it work. But I was thinking more along the lines of
update t1
set Manager = t2.FriendlyName
from dbo.new_table t1 join dbo.new_table t2 on t1.Manager = t2.MTName
where t1.Manager t2.FriendlyName or t1.Manager is Null
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply