Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


DTS - ActiveX script table lookup

Posted on 2003-03-06
Medium Priority
Last Modified: 2008-03-17
Can I alter the following ActiveX script to
take the value currently in DTSDestination (an integer),
look up a name from a reference table and store that name
instead of the reference number to the destination table?

'  Copy each source column to the destination column
Function Main()
     DTSDestination("FinanceID") = DTSSource("Col001")
     Main = DTSTransformStat_OK
End Function

Question by:vd

Accepted Solution

wdalton earned 200 total points
ID: 8083588
Yes you can.
The 'Lookups' tab of the datapump alows you to define lookup queries to other tables (even using other connections)
The syntax of the lookup query is simple: Select name from <yourtable> where <yourkey>=?
Each ? will correspond to a parameter when the lookup is called. Order is important as there is no parameter naming.
This lookup can be used in the activeX script by executing the lookup using the key value as parameter. probably looks something like DTSDestination("myName")=DTSLookups("MyLookup").execute(DTSSource("Col001"))
Don't know the exact syntac from the top of my head. Once the lookup is defined, use drag & drop to use it in the script.

Author Comment

ID: 8113364
Can you give me the activeX syntax for executing the lookup?

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question