Using DTS to import 40 Databases

[jluost1]

Hi, I have 40 Access97 databases with the same name (webdb.mdb) and the same data structure, but with different data.

Now I want to transform all 40 Databases into SQL Server 7, with different names (the name is "webdb" + webname, which is the name of a web site).

I know I can create a DTS package using DTS wizard. I can open the package, modify the source database, and save it with a new name. This way, I can create 40 packages and it just takes 10 minutes. But if I make change in 1 package, I have to change the other 39.

Can somebody tell me a better way?

Thanks.

 

[MinAmiChief]

Help.Maybe.

I have a similar problem. If the data inside the database (eg tables etc) is the same
1. create one DTS package that imports an .mdb (like webimport.mdb),
2. then change the name of the .mdb from WebDB1.mdb to webimport.mdb, WebDB2.mdb to webimport.mdb, etc... This can be done with a .bat file.

OR.

There is an Access upsizing tool. (Type in Access Upsizing to SQL Server in MS downloads or MS knowledgebase). This will convert your tables in SQL Server with a click of the button. You have to be in Access thats all.

HTH

The Chief



Be the change that you want to see in the world - Mahatma Gandhi

 

[tlbroadbent]

Check

http://www.sqldts.com/

for some great DTS examples. These include how to execute DTS packages from VB, how to use global variables, how to dynamically select file names, etc. I'm sure you could modify one of the downloadable examples for your applications.

Another possibility when importing Access tables is to use linked servers or ad hoc connections to the Access databases in T-SQL - perhaps in a stored procedure.

Here is an Ad Hoc example using OPENROWSET.

INSERT WebDBServer1
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','\\server1\share\path\webdb.mdb';'admin';'mypwd',table_name) AS a

table_name can be an Access table, a defined query or you can use a select statement as in the example below.

INSERT WebDBServer1
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','\\server1\share\path\webdb.mdb';'admin';'mypwd','Select col1, col2, col4, col6 From table_name Where col3>10') AS a

I would consider creating a table to hold the names of the webs. In the stored procedure , I would declare a cursor to read the web names from the table and build dynamic SQL statements that could be executed to load the Access data to the SQL tables. By doing this, the process wouldn't have to be changed - rows could be added to or deleted from the table. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.