SSIS subquery different database
Alright SSIS got me. I have Database A source and Database B target. I am taking data form a table in A and transfering it to the same table in B. This is ok. However I have a createdbyUSer column in B which need sto be filled with ID of a user i have in user tab.le in B. So.. (fictional statement) INSERT INTO B.dbo.People (name, address, status, createdBy) Select a.name, a.address, a.status, (SELECT userid from b.dbo.user where username开发者_运维百科 = 'myuser') FROM a.dbo.people
I am lost on how to do this in SSIS. I have too many components to pick from and not sure what path i should go down.
Simplest answer I can think of:
OLEDB source from Database A
Select a.name, a.address, a.status, a.myuser from a.dbo.people
lookup from Database B
Select b.userid,b.username from b.dbo.user
on the columns tab in the lookup drag a line between username and myuser and click the checkbox next to userid. In the bottom part of that tab alias the userid column as createdBy in the column under the heading Output Alias
OLEDB Destination to Database B
select the People table to output to and map the columns from your dataflow to the outputs.
精彩评论