DTS, Lookups--Using a lookup in a DTS transform

DTS, Lookups--Using a lookup in a DTS transform

Post by Charles S. Rathkop » Fri, 21 May 1999 04:00:00



Has anyone had any luck using a lookup in a DTS transform.  I swear that I'm
doing a direct translation of the example ('Performing Single-key Lookups
from an ActiveX Script' on the MSDN web site
http://msdn.microsoft.com/library/sdkdoc/sql/dtssamp_4.htm ) but I always
get the error:  "An exception of type 'Microsoft VBScript runtime error:
Type mismatch:  'DTSLoopkus'' was not handled."

If anyone has an example that actually works I would be very grateful.

Other details of my project.  The data source is an Excel sheet.  The
destination is an Access db.  The table being used for the lookup is also in
the same access db.  Direct column mapping (or even doing direct mapping
using script) works fine.

For direct mapping the script is:  DTSDestination("Project Name") =
DTSSource("Project Name")

Trying to use lookup my line is:  DTSDestination("Project Name")
=DTSLookups( "ProjNameTOProjNum").Execute(DTSSource("Project Name").Value)

My lookup is: ProjNameTOProjNum
with SQL:  SELECT ProjectNum
FROM Project
WHERE (ProjectName = ?)

Is there any other information I can provide?

If responding directly by email, please remove anti-spam measure.

Thank you in advance for your help.

Charley Rathkopf

 
 
 

DTS, Lookups--Using a lookup in a DTS transform

Post by mmclan » Sat, 22 May 1999 04:00:00


Unless it's a cut/paste typo in your message, your lookup is trying to
return a num into DTSDestination("Project Name").   How about:

DTSDestination("Project Num")
=DTSLookups( "ProjNameTOProjNum").Execute(DTSSource("Project Name").Value)


>Has anyone had any luck using a lookup in a DTS transform.  I swear that
I'm
>doing a direct translation of the example ('Performing Single-key Lookups
>from an ActiveX Script' on the MSDN web site
>http://msdn.microsoft.com/library/sdkdoc/sql/dtssamp_4.htm ) but I always
>get the error:  "An exception of type 'Microsoft VBScript runtime error:
>Type mismatch:  'DTSLoopkus'' was not handled."

>If anyone has an example that actually works I would be very grateful.

>Other details of my project.  The data source is an Excel sheet.  The
>destination is an Access db.  The table being used for the lookup is also
in
>the same access db.  Direct column mapping (or even doing direct mapping
>using script) works fine.

>For direct mapping the script is:  DTSDestination("Project Name") =
>DTSSource("Project Name")

>Trying to use lookup my line is:  DTSDestination("Project Name")
>=DTSLookups( "ProjNameTOProjNum").Execute(DTSSource("Project Name").Value)

>My lookup is: ProjNameTOProjNum
>with SQL:  SELECT ProjectNum
>FROM Project
>WHERE (ProjectName = ?)


 
 
 

DTS, Lookups--Using a lookup in a DTS transform

Post by Euan Garde » Sat, 22 May 1999 04:00:00


Charles,
Are you actually executing the package or just using the test button.
Be aware there is a known bug in the test button of the ActiveX script
where it will fail when you use lookups or globals variables. But it
should execute just fine.

This is fixed in SP1

Euan


Quote:> Has anyone had any luck using a lookup in a DTS transform.  I swear that
I'm
> doing a direct translation of the example ('Performing Single-key Lookups
> from an ActiveX Script' on the MSDN web site
> http://msdn.microsoft.com/library/sdkdoc/sql/dtssamp_4.htm ) but I always
> get the error:  "An exception of type 'Microsoft VBScript runtime error:
> Type mismatch:  'DTSLoopkus'' was not handled."

> If anyone has an example that actually works I would be very grateful.

> Other details of my project.  The data source is an Excel sheet.  The
> destination is an Access db.  The table being used for the lookup is also
in
> the same access db.  Direct column mapping (or even doing direct mapping
> using script) works fine.

> For direct mapping the script is:  DTSDestination("Project Name") =
> DTSSource("Project Name")

> Trying to use lookup my line is:  DTSDestination("Project Name")
> =DTSLookups( "ProjNameTOProjNum").Execute(DTSSource("Project Name").Value)

> My lookup is: ProjNameTOProjNum
> with SQL:  SELECT ProjectNum
> FROM Project
> WHERE (ProjectName = ?)

> Is there any other information I can provide?

> If responding directly by email, please remove anti-spam measure.

> Thank you in advance for your help.

> Charley Rathkopf

 
 
 

DTS, Lookups--Using a lookup in a DTS transform

Post by Euan Garde » Tue, 25 May 1999 04:00:00


Andy,
See my reply to Charles, your prob looks like it is caused by the bug, try
running the package
-Euan

> DTSLookups is not working

> I do focus the same problem like Charley Rathkopf

> Using the construct:
> DTSDestination("Abt_Ix") =
> TSLookups("A").Execute( DTSSource("Col001").Value)
> Will fail with error: Type mismatch 'DTSLookups'

> The lookup query, his name is 'A':
> SELECT Abt_Ix
> FROM T_Abteilungen
> WHERE (Abt = ?)

> I have SP1 installed.

> And Keller, UNISYS Switzerland



> > Has anyone had any luck using a lookup in a DTS transform.  I swear that
> I'm
> > doing a direct translation of the example ('Performing Single-key
Lookups
> > from an ActiveX Script' on the MSDN web site
> > http://msdn.microsoft.com/library/sdkdoc/sql/dtssamp_4.htm ) but I
always
> > get the error:  "An exception of type 'Microsoft VBScript runtime error:
> > Type mismatch:  'DTSLoopkus'' was not handled."

> > If anyone has an example that actually works I would be very grateful.

> > Other details of my project.  The data source is an Excel sheet.  The
> > destination is an Access db.  The table being used for the lookup is
also
> in
> > the same access db.  Direct column mapping (or even doing direct mapping
> > using script) works fine.

> > For direct mapping the script is:  DTSDestination("Project Name") =
> > DTSSource("Project Name")

> > Trying to use lookup my line is:  DTSDestination("Project Name")
> > =DTSLookups( "ProjNameTOProjNum").Execute(DTSSource("Project
Name").Value)

> > My lookup is: ProjNameTOProjNum
> > with SQL:  SELECT ProjectNum
> > FROM Project
> > WHERE (ProjectName = ?)

> > Is there any other information I can provide?

> > If responding directly by email, please remove anti-spam measure.

> > Thank you in advance for your help.

> > Charley Rathkopf

 
 
 

DTS, Lookups--Using a lookup in a DTS transform

Post by Andy Kelle » Wed, 26 May 1999 04:00:00


DTSLookups is not working

I do focus the same problem like Charley Rathkopf

Using the construct:
DTSDestination("Abt_Ix") =
TSLookups("A").Execute( DTSSource("Col001").Value)
Will fail with error: Type mismatch 'DTSLookups'

The lookup query, his name is 'A':
SELECT Abt_Ix
FROM T_Abteilungen
WHERE (Abt = ?)

I have SP1 installed.

And Keller, UNISYS Switzerland



Quote:> Has anyone had any luck using a lookup in a DTS transform.  I swear that
I'm
> doing a direct translation of the example ('Performing Single-key Lookups
> from an ActiveX Script' on the MSDN web site
> http://msdn.microsoft.com/library/sdkdoc/sql/dtssamp_4.htm ) but I always
> get the error:  "An exception of type 'Microsoft VBScript runtime error:
> Type mismatch:  'DTSLoopkus'' was not handled."

> If anyone has an example that actually works I would be very grateful.

> Other details of my project.  The data source is an Excel sheet.  The
> destination is an Access db.  The table being used for the lookup is also
in
> the same access db.  Direct column mapping (or even doing direct mapping
> using script) works fine.

> For direct mapping the script is:  DTSDestination("Project Name") =
> DTSSource("Project Name")

> Trying to use lookup my line is:  DTSDestination("Project Name")
> =DTSLookups( "ProjNameTOProjNum").Execute(DTSSource("Project Name").Value)

> My lookup is: ProjNameTOProjNum
> with SQL:  SELECT ProjectNum
> FROM Project
> WHERE (ProjectName = ?)

> Is there any other information I can provide?

> If responding directly by email, please remove anti-spam measure.

> Thank you in advance for your help.

> Charley Rathkopf

 
 
 

DTS, Lookups--Using a lookup in a DTS transform

Post by Charles S. Rathkop » Wed, 26 May 1999 04:00:00


Euan,

Thanks.  Late Friday I tried executing the package and, much to my shock and
surprise, it worked correctly.  I thought for sure that I had tried to
execute it before, but I think at that time it was still* up on other
bugs.

This little bug really limits the use of that test button, doesn't it.

I am using a 120 day eval that came with a book.  Do you know if I can apply
SP1 to this.  I'm currently waiting for my free development copy that I'm
supposed to receive because I bought Visual Studio 6.0 and sent in the
coupon.  Does anyone know if there is a way to check on the status of this
with Microsoft, or did I just give them more marketing info and otherwise
send my request into a black hole.  Did anyone else ever receive their
development edition of SQL 7?

Charley


>Charles,
>Are you actually executing the package or just using the test button.
>Be aware there is a known bug in the test button of the ActiveX script
>where it will fail when you use lookups or globals variables. But it
>should execute just fine.

>This is fixed in SP1

>Euan

 
 
 

1. Using DTS Lookups and onwards....

Hi folks,

I'm in the process of building DTS packages to populate a Datawarehouse, I'm
using the Lookup facility to pull surrigate keys to great effect.

My problem is that when pulling in a transaction, and doing a lookup on a
customer (for example), If this entry doesn't exist in the Customer
dimension, I get a nasty Null for my Cross ref.

What I want to do is :
Add the Customer to the dimension,
pull the surrigate key,
and go back to populate subsequent Customer details at a later stage.

Has any anybody tried to do this via the VBActiveX script in the DTS
transformations.

Any Suggestions warmly appreciated.
Kind regards
Danny

2. Foxpro Memory Leak

3. Using lookup tables in DTS (How?)

4. Need informal Acceptance Test for Oracle 7.3.4

5. Using Stored Procedures in DTS Lookups

6. Latest ADO version?

7. Error using DTS Lookups in SQLServer 7.0

8. Job Prospects In Oracle?

9. using DTS Lookup and stored procedures or UDFs

10. DTS DDQ VBS Update Using Lookup

11. DTS lookup using stored procedure : one input and one output parameter

12. Using DTS Lookups

13. DTS Transformations using Lookup