Re: Basic Lookup Queries

#1

Lots of good examples with picutures here :- www.sqldts.com

--
HTH
Ryan Waight, MCDBA, MCSE

"Brandon Campbell" <bkc5 (AT) cdc (DOT) gov> wrote

Quote:

Hello,

I've been to the BOL to find out how to write a basic
lookup query and I am confused as to how to do this. I
created a connection to an excel document and another to
my SQL database. In the example provide by Microsoft, I
completed the first connection. It then says to write a
query statement leaving a question mark. Am I supposed to
open a data driven query? If so, where do I write this
query? The third step is to write an ActiveX script
transformation. Does this go in the data driven query? I
wish Microsoft would use some pictures as well as words.
It doesn't seem very helpful using the Enterprise Manager
which is mostly icon based and not have a pictoral guide.

Thank you for your help.

Brandon

#2

OK

The connection types matter little.

In your example . You have the words in Access but not the ID. You want to
lookup the Word_ID number that goes with this ID Number and enter that + the
word into Table2?. In any event this should get you started

Access ---> SQL Server

Add an additional SQL Server connection for your lookups

You write a query in the lookup that points to the SQL Server connection
specifically designed for the lookup.

It would go something like

SELECT word_id_number FROM Table1 WHERE ID_number = ?

In your ActiveX transform it would look like

dim Wrd_id_Num

Wrd_id_Num = DTSLookups("NameOfLookup").Execute(DTSSource("ID_N umber"))

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Brandon Campbell" <bkc5 (AT) cdc (DOT) gov> wrote

Quote:

Ryan,

I printed out the tutorial on DTS lookups and it doesn't help much. I
guess the problem is that they are doing a SQL Server to SQL Server
lookup and don't mention any other possibilities and the I believe the
pictures are mislabeled. Second, I still do not know how to provide the
server with the lookup value.

This is what I have. I have an old Access database that is flat and I am
trying to transfer all the data into a SQL database that is relational.
A row in the Access database has several descriptive fields.

ie:
Access Database

ID_number, word1, word2, word3

The relational database two tables.

SQL Server
Table 1

ID_number
word_id_number

Table 2

word_id_number
word

I need to provide the Lookup the ID_Number from table 1 and put the
information into table 2 corresponding with the word_id_number.

Brandon H. Campbell
CDC/NIOSH

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

Re: Basic Lookup Queries

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Basic Lookup Queries - 11-10-2003 , 07:35 AM

Re: Basic Lookup Queries - 11-10-2003 , 09:30 AM