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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

777 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