SSIS lookup across disparate servers / DBs

[Dave]

Hey there,

I need to do a lookup within SSIS, however I need to be able to reach
across / join to two disparate servers / databases in my lookup.

However, the SSIS Lookup object only has 1 “OLE DB Connection manager”
connection at its disposal.

E.g.
SELECT dw.CountryKey, lir.CountryCode
FROM DB1.dbo.Country lir
JOIN DB2.dbo.CountryLookup dw
ON lir.ISOCountryCode = dw.CountryCode

Again, both DBs are on separate servers, so connection strings are
needed to avoid hard coding.

So I could use some help in how to best achieve my multi-server joined
lookup. Any ideas?

Thanks so much,

dave

[Alen Teplitsky]

are you importing data? you can do this from a data flow task and in
the OLE DB source specify a SQL command instead of a table. you might
have to create a linked server

[Dave]

> have to create a linked server- Hide quoted text -
>
> - Show quoted text -

yeah this is part of a greater task of importing data. the lookup was
to add data for 1 additional column. i think you're right with linked
server, and i could use that in the lookup task.