Re: SSIS lookup across disparate servers / DBs

#1

On Nov 25, 5:34*pm, Dave <djohanns... (AT) gmail (DOT) com> wrote:

Quote:

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

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

#2

On Nov 25, 5:47*pm, Alen Teplitsky <alent1... (AT) gmail (DOT) com> wrote:

Quote:

On Nov 25, 5:34*pm, Dave <djohanns... (AT) gmail (DOT) com> wrote:

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

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- 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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: SSIS lookup across disparate servers / DBs

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: SSIS lookup across disparate servers / DBs - 11-25-2009 , 07:47 PM

Re: SSIS lookup across disparate servers / DBs - 11-25-2009 , 08:10 PM