I've got a DTS process that creates mdb's for exporting data back to
clients based on recent orders paid. The main system is SQL Server
but our clients prefer mdb files for their purposes.

My DTS process currently looks up orders paid through a certain date,
but not yet exported and then inserts those orders into an Access
template which is later renamed (the template itself always stays
empty).

That all works fine but now I need to add another step to the DTS
package. Some of the info that's been requested in the export is not
stored in SQL Server, but in a separate mdb. I need my DTS package to
go look those values up and update the export mdb.

For instance I have employee data and the main export process inserts
that employees ID number, first and last name, etc. but the employee's
company association is *not* stored in SQL Server - that has to be
fetched from the other Access database.

So after all employees are inserted into the export mdb I need a
second process that will go and look up their company in the other mdb
based on the employee ID. I set up an ActiveX Copy Column task from
the other mdb's connection, but of course all that did was insert rows
for ALL companies in the other mdb, rather than joining and updating
only the company info for the employee(s) contained in the export mdb.

I also tried using a Data Driven Query task and creating a mapping to
the employees table in the second mdb that contains the company
associations but could not figure out how to get that to work.

I've found some examples online but none that I can fully digest to
help me figure out a solution for my problem.

Any insight would be greatly appreciated!

TIA,
Mike

As a follow-up to my first message, I haven't fixed this yet, but I am
on to something that looks like it should work, although it's not yet.

Here's what I've done. I've added two lookups to my main transform
data task. They are:

SELECT FacilityNumber
FROM Applicators
WHERE (ApplicatorID = ?)

and

SELECT CompanyName
FROM Applicators
WHERE (ApplicatorID = ?)

Applicators being the appropriate table in the 3rd database.
Then in the Transformations step I added two lines to pull the values
via the lookups. Although the code makes sense, it's not working.
Here's what I added:

DTSDestination("FacilityName") =
DTSLookups("FindCompany").Execute(DTSSource("State AssignedID").Value)
DTSDestination("FacilityNumber") =
DTSLookups("FindFacility").Execute(DTSSource("Stat eAssignedID").Value)

Not sure what I'm missing here, but any help would be greatly
appreciated.

Mike







On Mon, 01 Nov 2004 14:56:42 GMT, Mike
<ga_harley_guy (AT) _REMOVE_yahoo (DOT) com> wrote:

Actually the code below DID work, there was a data problem with the
key I was using.


On Mon, 01 Nov 2004 16:50:15 GMT, Mike
<ga_harley_guy (AT) _REMOVE_yahoo (DOT) com> wrote:

So everything is good now?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Mike" <ga_harley_guy (AT) _REMOVE_yahoo (DOT) com> wrote