something wrong with DTSLookups

YorkyFlatcap · January 12th, 2005, 01:09 PM

I have setup a new MS Access DB. The tables are initially empty. I'm required to transfer data from an Excel Spreadsheet to an MS Access database.

I've set up a 'Data Task' channel between the XLS file and the MS DB. Within the Data task channel I've written some VB script that checks the validity of data going into a table, lets say Clients. This works well, but my main problem is that whilst I'm filtering data from the XLS file to the Clients table, I wish to only insert UNIQUE names of clients into the table. I thought I could achieve this through the use of the DTSLookup object. But this is not working as I would expect. If the table has some client names in it already, I want to check that the name given to me by DTSSource("CLIENT").value does not exist in the DB Access file. I do this by doing the following in my VBScript :

Code:

lookUpResults = DTSLookups("GetClientID").Execute(newClient)
    IF IsEmpty(lookUpResults) THEN
        DTSDestination("ClientName") = strClient

        ......

where newClient could have a value such as 'South Western Bell'
and the following is my Lookup Sql :

Code:

SELECT     ClientName_id
FROM         Client
WHERE     (ClientName = ?)

I would have expected that if client name exists in the database, then lookupResults should have a value, but lookupResults is returning empty each time.:( Can anyone shed any light as to why its returning nothing everytime?

Also, one more point is, as I stated at the beginning of this topic, the initial state of the tables are empty, and I have multiple values of the same client name. I wish to filter out these multiple values and only insert a unique value of the client name, but this is not working either.
So the DTSLookup object does not seem to work when there are no values in the database to search for or when there are values stored in the DB!

Can anyone help me please as I have no idea what is going on here!

Regards
John

joefawcett · January 13th, 2005, 04:49 AM

So if you run the query by hand there are rows returned, but in a lookup it comes back empty? Are you in a transaction such that the previous rows have not been finally written to the Access table?

Can you use a DISTINCT query on the Excel data to narrow down the initial data returned?

--

Joe (Microsoft MVP - XML)

YorkyFlatcap · January 13th, 2005, 05:20 AM

Thankyou for your reply. If I run the queries by hand then the queries work. Furthermore, I can perform all standard SQL on the data in Excel.
I think you may be on the right track with respect to the data is not being written to the database until the DTS has been fully completed.

So the question is : Whilst the data is being pumped into Access from Excel, is there a way whereby I can immediately update the database so that when the next data retrieval is being made from excel, I can perform a successful DTSLookup?

I've read that I have to have a seperate data pump in order to successfully use the DTSLookup object. I can't quite figure out how this can be achieved since I only have one datapump to perform the transformation of data between Excel and MS Access.

Just one final point to add if its going to be of any help. The Data pump is a Transform Data Task pump.

Regards

John

jemacc · January 14th, 2005, 07:41 PM

Try bring your results set by using an order by desc to bring your records to the top of list.

Can you show your entire line? What do you do when you encounter a null value, do you continue or skip it.

Jaime E. Maccou