Hi Folks,

I am pretty new to DTS so I am asking you for some hints. I need to
import many Access databases (approx 90) to a new database on the sql
server. The design of the new database is COMPLETELY NEW. Those 90
Access databases all share pretty much the same design which was adapted
a little bit over the past few years. For importing the data I intend to
write stored procedures which extract the data from the old databases
and insert it into the new designed database. But therefore I need to
import those 90 access databases into my sqlserver. This is the tricky
part. I realized that I can't use the upsizing assistant since I would
have to use it manually for those 90 databases (and I would have to do
it when developing the import routines, when testing them, and when
performing the final import and migration to the new system).
Is it possible to write a DTS Package which "copies" each of the 90
databases into a new seperate SQL Server Database? Or is it less effort
to perform an Upsize for each database?
regards
Stephanz

Whilst I have never done this, I can guess how I would attempt it.

You have n Databases in Access that are the same (for the first approach
they have to be the same)
You want to import to SQL Server. A different database per access
database

1.
Use the wizard to create a package that does the work you need
Now you have Access Connections and SQL Server Connections.
What you need to do is call this package and on each iteration you
dynamically change the values for the Access database and the SQL Server
database on their relative Connections.

2. if the structures are not the same then it is more difficult.

You would have to loop through the tables on the access source
Create a destination table in SQL Server of same structure
Create mappings dynamagically in a DataPump task
Pump data loop

You will also need to take care of the connection details although in
this example there are less than the first.


#2 is you ping me offline then I can put you on the right track with
some code for doing the dynamic stuff.


Allan




"Stephan Zaubzer" <stephan.zaubzer (AT) schendl (DOT) at> wrote

Thank you...
The first method would work, if the structures of the databases would be
the same. Unfortunately over the past few years there where slight
changes, so method 1 os not suitable for me.
Method 2 sounds great. But my problem: How do I start? I am an
experienced C/C++/Java/Perl programmer. But I have never worked with
anything like VisualBasic.
With the Visual DTS Designer I know how to make a connection to an
Access database. But how do I use this connection for example in a loop,
changing the path of the mdb file in each iteration? How do I iterate
over all tables? I would need some starting point from which on I can
work on myself.
Regards
Stephan

Allan Mitchell wrote:

Well you could pay for me to come on site (grin) or yo9u can ping me
offline for a package that shows you a little of #2 as mentioned before.
As for looping and the connections thing then these may be helpful.

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)

How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)

Allan


"Stephan Zaubzer" <stephan.zaubzer (AT) schendl (DOT) at> wrote