开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜