Periodical import of Access db into SQL Server 2000

Doug Baroter

Hi,

One of my clients has the following situation. They use Access DB for
data update etc. some business functions while they also want to view
the Access data quickly and more efficiently in SQL Server 2000. Huge
Access db with over 100 user tables, over 60 MB data.

The DTS package that comes with SQL Server 2000 seems pretty "messy"
in the sense that it assumes that one needs to do one time import only
or accurately it does not seem to address periodical import well, like
in this case, we need to fresh the Access data in the SQL Server
import daily. And I don't see DTS package (saved or new) has an
option, that would allow one to delete existing destination data
first, that is what we want for all freshes. For the saved package,
the design screen is too busy, maybe, here one of the icon(function)
provides what we need, however, I don't it's globaly applicable, I
mean, the whole database vs. a particular table.

So, what do you think of the following strategy?

1) Create/save the Access import DTS process;
2) Create a sql script that would check and drop the destination
database, and
recreate the same new destionation database;

The schedule the sql script a minute or two before the scheduled
runtime of
the saved DTS package.

Does this approach make some sense or do you have a better idea? Now,
a new
question arises here, would this task of frequent drop and creation of
a reasonably large database contribute to disk fragmentation, which
would slow down data retrieval among others.

Thanks.

Jul 20 '05 #1

John Bell

Hi

DTS packages can be scheduled, run from T-SQL or code. The last screen of
the Import Wizard will give you this option and can be used as an example if
you want to set it up manually.

To clear out the existing data, in the copy tables option you choose the
create the table (each time, which may fail on the second run!) and if the
table exists when the package is being designed you will get the option to
delete the existing data or append the rows. These option are found by
clicking the transform button on the select source tables and views dialog.
It may be an idea to run an import once to get the tables created and on the
second time to create the package (which will have the delete from tables
(Execute SQL) task You may want this to truncate the table rather than doing
a delete. The Execute SQL Tasks can also run stored procedures and other
statements.

I suggest you read the information regarding DTS in books online and browse
the excellent resources at http://www.sqldts.com as most of your
findings/conclusion seem incorrect.

John

"Doug Baroter" <qw********@boxfrog.com> wrote in message
news:fc**************************@posting.google.c om...

Hi,

One of my clients has the following situation. They use Access DB for
data update etc. some business functions while they also want to view
the Access data quickly and more efficiently in SQL Server 2000. Huge
Access db with over 100 user tables, over 60 MB data.

The DTS package that comes with SQL Server 2000 seems pretty "messy"
in the sense that it assumes that one needs to do one time import only
or accurately it does not seem to address periodical import well, like
in this case, we need to fresh the Access data in the SQL Server
import daily. And I don't see DTS package (saved or new) has an
option, that would allow one to delete existing destination data
first, that is what we want for all freshes. For the saved package,
the design screen is too busy, maybe, here one of the icon(function)
provides what we need, however, I don't it's globaly applicable, I
mean, the whole database vs. a particular table.

So, what do you think of the following strategy?

1) Create/save the Access import DTS process;
2) Create a sql script that would check and drop the destination
database, and
recreate the same new destionation database;

The schedule the sql script a minute or two before the scheduled
runtime of
the saved DTS package.

Does this approach make some sense or do you have a better idea? Now,
a new
question arises here, would this task of frequent drop and creation of
a reasonably large database contribute to disk fragmentation, which
would slow down data retrieval among others.

Thanks.

Jul 20 '05 #2

Doug Baroter

Well, thanks for pointing out the fact that the Transformation option
include "delete and recreate table" feature, which slip away from my
eyes last time when I ran DTS import. However, this feature is TABLE
specific vs. the whole DATABASE , which means, one has to do it for
each user table, like in this particular case, I would have to check
each table, sum > 100, which is very time-consuming if I decide to use
this approach.
My original approach would work, my question was "what's the downside
of it if any?" I've read BOL on DTS.

Also, it is simply disappointing that the DTS import from Access to
SQL Server can't carry over constraints, RI etc. (system
objects/schema) though both are MS products, it's a shame. One may
argue that one is ANSI SQL compliant while the other is not, well,
couldn't a software powerhouse do what it can in the schema
import/transformation?

My findings about DTS may not have been very accurate, but you did not
seem to notice my solution.
"John Bell" <jb************@hotmail.com> wrote in message news:<3f***********************@news.easynet.co.uk >...

Hi

DTS packages can be scheduled, run from T-SQL or code. The last screen of
the Import Wizard will give you this option and can be used as an example if
you want to set it up manually.

To clear out the existing data, in the copy tables option you choose the
create the table (each time, which may fail on the second run!) and if the
table exists when the package is being designed you will get the option to
delete the existing data or append the rows. These option are found by
clicking the transform button on the select source tables and views dialog.
It may be an idea to run an import once to get the tables created and on the
second time to create the package (which will have the delete from tables
(Execute SQL) task You may want this to truncate the table rather than doing
a delete. The Execute SQL Tasks can also run stored procedures and other
statements.

I suggest you read the information regarding DTS in books online and browse
the excellent resources at http://www.sqldts.com as most of your
findings/conclusion seem incorrect.

John

"Doug Baroter" <qw********@boxfrog.com> wrote in message
news:fc**************************@posting.google.c om...

Hi,

One of my clients has the following situation. They use Access DB for
data update etc. some business functions while they also want to view
the Access data quickly and more efficiently in SQL Server 2000. Huge
Access db with over 100 user tables, over 60 MB data.

The DTS package that comes with SQL Server 2000 seems pretty "messy"
in the sense that it assumes that one needs to do one time import only
or accurately it does not seem to address periodical import well, like
in this case, we need to fresh the Access data in the SQL Server
import daily. And I don't see DTS package (saved or new) has an
option, that would allow one to delete existing destination data
first, that is what we want for all freshes. For the saved package,
the design screen is too busy, maybe, here one of the icon(function)
provides what we need, however, I don't it's globaly applicable, I
mean, the whole database vs. a particular table.

So, what do you think of the following strategy?

1) Create/save the Access import DTS process;
2) Create a sql script that would check and drop the destination
database, and
recreate the same new destionation database;

The schedule the sql script a minute or two before the scheduled Typo "The" should be "Then"
runtime of
the saved DTS package.

Does this approach make some sense or do you have a better idea? Now,
a new
question arises here, would this task of frequent drop and creation of
a reasonably large database contribute to disk fragmentation, which
would slow down data retrieval among others.

Thanks.

Jul 20 '05 #3

DCM Fan

I suggest spending the time taking the first replier's advice, in that you
should use the wizard to create a package that just "copies" the data only.

Then, add a new Execute SQL Task as the first step of the process that does a
TRUNCATE TABLE on every table (yes, it might mean typing out 100 statements, or
writing a cursor, but it'll work)

Then add more SQL tasks at the end that rebuild the RI, INDEXES, CONSTRAINTS,
etc...it's not that difficult...merely a grudge task. But when it's done, and
the package runs, it WILL be what you want.

Sure, it's a shame MS didn't build a wizard that says "Hey, get this Access
database and build all INDEXES, FK, RI, CONSTRAINTS, etc, and oh, by the way,
move the data too."

Jul 20 '05 #4