I have an Access DB and recently designed a SQL 2000 tables scheme
that is some how similar to Access but added more fields and more
tables and normalized tables to more tables.

I need to use DTS (if this is the right tool to use, if not, please
suggest) to transform all the data ONLY from Access to SQL.

Is there any way that I can customize the way it transform and I need
to save these procedures in future use (just in case if I want to do
all these transforming again in the future).

The weird thing about the table design in sql is that in Customers
table, the primary ID will be referenced to new record. for example.

Customers table:
ID, refID, firstName, lastName, dayPhone
1,0,aaa,aaa,123-456-7890
2,1,bbb,bbb,123-123-1234
3,1,ccc,ccc,123-123-4321
4,2,ddd,ddd,123-123-4320

you see how refID is actually from its previous ID and this is the way
that my previous guy designed (not good I know of, but has no
permission to change the scheme ).. so I need a very huge favor and
help on this thing to get my old app completed transfered to new sql
and without breaking the relationship of ID, refID.... Thanks.. you
can reach me at msn IM ebug (AT) hotmail (DOT) com or email to this account as
well... Again.. I am very appreciated for any of your help. Thank
you.

DTS can certainly do this yes.

If refID is your SourceSystem ID then you have duplicated values no ? RefID
= 1

You can work the logic into an Active SCript transform. You can save the
logic in a package.

If you show us Real source and real destination rows we may be able to offer
more concrete samples.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Kelvin" <ebug (AT) hotmail (DOT) com> wrote

Hi Allan,

Thanks for your help and tips.
Here is the real table scheme and the data of what it stands for.

Customers Table
ID,Name,studentID,staffID,providerID,schoolID
1,ABC High School,(NULL),(NULL),(NULL),(NULL)
2,Kelvin,(NULL),(NULL),(NULL),1
3,Linda,2,(NULL),(NULL),(NULL),1

above relationship shows that:
ID=1 is a school,
ID=2 is a student that is in ABC High School
ID=3 is a Staff that has Kelvin as student under her and himself works
under ABC High school (working under this school)
as you can see this is how it goes in database, I need to transfer all
this into SQL (it was in access db) but the new design i as following:

Staff table that contains staff info with ID,StaffName
Providers table that contains provider info with ID,providerName
Students table that contains students info with ID,studentName

so to link this up, I created a reference table like this:
FromID, ToID
So I can linke student with school with its own id and recorded in a
reference table.. if I want to delete them.. I just delete the record
in reference table instead of deleting students or staff table.

I hope I answered your question right. and hope there is a solution
for this kind of table scheme.

Kelvin


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

OK So you are storing denormalised data inthe Access DB that you want to
normalise into SQL Server

Look at your relationships. Which comes first

A Student is taught by a teacher at a school

It would suggest

A Teacher cannot exist without a school in which to teach and a Teacher
cannot exist without Students
Q: Can a teacher exist with no Students. If Yes and you enter a null for
your StudentID in a row for a teacher then your ability to distinguish
between a Student and a teacher is shot.

This suggests your load sequence should be

Schools
Students
Teachers

1. You load up the schools checking to see if you have them first (WHERE
SchoolID IS NULL)
2. You then load up the Pupils who attend the School. You can refer back
to the Source table to retrieve the name of the school at the source to find
out that the new identifier is in the destination.
3. You then load up teachers and do the same lookup with School and a
lookup with the student finding out their name from the source and looking
the identifier up in the destination where the name matches.

Get the idea?

Check your data for the possibility of a teacher with no students and
therefore a NULL in the StudentID. I cannot see a way to differentiate
between a Teacher and a student this way.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Kelvin" <ebug (AT) hotmail (DOT) com> wrote