|
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. | |
Share:
|
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. | | |
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. | | |
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." | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
1 post
views
Thread by Chris |
last post: by
|
2 posts
views
Thread by BUSHII |
last post: by
|
1 post
views
Thread by mark |
last post: by
|
1 post
views
Thread by Dan |
last post: by
|
2 posts
views
Thread by mattc66 via SQLMonster.com |
last post: by
| | |
1 post
views
Thread by Child of His |
last post: by
|
1 post
views
Thread by Wandering |
last post: by
| | | | | | | | | | |