Dynamic Query Based on a Different Result Set

dear all,

here scenario.

server 1 contains user_id along respective city details.

server 2 contains user_id along respective order details.

i dont have server 1 linked server 2 , viceversa.

now, want filter unique list of users 'x' city - running query in server 1
- based on result set - wanna generate order details querying in server 2

though can store server 1 result set table , port server 2 run serve 2 query
- have constraint cannot create tables in server 1.

i need accomplish designing ssis package.

---

regards, vinodh

hi shailesh,

am kinda lost in below step. please elaborate or me step step instruction follow.

 

select * transaction table2 --(instead of * mention column names)

now using lookup task  join both data set user_id column

and give desire output.

---

regards, vinodh

hi vinodh,

hope have done below 2 steps

1. define 2 connection manager each 1 server.

2. create data flow task inside take 1 oledb source  , select server 1 connection manager , write query

select distinct(user_id) table1 city_id = '55'

now inside same data flow task drag lookup transformation

double-click lookup transformation

in ole db connection manager box, select connection manager second server's database.

use option results of sql query , write query select data second server's database

select * transaction table2

now go columns tab and

in available input columns panel, drag userid available lookup columns panel , drop on  userid column.

and click ok.

after verify result .

you can refer below links

http://msdn.microsoft.com/en-us/library/ms167106%28v=sql.90%29.aspx

http://sqlblog.com/blogs/andy_leonard/archive/2010/02/09/ssis-snack-configuring-an-ssis-2005-lookup-transformation-for-a-left-outer-join.aspx

hope helps!!

---

shailesh , please mark post answered if answers question.

SQL Server  >  SQL Server Integration Services