SSIS subquery different database

Question

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.

Answer 1

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.