In message <Hoj9c.40754$PY.27519@newssvr26.news.prodigy.com>, Jim Miller
<jmiller@miramontes.com> writes
[quoted text, click to view] >I'm trying to move a multiple-table database structure from SQL Server to
>Access (don't blame me, blame my client...). A very simplified view of the
>problem is this:
>
>* The SQL "ProblemReports" table contains information submitted by a user.
>This table includes the user's name and ID string, and other information
>about the problem.
>
>* The Access database contains one table for ProblemReports and one table
>for User information. For each of the entries in the original SQL
>ProblemReports table, I have to create an entry in the User table for the
>user who created the report, and an entry in the Access ProblemReports table
>with the rest of the information about the ProblemReport. I then need to
>point the Access ProblemReports record at the corresponding Access User
>record by putting the ID number of the newly-created User entry into a "User
>ID" field in the Access ProblemReports table.
>
>Newbie to DTS that I am, I've figured out how to build connections and tasks
>that move the User information from SQL to Access. I can similarly move the
>ProblemReport information from SQL to Access. The problem is that I need to
>run these two procedures sequentially by row: For each row in the SQL table,
>I need to create a user entry, get the ID of that entry via a Lookup or
>something similar, and put the ID into the ProblemReport's "User ID" field.
>Currently, the user task processes all the rows in the SQL table and creates
>an entry in the Access User table for each. This means I can't get the ID
>numbers for the User entries as they're created, and all the ProblemReport
>entries end up with the same value in the User ID field, which is clearly
>wrong.
>
>Any help? How do I get multiple things happening to a row in the SQL table
>before moving on to the next row? (By the way, the structure of the Access
>database is not under my control -- I have to live with the way it's
>structured.)
>
>Thanks,
>Jim
>
From within an ActiveX Transform Script you can call a lookup. Lookups
are basically SQL statements than can have optional parameters, and may
or may not return any data for your use within the script. So you can
write inserts, updates, deletes and selects as lookups, or even multiple
statements, which in turn can even be wrapped up as stored procedures.
So whilst the DataPump is normally used for importing data, using
lookups you can do lots of other stuff on the way.
Data Drive Query tasks are an alternative as they allow you to write
your own insert statement. Similar to lookups the DDQ SQL statements,
whilst having nominal names such as insert, update and delete for
example, they can actually be anything. By returning multiple values you
can cause multiple queries to execute.
You may however just find it simpler to import all data directly into a
staging database in the same structure as Access. You can then use pure
T-SQL to perform the migration.
--
Darren Green (SQL Server MVP)
DTS -
http://www.sqldts.com PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org
